Тази статия ще говори за това как да се съчетаят всички стойности от таблица и да се извлекат в различни клетки. Това е подобно на търсене на множество стойности.
Обща формула
{= INDEX (имена, МАЛКИ (IF (групи = име на група, ROW (имена)) -MIN (ROW (имена))+1), COLUMNS (разширяващи се диапазони))), “--List Ends--”)}
Твърде много функции и променливи !!!. Нека да видим какви са тези променливи.
Имена: Това е списъкът с имена.
Групи: Списъкът на групите, към които принадлежат и тези имена.
Име на групата: препратката към името на групата.
Разширяване на диапазоните: това е разширяващият се диапазон, който се използва за увеличаване на броя при копиране вдясно.
Пример: Извадете имената на служителите в различни колони според тяхната компания.
Да приемем, че имате таблица със служители, групирана според тяхната компания. Първата колона съдържа имена на служители, а втората колона съдържа името на компанията.
Сега трябва да получим името на всеки служител в различни колони според тяхната компания. С други думи, трябва да ги разгрупираме.
Тук съм посочил A2: A10 като служител и B2: B10 като компания, така че тази формула е лесна за четене.
Напишете тази формула на масив във F2. Използвайте CTRL+SHIFT+ENTER, за да въведете тази формула.
{= ИНДЕКС (Служител, МАЛКИ (АКО (Фирма = $ E2, ROW (Служител) -MIN (ROW (Служител))+1) )}
Копирайте тази формула във всички клетки. Той ще извлече всяко отделно име в различните колони според тяхната група.
Както можете да видите на изображението по -горе, всеки служител е разделен в различни клетки.
И така, как работи тази формула?
За да разберем формулата, нека разгледаме формулата в G2
Което е = IFERROR (INDEX (Служител, МАЛКО (АКО (Фирма = $ E3, ROW (Служител) -МИН (ROW (Служител))+1), COLUMNS ($ E $ 1: F2))), "-Списъкът завършва-")
Механиката е проста и почти същата като многократната формула VLOOKUP. Номерът е да получите индексния номер на всеки служител от различни групи и да го предадете на формулата INDEX. Това става чрез тази част от формулата.
АКО (Търговско дружество=$ E3, ROW (Служител) -МИН (ROW (Служител))+1):
Тази част връща масив от индекси и невярно за името на компанията в $ E3, който съдържа „Rankwatch“.
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Как? Нека го съборим отвътре.
Тук съпоставяме името на компанията в $ E3 с всяка стойност в Обхват на компанията (Фирма = $ E3).
Това връща масив от true и false. {FALSE;ВЯРНО; FALSE;ВЯРНО; FALSE; FALSE;ВЯРНО; FALSE;ВЯРНО}.
Сега функцията IF изпълнява изявления TRUE за TRUE, което е ROW (служител) -MIN (ROW (Служител))+1. Тази част връща тази част връща масив от индекси, започващи от 1 до номер на служители {1; 2; 3; 4; 5; 6; 7; 8; 9}. Функцията if събира стойности само за TRUE, което от своя страна ни дава {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Настоящата формула е опростена до
= IFERROR (INDEX (Служител, МАЛКО ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},КОЛОНИ ($ E $ 1: F2))), "-Списъкът завършва-"). Както знаем, малката функция връща n -та най -малка стойност от масив. КОЛОНИ ($ E $ 1: F2) това връща 2. Функцията SMALL връща втората най -малка стойност от масива по -горе, която е 4.
Сега формулата е опростена = IFERROR (INDEX (служител, 4), "-Списъкът завършва-"). Сега функцията INDEX просто връща четвъртото име от служител масив, който ни дава „Сам”.
Така че да, ето как извличате имена от групи в различни колони, използвайки INDEX, SMALL, ROW, COLUMNS и IF функция. Ако имате някакви съмнения относно тази функция или ако тя не работи за вас, кажете ми секцията за коментари по -долу.
Свали файл:
Свързани статии:
VLOOKUP Множество стойности
Използвайте INDEX и MATCH за търсене на стойност
Търсена стойност с множество критерии
Популярни статии:
Функцията VLOOKUP в Excel
COUNTIF в Excel 2016
Как да използвате функцията SUMIF в Excel