MS EXCEL: Как внести изменения во вкладках, показанных на Master

Это такой распространенный вопрос, я чувствую себя немного глупо, задавая его! Мастер лист имеет список имен детей и их классов. Вкладки разных классов. Есть столбец для проверки посещаемости; 0 за неявку 1 за подарок. После проверки это будет отображаться в столбце посещаемости на Мастер - помните, что есть около 10 вкладок. Я хочу что-то простое, как =Class1! но как мне заставить это работать? ИФ тоже подойдет, но это проще, особенно если дети меняют класс. Это сбивает с толку меня!

Источник

3 ответа

Ниже ответ основан на предположениях, которые я изложил в комментариях к вашему вопросу.

Обычным способом ссылки на информацию о посещаемости будет использование VLOOKUP (если известен лист, в котором вам нужно искать), как показано ниже:

=VLOOKUP(Master!A2,ClassA!A:B,2,FALSE)

Однако, поскольку лист поиска должен меняться в зависимости от столбца "Класс" на мастер-листе, используйте функцию INDIRECT для ссылки на имя листа, хранящееся в этом столбце. Использование будет как ниже: =IFERROR(VLOOKUP(Master!A2,INDIRECT("'"&B2&"'!A:B"),2,FALSE),"-")

Настройка справочных листов согласно рисунку ниже:

Источник

Вы можете сделать это с помощью запросов xl. Убедитесь, что данные для каждого класса на каждой вкладке отформатированы в виде таблицы. Все таблицы должны иметь ТОЧНО одинаковые заголовки. Остерегайтесь орфографии и случайных пробелов.

Перейдите на вкладку данных первого класса и выберите верхнюю левую ячейку. На вкладке меню "Данные" выберите пункт "Из таблицы" и выберите "Из таблицы". Откроется редактор запросов. Перейдите в раскрывающееся меню "Закрыть и загрузить" слева, выберите "Закрыть и загрузить в...", затем выберите "Только создать соединение". Нажмите Загрузить. Сделайте это для каждой вкладки класса.

Затем в Data\Get & Transform выберите "Новый запрос", перейдите к "Объединить запросы" и выберите "Добавить".

Появится всплывающее окно с вопросом, является ли это таблица двумя или тремя или более таблицами. Три или более в вашем случае, я думаю. С помощью кнопки "Добавить" добавьте все таблицы классов в поле "Таблицы для добавления". Затем выберите ОК.

Откроется редактор Power Query с предварительным просмотром всего набора данных.

Снова выберите "Закрыть и загрузить в..."

В диалоге загрузки оставьте "Таблица" выбранной. Выберите "Существующий рабочий лист:". Перейдите на свой главный лист и выберите желаемое местоположение.

Нажмите загрузить. Вуаля.

Это сортируемый фильтруемый стол. Обновите с помощью кнопки "Обновить все" на вкладке меню "Данные" или на вкладке динамического меню "Запрос".

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

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

Удачи.

Источник

Поскольку вы не предоставили пример данных на основании информации о ценах с вопросом, я создал это решение, которое отображает статус посещаемости на основном листе, используя записи из вкладок классов.

Как это устроено:

Формула в мастер-листе:

  • В клетке C4 чтобы получить посещаемость первого класса,

=IF(ISBLANK(ClassI!C3),"",IF(ClassI!C3=0,"Ab","Pr"))

  • В клетке C7 чтобы получить посещаемость класса II,

=IF(ISBLANK(ClassII!C3),"",IF(ClassII!C3=0,"Ab","Pr"))

  • Заполните формулу прямо тогда вниз.
  • При необходимости измените записи на листах и ​​ссылках на ячейки в формуле.
Источник
Другие вопросы по тегам