В това видео ще разгледаме как да изградим двупосочно търсене с INDEX и MATCH, използвайки приблизително съвпадение.
Тук имаме прост калкулатор на разходите, който търси разходите въз основа на ширината и височината на материала. Съвпадението трябва да е приблизително. Например, ако ширината е 250 и височината е 325, правилният резултат е 1800 долара.
Ако ширината е 450, а височината остава 325, верният резултат е 3 600 $.
Можем да изградим формула, която прави това търсене, използвайки INDEX и MATCH.
Първо, нека да накараме INDEX да работи като доказателство за концепция, чрез твърдо кодиране на част от формулата. Това е чудесен начин да се уверите, че имате правилната идея, преди да започнете.
И така, с данните в нашата таблица като масив и с ширина 450 и височина 325, INDEX ще се нуждае от номер на ред 3 и номер на колона 4, за да извлече правилната стойност в таблицата . Това работи добре, но разбира се няма да се промени, тъй като стойностите са кодирани твърдо.
И така, нека настроим функциите MATCH, от които се нуждаем, за да изчислим тези стойности.
За да получим стойността за ширина, която е номерът на реда в INDEX, ще използваме справочната стойност от M7 и стойностите в колона Б като масив за търсене. За тип съвпадение искаме да използваме 1 за приблизително съвпадение, защото стойностите са сортирани във възходящ ред. Резултатът е 4.
За да получим височина, която е колоната в INDEX, отново ще използваме MATCH със стойността от M8, стойностите за височини от ред 6. Отново типът на съвпадението отново е зададен на 1 за приблизително съвпадение. Резултатът е 3.
Сега, ако променя ширината на 350 и височината на 550, ще получим нов набор от резултати.
Тези стойности са точно това, от което се нуждаем за INDEX. Така че сега просто ще копирам и поставя функциите MATCH в оригиналната формула INDEX.
Широчината влиза в номера на реда.
И височината влиза в номера на колоната.
Сега имаме динамично търсене, което правилно изчислява разходите въз основа на ширина и височина, като приблизително съвпада.
Разбира се
Условно форматиранеСвързани преки пътища
Копиране на избрани клетки Ctrl + C ⌘ + C Поставяне на съдържание от клипборда Ctrl + V ⌘ + V
