oguruma
XLDnaute Occasionnel
Bonjour,
Bien qu'il existe de multiples versions sur la toile et par le passé j'avais déjà publié une version du RechercheX ou une fonction faisant l'équivalent du Index+Equiv vous trouverez 3 versions du VLOOKUP Excel.
Pour ces 3 versions l'algorithme du code reste le même. Les points qui les diffèrent sont les lignes d'optimisation : si on bufferise la table des données, si on trie la table ou pas sur la 1ère colonne de recherche et si on effectue au préalable les premières recherches pour palier ou pas à la recherche approximative.
Version 1 : fnVLOOKUP
Peu optimisée. Les 1ères recherches préalables sont lancées systématiquement. La table des données n'est pas bufferisée.
Version 2 : fnVLOOKUP
La table des données n'est pas bufferisée mais la recherche préalable au mode approximatif n'est effectuée que si précisée dans les paramètres
Version 3 : fnVLOOKUP3
Possibilité de bufferiser la table des données dans les options, les traitements préalables de recherche et quelque soit le mode ne sont effectués que si nécessaires
Bien qu'il existe de multiples versions sur la toile et par le passé j'avais déjà publié une version du RechercheX ou une fonction faisant l'équivalent du Index+Equiv vous trouverez 3 versions du VLOOKUP Excel.
Pour ces 3 versions l'algorithme du code reste le même. Les points qui les diffèrent sont les lignes d'optimisation : si on bufferise la table des données, si on trie la table ou pas sur la 1ère colonne de recherche et si on effectue au préalable les premières recherches pour palier ou pas à la recherche approximative.
Version 1 : fnVLOOKUP
Peu optimisée. Les 1ères recherches préalables sont lancées systématiquement. La table des données n'est pas bufferisée.
PowerQuery:
let fnVLOOKUP=
(
LookupValue as any,
TableData as table,
ColumnIndex as number,
optional pSortTable as logical,
optional pApproximative as logical,
optional pMsg as text
) as any =>
let
// Recherche approximative demandée ? Par défaut la recherche sera approximative
bMatchApproximative = if pApproximative = null then true else pApproximative,
bSort = if pSortTable = null then true else pSortTable,
ErrMsg=if pMsg = null then "#N/A" else pMsg,
// Initialisation de la recherche - on liste les colonnes de la table
ListColumns = Table.ColumnNames(TableData),
// La liste des colonnes est transformée en table
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
ColumnsTable = Table.Buffer(Table.FromList(ListColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
// On identifie la colonne de recherche
ColumnNameMatched = Record.Field(ColumnsTable{0},"Column1"),
ColumnNameReturned = Record.Field(ColumnsTable{ColumnIndex - 1},"Column1"),
// Doit-on trier la table d'entrée
SortedData = if bSort then
Table.Sort(TableData,{{ColumnNameMatched, Order.Descending}})
else TableData,
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
RenameLookupColumn = Table.Buffer(Table.RenameColumns(SortedData,{{ColumnNameMatched, "LookupColumn"}})),
SeekRow = Table.Buffer(Table.SelectRows(RenameLookupColumn, each [LookupColumn] <= LookupValue)),
// On évalue la 1ère tentative de recherche
IstMatch=
if Table.IsEmpty(SeekRow)=true
then ErrMsg
else Record.Field(SeekRow{0},"LookupColumn"),
// Valeur retournée en cas de recherche approximative
ReturnValue=
if Table.IsEmpty(SeekRow)=true
then ErrMsg
else Record.Field(SeekRow{0},ColumnNameReturned),
// Cas où la recherche doit être approximative
FinalReturn =
if bMatchApproximative=true
then ReturnValue
// Sinon on renvoie la valeur exacte
else if LookupValue = IstMatch
then ReturnValue
else ErrMsg
// On renvoie la valeur recherchée quelque soit le mode de recherche
in
FinalReturn
in
fnVLOOKUPVersion 2 : fnVLOOKUP
La table des données n'est pas bufferisée mais la recherche préalable au mode approximatif n'est effectuée que si précisée dans les paramètres
Code:
let fnVLOOKUP=
(
LookupValue as any,
TableData as table,
ColumnIndex as number,
optional pSortTable as logical,
optional pApproximative as logical,
optional pMsg as text
) as any =>
let
// Recherche approximative demandée ? Par défaut la recherche sera approximative
bMatchApproximative = if pApproximative = null then true else pApproximative,
bSort = if pSortTable = null then true else pSortTable,
ErrMsg=if pMsg = null then "#N/A" else pMsg,
// Initialisation de la recherche - on liste les colonnes de la table
ListColumns = Table.ColumnNames(TableData),
// La liste des colonnes est transformée en table
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
ColumnsTable = Table.Buffer(Table.FromList(ListColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
// On identifie la colonne de recherche
ColumnNameMatched = Record.Field(ColumnsTable{0},"Column1"),
ColumnNameReturned = Record.Field(ColumnsTable{ColumnIndex - 1},"Column1"),
// Doit-on trier la table d'entrée
SortedData = if bSort then
Table.Sort(TableData,{{ColumnNameMatched, Order.Descending}})
else TableData,
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
RenameLookupColumn = Table.Buffer(Table.RenameColumns(SortedData,{{ColumnNameMatched, "LookupColumn"}})),
SeekRow = Table.Buffer(Table.SelectRows(RenameLookupColumn, each [LookupColumn] <= LookupValue)),
// On évalue la 1ère tentative de recherche
IstMatch=
if Table.IsEmpty(SeekRow)=true
then ErrMsg
else Record.Field(SeekRow{0},"LookupColumn"),
// On démarre les recherches
FinalReturn =
// Cas où la recherche doit être approximative
if bMatchApproximative=true
then let
// On recherche la clef la plus proche
V= if Table.IsEmpty(SeekRow)=true then ErrMsg else Record.Field(SeekRow{0},ColumnNameReturned)
in
V
// Sinon on renvoie la valeur exacte si on trouve la clef de recherche
else if LookupValue = IstMatch
then let
V= if Table.IsEmpty(SeekRow)=true then ErrMsg else Record.Field(SeekRow{0},ColumnNameReturned)
in
V
else ErrMsg
// On renvoie la valeur recherchée quelque soit le mode de recherche
in
FinalReturn
in
fnVLOOKUPVersion 3 : fnVLOOKUP3
Possibilité de bufferiser la table des données dans les options, les traitements préalables de recherche et quelque soit le mode ne sont effectués que si nécessaires
PowerQuery:
let fnVLOOKUP=
(
LookupValue as any,
TableData as table,
ColumnIndex as number,
optional pSortTable as logical,
optional pApproximative as logical,
optional pMsg as text,
optional pBuff as logical
) as any =>
let
// Recherche approximative demandée ? Par défaut la recherche sera approximative
bMatchApproximative = if pApproximative = null then true else pApproximative,
bSort = if pSortTable = null then true else pSortTable,
ErrMsg=if pMsg = null then "#N/A" else pMsg,
bBuff = if pBuff = null then false else pBuff,
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
TableDataWork=if bBuff then Table.Buffer(TableData) else TableData,
// Initialisation de la recherche - on liste les colonnes de la table
ListColumns = Table.ColumnNames(TableData),
// La liste des colonnes est transformée en table
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
ColumnsTable = Table.Buffer(Table.FromList(ListColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
// On identifie la colonne de recherche
ColumnNameMatched = Record.Field(ColumnsTable{0},"Column1"),
ColumnNameReturned = Record.Field(ColumnsTable{ColumnIndex - 1},"Column1"),
// Doit-on trier la table d'entrée
SortedData = if bSort then
Table.Sort(TableDataWork,{{ColumnNameMatched, Order.Descending}})
else TableDataWork,
// On fait appel au buffer pour optimiser les traitements, attention volumétrie vs mémoire
RenameLookupColumn = Table.Buffer(Table.RenameColumns(SortedData,{{ColumnNameMatched, "LookupColumn"}})),
SeekRow = Table.Buffer(Table.SelectRows(RenameLookupColumn, each [LookupColumn] <= LookupValue)),
// On démarre les recherches
FinalReturn =
// Cas où la recherche doit être approximative
if bMatchApproximative=true
then let
// On recherche la clef la plus proche
V= if Table.IsEmpty(SeekRow)=true then ErrMsg else Record.Field(SeekRow{0},ColumnNameReturned)
in
V
// Sinon on renvoie la valeur exacte si on trouve la clef de recherche
else let
IstMatch=
if Table.IsEmpty(SeekRow)=true
then ErrMsg
else Record.Field(SeekRow{0},"LookupColumn"),
V=if LookupValue = IstMatch
then if Table.IsEmpty(SeekRow)=true then ErrMsg else Record.Field(SeekRow{0},ColumnNameReturned)
else ErrMsg
in
V
// On renvoie la valeur recherchée quelque soit le mode de recherche
in
FinalReturn
in
fnVLOOKUP
