Excel: взять горизонтальные данные из таблицы, организовать вертикально на другом листе, игнорируя пробелы

Question

У меня есть таблица контактных данных поставщиков, организованная следующим образом:

vendorname | address | city | state | zip | phone | fax | contactperson | phone | email

... и я хочу выбрать vendorname из выпадающего меню на другом листе (обработал эту часть), который затем заполнил бы контактную информацию для этого поставщика в ячейках ниже.

Чтобы было сложнее, я хочу, чтобы он игнорировал все пустые столбцы таблицы. Например, скажем, нет номера факса для записи в таблице. Я не хочу, чтобы он помещал пустую ячейку между «телефоном» и «контактным лицом».'

Можно ли это сделать, просто используя формулы?

Answer 1

Вы, вероятно, хотите использовать функцию VLOOKUP() . Это будет пример:

=VLOOKUP($B$3, Table1,2,FALSE)

Где $ B $ 3 - это ячейка с раскрывающимся списком, а Table1 - это таблица с контактными данными. Поместите эту формулу в первую ячейку, которая будет заполнена, когда элемент выбран из выпадающего списка. В других ячейках, которые нужно заполнить, вам просто нужно увеличить третий параметр (параметр индекса столбца; например, 2 в приведенном выше примере).

AFAIK, нет способа пропустить пустые столбцы с помощью этого метода. Вам, вероятно, понадобится VBA для этого.

Answer 2

Да, это может быть достигнуто только с помощью формул:

Поиск ярлыков (столбец A):

=IFERROR(INDEX(Table1[#Headers],SMALL(IF(OFFSET(Table1[#Headers],MATCH($B$8,Table1[vendorname],0),0)="","",COLUMN(Table1[#Headers])),ROW()-ROW($A$7))),"")

• MATCH($B$8,Table1[vendorname],0) находит строку, содержащую выбранный контакт
• OFFSET(Table1[#Headers],MATCH(...),0) получает адрес выбранной строки
• IF(OFFSET(...)="","",COLUMN(Table1[#Headers])) проверяет, какие столбцы выбранного элемента содержат данные (пустое значение для пустых, номер столбца для остальных)
• SMALL(IF(...),ROW()-ROW($A$7) выбирает следующий столбец, который будет использоваться
• INDEX(Table1[#Headers],SMALL(...)) получает заголовок
• =IFERROR(INDEX(...),"") показывает пустую ячейку после отображения всех данных

Поиск данных (столбец B):

=IFERROR(INDEX(OFFSET(Table1[#Headers],MATCH($B$8,Table1[vendorname],0),0),SMALL(IF(OFFSET(Table1[#Headers],MATCH($B$8,Table1[vendorname],0),0)="","",COLUMN(Table1[#Headers])),ROW()-ROW($A$7))),"")

Единственным отличием от предыдущего является первый параметр INDEX() , который является той же формулой OFFSET() используется в small для поиска данных в выбранной строке, а не в заголовке.

Обе формулы являются формулами массива, поэтому вам нужно ввести их, нажав CTRL+SHIFT+ENTER.