Arhn - архитектура программирования

Вернуть значение HLOOKUP для всего листа в Excel

У меня есть Sheet2 с несколькими элементами (выделены жирным шрифтом), которые содержат несколько значений.

Лист2

В Sheet1 (ниже) мне нужно вернуть это значение, используя эту формулу для B2:

=ЕСЛИ(A2=0,,ГПР($A2,Лист2!1:1048576,ВПРАВО(B$1,2)+1,0))

и для С2

=ЕСЛИ(B2=0,,ГПР($A2,Лист2!1:1048576,ВПРАВО(C$1,2)+1,0))

ДЛЯ В6

=ЕСЛИ(A6=0,,ГПР($A6,Лист2!1:1048576,ВПРАВО(B$1,2)+1,0))

Sheet1

Но, как вы можете видеть, он возвращает только первую «строку» таблицы (A, B, C, D), а не таблицу ниже (E, F, G, H). Я думаю, что это очень простые изменения, но я все еще не могу найти.

excel vlookup
04.02.2017

Ответы:


1

Я подозреваю, что ваши образцы данных не совсем точно представляют фактический набор данных, поэтому транспонированный Функция ИНДЕКС может заменить вашу функция HLOOKUP.

Образец листа 2:

group A group B group C group D
A-001   B-001   C-001   D-001
A-002   B-002   C-002   D-002
A-003   B-003   C-003   D-003
A-004   B-004   C-004   D-004
A-005   B-005   C-005   D-005
        B-006   C-006   D-006
        B-007   C-007   
                C-008   
                C-009   

group E group F group G group H
E-001   F-001   G-001   H-001
E-002   F-002   G-002   H-002
        F-003

Формула Sheet1!B2:

=INDEX(Sheet2!$A:$D, COLUMN(B:B)+INT((ROW(1:1)-1)/4)*11, MOD(ROW(1:1)-1, 4)+1)&TEXT(,)

Скопируйте или заполните как справа, так и снизу. Разрешение TEXT в строку нулевой длины не позволяет INDEX возвращать ноль, когда он встречает пустую ячейку. INT/MOD/СТРОКА/COLUMN — это простые математические операции для разрешения номер строки и столбца для ИНДЕКС.

введите здесь описание изображения

04.02.2017
  • Это Золото! Хотя я все еще пытаюсь понять, почему ROW(1:1)-1)/4)*11 и &TEXT(,). Где я могу дополнительно изучить это? для получения более подробной информации ссылка предоставила не так много информации. Очень благодарен за вашу помощь. 04.02.2017
  • Я действительно хочу больше узнать об этом. Не могли бы вы дать несколько книг или ссылку, чтобы копать дальше? 04.02.2017
  • TEXT(,) — это просто еще один способ разрешить строку нулевой длины; например "". Поместите =INT((ROW(1:1)-1)/4)*11 в неиспользуемую ячейку и заполните вниз и вправо, чтобы увидеть, как он корректирует номер строки, возвращаемый функцией ИНДЕКС. Кстати, если данные в Sheet2 часто меняются, вы можете использовать INT((ROW(1:1)-1)/4)*Sheet2!ROW(11:11) для автоматической компенсации вставленных или удаленных строк для смещения. Проверьте все ссылки, которые я предоставил в своем ответе, на официальные документы support.office.com. 04.02.2017
  • Спасибо. Но можно ли использовать функцию ПРОСМОТР? или другую функцию, потому что эта формула является статической. Это означает, что когда, например, я меняю строку A7 на группу H вместо F, данные в столбцах B, C, D,.. не изменились. 04.02.2017
  • Да, вы можете преобразовать формулу в поиск, но вам придется использовать ЕСЛИОШИБКА для циклического просмотра каждого из четырех столбцов. 04.02.2017
    • Новые материалы
    Коллекции публикаций по глубокому обучению
    Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
    Представляем: Pepita
    Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
    Советы по коду Laravel #2
    1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
    Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
    Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
    3 способа решить квадратное уравнение (3-й мой любимый) -
    1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
    Создание VR-миров с A-Frame
    Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
    Демистификация рекурсии
    КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..