Table des matières
En pratique, il y a très souvent des cas où vous et moi avons besoin de trouver la valeur la plus proche dans un ensemble (tableau) par rapport à un nombre donné. Il pourrait s'agir, par exemple :
- Calcul de remise en fonction du volume.
- Calcul du montant des primes en fonction de la mise en œuvre du plan.
- Calcul des tarifs d'expédition en fonction de la distance.
- Sélection de conteneurs appropriés pour les marchandises, etc.
De plus, l'arrondi peut être nécessaire à la fois vers le haut et vers le bas, selon la situation.
Il existe plusieurs façons – évidentes et moins évidentes – de résoudre un tel problème. Examinons-les séquentiellement.
Pour commencer, imaginons un fournisseur qui accorde des remises sur le commerce de gros, et le pourcentage de remise dépend de la quantité de marchandises achetées. Par exemple, lors de l'achat de plus de 5 pièces, une remise de 2% est accordée, et lors de l'achat à partir de 20 pièces - déjà 6%, etc.
Comment calculer rapidement et magnifiquement le pourcentage de remise lors de la saisie de la quantité des marchandises achetées?
Méthode 1 : IF imbriqués
Une méthode de la série "qu'est-ce qu'il y a à penser - vous devez sauter!". Utiliser des fonctions imbriquées IF (SI) pour vérifier séquentiellement si la valeur de la cellule tombe dans chacun des intervalles et afficher une remise pour la plage correspondante. Mais la formule dans ce cas peut s'avérer très lourde :
Je pense qu'il est évident que déboguer une telle "poupée monstre" ou essayer d'y ajouter quelques nouvelles conditions après un certain temps est amusant.
De plus, Microsoft Excel a une limite d'imbrication pour la fonction SI - 7 fois dans les anciennes versions et 64 fois dans les versions plus récentes. Et si vous avez besoin de plus ?
Méthode 2. VLOOKUP avec vue d'intervalle
Cette méthode est beaucoup plus compacte. Pour calculer le pourcentage de remise, utilisez la fonction légendaire VPR (VLOOKUP) en mode recherche approximative :
De
- B4 – la valeur de la quantité de marchandises dans la première transaction pour laquelle nous recherchons une remise
- $G$4 :$H$8 – un lien vers le tableau des remises – sans « en-tête » et avec les adresses fixées par le signe $.
- 2 — le numéro ordinal de la colonne de la table de remise à partir de laquelle nous voulons obtenir la valeur de remise
- VRAI - c'est là que le "chien" est enterré. Si comme dernier argument de la fonction VPR spécifier COUCHÉ (FAUX) ou 0, alors la fonction cherchera correspondance stricte dans la colonne de quantité (et dans notre cas, cela donnera une erreur #N/A, car il n'y a pas de valeur 49 dans le tableau des remises). Mais si à la place COUCHÉ écrire VRAI (VRAI) ou 1, alors la fonction ne cherchera pas l'exact, mais le plus proche le plus petit valeur et nous donnera le pourcentage de réduction dont nous avons besoin.
L'inconvénient de cette méthode est la nécessité de trier le tableau des remises par ordre croissant de la première colonne. S'il n'y a pas un tel tri (ou s'il est fait dans l'ordre inverse), alors notre formule ne fonctionnera pas :
Par conséquent, cette approche ne peut être utilisée que pour trouver la plus petite valeur la plus proche. Si vous avez besoin de trouver le plus grand le plus proche, vous devez utiliser une approche différente.
Méthode 3. Trouver le plus grand le plus proche à l'aide des fonctions INDEX et MATCH
Examinons maintenant notre problème de l'autre côté. Supposons que nous vendions plusieurs modèles de pompes industrielles de différentes capacités. Le tableau des ventes à gauche indique la puissance requise par le client. Nous devons sélectionner une pompe de la puissance maximale ou égale la plus proche, mais pas inférieure à ce qui est requis par le projet.
La fonction VLOOKUP ne vous aidera pas ici, vous devrez donc utiliser son analogue - un tas de fonctions INDEX (INDICE) et PLUS EXPOSÉ (RENCONTRE):
Ici, la fonction MATCH avec le dernier argument -1 fonctionne dans le mode de recherche de la plus grande valeur la plus proche, et la fonction INDEX extrait ensuite le nom du modèle dont nous avons besoin de la colonne adjacente.
Méthode 4. Nouvelle fonction VIEW (XLOOKUP)
Si vous avez une version d'Office 365 avec toutes les mises à jour installées, alors au lieu de VLOOKUP (VLOOKUP) vous pouvez utiliser son analogue - la fonction VIEW (XRECHERCHE), que j'ai déjà analysé en détail :
Ici:
- B4 – la valeur initiale de la quantité du produit pour lequel nous recherchons une remise
- $G$4 :$G$8 – la gamme où nous recherchons des matchs
- 4 $ H $ : 8 $ H $ – la plage de résultats à partir de laquelle vous souhaitez retourner la remise
- quatrième argument (-1) inclut la recherche du plus petit nombre le plus proche que nous voulons au lieu d'une correspondance exacte.
Les avantages de cette méthode sont qu'il n'est pas nécessaire de trier le tableau des remises et la possibilité de rechercher, si nécessaire, non seulement la plus petite valeur la plus proche, mais également la plus grande valeur la plus proche. Le dernier argument dans ce cas sera 1.
Mais, malheureusement, tout le monde n'a pas encore cette fonctionnalité - seuls les heureux propriétaires d'Office 365.
Méthode 5. Power Query
Si vous n'êtes pas encore familiarisé avec le puissant et entièrement gratuit complément Power Query pour Excel, alors vous êtes ici. Si vous êtes déjà familier, essayons de l'utiliser pour résoudre notre problème.
Faisons d'abord quelques travaux préparatoires :
- Convertissons nos tables sources en dynamiques (intelligentes) à l'aide d'un raccourci clavier Ctrl+T ou équipe Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau).
- Pour plus de clarté, donnons-leur des noms. Ventes и Remises languette Constructeur (Conception).
- Charger tour à tour chacune des tables dans Power Query à l'aide du bouton À partir du tableau/plage languette Données (Données – À partir du tableau/de la plage). Dans les versions récentes d'Excel, ce bouton a été renommé en Avec des feuilles (De la feuille).
- Si les tables ont des noms de colonne différents avec des quantités, comme dans notre exemple ("Quantité de marchandises" et "Quantité de ..."), elles doivent être renommées dans Power Query et nommées de la même manière.
- Après cela, vous pouvez revenir à Excel en sélectionnant la commande dans la fenêtre de l'éditeur Power Query Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger vers…) puis option Créez simplement une connexion (Créer uniquement une connexion).
- Commence alors le plus intéressant. Si vous avez de l'expérience dans Power Query, je suppose que la suite de la réflexion devrait aller dans le sens de la fusion de ces deux tables avec une requête de jointure (fusion) à la VLOOKUP, comme c'était le cas dans la méthode précédente. En fait, il va falloir fusionner en mode ajout, ce qui n'est pas du tout évident à première vue. Sélectionner dans l'onglet Excel Données – Obtenir des données – Combiner des demandes – Ajouter (Données — Obtenir des données — Combiner des requêtes — Ajouter) puis nos tables Ventes и Remises dans la fenêtre qui apparaît :
- Après avoir cliqué sur OK nos tables seront collées en un seul ensemble – les unes sous les autres. Veuillez noter que les colonnes avec la quantité de marchandises dans ces tableaux tombaient les unes sous les autres, car. ils ont le même nom :
- Si la séquence originale de lignes dans la table des ventes est importante pour vous, alors pour qu'après toutes les transformations ultérieures, vous puissiez ensuite la restaurer, ajoutez une colonne numérotée à notre table à l'aide de la commande Ajout d'une colonne - Colonne d'index (Colonne Ajouter — Colonne Index). Si la séquence de lignes n'a pas d'importance pour vous, vous pouvez ignorer cette étape.
- Maintenant, à l'aide de la liste déroulante dans l'en-tête du tableau, triez-le par colonne Quantité Ascendant:
- Et l'astuce principale : faites un clic droit sur l'en-tête de la colonne La remise choisir une équipe Remplissez (Remplissez). Cellules vides avec nul rempli automatiquement avec les valeurs de remise précédentes :
- Il reste à restaurer l'ordre initial des lignes en triant par colonne Sommaire (vous pouvez le supprimer en toute sécurité plus tard) et débarrassez-vous des lignes inutiles avec un filtre nul par colonne Code de transaction:
- Utilisation de la fonction VLOOKUP pour rechercher et rechercher des données
- L'utilisation de VLOOKUP (VLOOKUP) est sensible à la casse
- RECHERCHEV XNUMXD (RECHERCHEV)