В Excel мне нужно найти данные на листе 3 из данных на листе 1.

Question

У меня есть книга Excel с несколькими листами. В столбце 'SheetA'!W:Wперечислен текст, который можно найти в заголовке столбца в строке 'Sheet3'!1:1. Например, SheetA'!W42содержит "B16". Ячейка Sheet3'!CB1имеет текст заголовка "B16: Sample 40", поэтому это будет целевой столбец.

Пример: Образец'SheetA'!W42

В 'SheetA'!CD42есть формула, которая вычисляет номер образца, который необходимо найти в 'Sheet3'!A:A. В примере ниже CD42отображается вычисленное значение «30», которое можно найти в 'Sheet3'!A32.

Пример: образец данных из Sheet3, отображающий заголовки и пересечение заголовка столбца (B16) и строки, содержащей интересующий номер образца:

Цель состоит в том, чтобы извлечь содержимое ячейки на Листе 3, где пересекаются искомые столбец и строка, и отобразить его в ячейке на Листе А. В представленном примере на 'Sheet3'!CB:CBпересекающейся строке 32 и на Листе А будет отображено значение «0,1950581843».

Кто-нибудь знает, как этого можно добиться?

Answer 1

Учитывая ваши данные выше, я собираюсь сделать несколько предположений. Если все это правда, то HLOOKUPможет легко решить вашу проблему. В противном случае может потребоваться более сложная цепочка функций.

Предположения:

1. Все соответствующие тексты заголовков 'Sheet3'!1:1находятсяабсолютно уникальныйв этом ряду.
2. Все соответствующие тексты заголовков 'Sheet3'!1:1имеют формат «[X]: [Y]», где:
   • [X] — это значение, которое можно найти в 'SheetA'!W:W.
   • [Y] — значение, 'SheetA'!X:Xкоторое находится в той же строке, что и [X].
3. Все возможные значения 'SheetA'!CD:CDдоступны для поиска в 'Sheet3'!A:A, поэтому они будут находиться в той же строке, что и данные, которые предполагается извлечь.
   • Я просто добавляю это, потому что заметил, что в Sheet3 есть некоторое повторение этих значений, и хотел убедиться, что нет условий, при которых вы могли бы искать значение, 'SheetA'!CD:CDкоторое должно было сопоставляться с другим столбцом (и, следовательно, может выдавать неточные результаты при сопоставлении в 'Sheet3'!A:A).
   • Если повторение необходимо исключительно для удобства чтения, могу ли я предложить вместо этого использовать функцию «Закрепить области»?
4. Все соответствующие значения в 'Sheet3!A:A'идеально последовательны, начинаются с «1» в 'Sheet3'!A3, никогда не повторяются в этом столбце, ивсегдасортировка по возрастанию.

Учитывая вышеизложенные предположения, я построил собственные таблицы с образцами данных, которые примерно отражают ваш сценарий.

Вот частичный скриншот моего «Листа 3».

И частичный снимок моего «ЛистаА».

Список аналогов:

• Лист3
  • Мой A:A = Ваш A:A, BY:BY:, CA:CA:, ...
  • Мои B:B, C:C, D:D, ... = Ваши B:B, BZ:BZ, CB:CB, ...
• ЛистА
  • Мой А:А = Ваш W:W
  • Мой Б:Б = Твой Х:Х
  • Мой C:C = Ваш CD:CD
  • Мой D:D = Любой столбец, в который вы хотите поместить найденные данные.

Как вы можете видеть на втором снимке экрана, формула для D2 следующая:

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

Пошаговое описание формулы:

ГПРпозволяет вам просматривать диапазон ячеек по горизонтали для значения, а затем возвращать значение ячейки в том же столбце на основе относительной позиции строки. Он принимает четыре аргумента, три из которых являются обязательными:искомое_значение,таблица_массив,row_index_num,[диапазон_просмотра]. Это формула, которая выполнит всю работу по поиску нужных вам данных на Листе3 и помещению их в ячейку на ЛистеA.

• искомое_значениеэто значение, которое вы хотите HLOOKUPнайти первым. Это значениедолженприсутствовать в первом рядутаблица_массив, потому что это единственная строка, которая будет искаться. HLOOKUPтакже вернет только первое совпадение, поэтому эти значения также должны быть уникальными. Здесь мы используем CONCATENATEдля построения нашей строки поиска.
  
  • ОБЪЕДИНЯТЬпозволяет объединять различные строки и значения в одну строку. Он принимает ряд аргументов, упорядоченных в соответствии с последовательностью, в которой они должны быть размещены в результирующей строке.
    
    • А2— наш первый аргумент для CONCATENATE. Первая часть нашей строки будет «Имя образца» в A2.
    • ": "— наш второй аргумент для CONCATENATE. Это помещает двоеточие и пробел в строку, чтобы соответствовать формату значений в 'Sheet3'!1:1.
    • Би 2наш последний аргумент для CONCATENATE. Он извлекает "Sample ID" из B2, чтобы завершить синтаксис, используемый для заголовков в 'Sheet3'!1:1.
• таблица_массивэто ссылка на диапазон ячеек, HLOOKUPс которыми вы хотите работать. Помните, что первая строка должна содержатьискомое_значение. Этот диапазон также должен охватывать все возможные значения дляrow_index_num.
  • Лист3!А:Y— это ссылка на все ячейки в столбцах A–Y (единственные заполненные в моем Sheet3) из Sheet3. Это гарантирует, что любые данные, добавленные в новые строки позже, также будут в области поиска. Если данные будут добавлены в новые столбцы вместо строк, я бы хотел использовать ссылку Sheet3!1:32(в настоящее время 32 — последняя заполненная строка в моем Sheet3). Если данные могут быть добавлены новыми столбцамииДля новых строк я бы сослался на весь лист с помощью Sheet3!1:1048576или Sheet3!A:XFD. (Примечание: ссылка «весь лист» действительна для Excel 2013. Более ранние версии могут иметь меньшие ограничения по строкам/столбцам — настройте 1048576или XFDпо мере необходимости.)
• row_index_num— положительное целое число, представляющее позицию строки относительно самой верхней строки втаблица_массив. Это говорит, HLOOKUPкакую ячейку вы хотите вернуть из соответствующего столбца. Обратите внимание, что, поскольку HLOOKUPищет толькоискомое_значениев верхнем рядутаблица_массив, иrow_index_numне может быть отрицательным, вы не можете использовать его HLOOKUP(по крайней мере, само по себе) для возврата информации из ячеек, которые находятся вышеискомое_значение.
  • С2+2- Поскольку все значения в 'Sheet3'!A:Aидеально последовательны, без пропущенных целых чисел, и всегда будут упорядочены по возрастанию, мы можем использовать эти значения (также представленные в 'SheetA'!C:C) как индикаторы номеров строк для данных, которые мы хотим найти. Здесь +2для учета того факта, что нумерация начинается с "1" в строке 3 Листа3.
• [диапазон_просмотра]— необязательный аргумент для HLOOKUP. Параметры — ИСТИНА или ЛОЖЬ, указывающие, хотите ли вы разрешить приблизительные совпадения или следует разрешить только точные совпадения. Excel по умолчанию принимает значение ИСТИНА (приблизительное совпадение), если этот аргумент опущен, что часто может привести к нежелательному поведению — особенно если ваш лист не отсортирован определенным образом. Поэтому мы указываемЛОЖЬздесь, чтобы убедиться, что HLOOKUPбудет найдено только точное совпадение.

Адаптировав вышесказанное к макету вашего листа, я полагаю, что это формула, которая вам понадобится для ячейки 'SheetA'!CE42(предполагая, что именно туда вы хотите поместить данные):

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

Обратите внимание, что если ваши данные на Листе 3 располагаются правее столбца CB и/или данные могут быть добавлены в дополнительные столбцы, вам необходимо будет выполнить настройку.таблица_массивсоответственно.