031

В этой статье будет рассказано о том, как получить все значения из таблицы и получить их в разных ячейках. Это похоже на поиск нескольких значений.

Общая формула

{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(expanding ranges))), “--List Ends--”)}

Слишком много функций и переменных !!!. Посмотрим, что это за переменные.

Имена: это список имен.

Группы: список групп, к которым также принадлежат эти имена.

Group_name: ссылка на название группы.

link: / excel-range-name-expanding-links-in-excel [Расширение диапазонов]: это расширяющийся диапазон, который используется для увеличения числа при копировании вправо.

Пример: извлеките имена сотрудников в разные столбцы в соответствии с их компанией.

030

Допустим, у вас есть таблица сотрудников, сгруппированных по их компаниям. Первый столбец содержит имена сотрудников, а второй столбец содержит название компании.

Теперь нам нужно получить имя каждого сотрудника в разных столбцах в соответствии с их компанией. Другими словами, нам нужно разгруппировать их.

Здесь я назвал A2: A10 как «Сотрудник», а B2: B10 как «Компания», чтобы эту формулу было легко читать.

Запишите эту формулу массива в F2. Используйте CTRL + SHIFT + ENTER, чтобы ввести эту формулу.

{=INDEX(Employee,SMALL(IF(Company=$E2,ROW(Employee)-MIN(ROW(Employee))+1),COLUMNS($E$1:E1))), “--List Ends--”)}

Скопируйте эту формулу во все ячейки. Он извлечет каждое отдельное имя в разные столбцы в соответствии с их группой.

031

Как вы можете видеть на изображении выше, каждый сотрудник разделен по разным ячейкам.

Итак, как работает эта формула?

Чтобы понять формулу, давайте посмотрим на формулу в G2, которая = ЕСЛИОШИБКА (ИНДЕКС (Сотрудник, МАЛЫЙ (ЕСЛИ (Компания = $ E3, СТРОКА (Сотрудник) -MIN (СТРОКА (Сотрудник)) + 1), COLUMNS ($ E $ 1: F2))), «- Концы списка—«)

Механика проста и почти такая же, как у link: / lookup-formulas-vlookup-multiple-values ​​[множественная формула VLOOKUP]. Уловка состоит в том, чтобы получить порядковый номер каждого сотрудника из разных групп и передать его в формулу ИНДЕКС. Это делается с помощью этой части формулы.

IF (Компания = $ E3, link: / lookup-and-reference-excel-row-function [ROW] (Employee) -MIN (`link: / lookup-and-reference-excel-row-function [ROW] `(Сотрудник *)) + 1):

Эта часть возвращает массив индексов и false для названия компании в $ E3, которое содержит «Rankwatch».

\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.

Как? Снесем изнутри.

Здесь мы сопоставляем название компании в $ E3 с каждым значением в диапазоне Company (Company = $ E3).

Это возвращает массив значений true и false. \ {FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}.

Теперь функция IF запускает операторы TRUE для TRUE, которые являются link: / lookup-and-reference-excel-row-function [ROW] (Employee) -MIN (`link: / lookup-and-reference-excel- row-function [ROW] `(Сотрудник)) + 1.

Эта часть возвращает, эта часть возвращает массив индексов от 1 до количества сотрудников \ {1; 2; 3; 4; 5; 6; 7; 8; 9}. Функция if отображает значения только для ИСТИНА, что, в свою очередь, дает нам \ {ЛОЖЬ; 2; ЛОЖЬ; 4; ЛОЖЬ; ЛОЖЬ; 7; ЛОЖЬ; 9}.

Текущая формула упрощена до = ЕСЛИОШИБКА (ИНДЕКС (Сотрудник, МАЛЫЙ (\ {ЛОЖЬ; 2; ЛОЖЬ; 4; ЛОЖЬ; ЛОЖЬ; 7; ЛОЖЬ; 9}, СТОЛБЕЦ ($ E $ 1: F2))), «- Список заканчивается — «). Как мы знаем, небольшая функция возвращает n-е наименьшее значение из массива. COLUMNS ($ E $ 1: F2) возвращает 2. Функция SMALL возвращает второе наименьшее значение из массива выше, равное 4.

Теперь формула упрощена = ЕСЛИОШИБКА (ИНДЕКС (Сотрудник, 4), «- Список заканчивается—»). Теперь функция ИНДЕКС просто возвращает четвертое имя из массива сотрудников, которое дает нам «Сэм».

Так что да, вот как извлекать имена из групп в разных столбцах с помощью link: / excel-range-name-expanding-links-in-excel [INDEX], link: / statistics-formulas-excel-small-function [SMALL ] `, link: / lookup-and-reference-excel-row-function [ROW] `, COLUMNS and IF function. Если у вас есть какие-либо сомнения относительно этой функции или она не работает для вас, дайте мне знать в разделе комментариев ниже.

Скачать файл: