Вопрос или проблема
У меня есть таблица с данными, которая похожа на таблицу ниже:
Я хотел бы отобразить таблицу справа от этой таблицы, чтобы показать каждый цвет продукта в своем столбце:
Таким образом, рабочий лист будет выглядеть примерно так:
- Когда я обновляю таблицу слева, я хотел бы, чтобы таблица справа обновлялась автоматически (включая добавление и удаление строк).
После поиска в Google я нашел несколько примеров, но ни один из них, похоже, не делает то, что я хотел бы:
-
Данные можно фильтровать, но я не уверен, можно ли фильтровать с помощью нескольких таблиц.
-
Я попробовал сводную таблицу, но не смог получить ничего, кроме итогов. Возможно, я что-то сделал не так, но единственный вид, который я смог получить, был итогами и суммами значений – я не смог создать вторую таблицу, используя сводную таблицу.
-
Можно ли сделать это с помощью VLookup, но не иметь пустых записей, где нет совпадений?
Предположим, что таблица Продукт:Цвет:Цена находится в A1:C9, а таблица Красный:Цена:Синий:Цена:Зеленый:Цена находится в F1:K9:
В F2 введите:
=IF(ROW()=2,IFERROR(INDEX($A$2:$A$9,MATCH(F$1,$B$2:$B$9,0)),""),IFERROR(INDEX(OFFSET($A$2:$A$9,MATCH(F1,$A$2:$A$9,0),0),MATCH(F$1,OFFSET($B$2:$B$9,MATCH(F1,$A$2:$A$9,0),0),0)),""))
В G2:
=IF(F2="","",IFERROR(INDEX($C$2:$C$9,MATCH(F2,$A$2:$A$9,0)),""))
Затем перетащите обе формулы до K9. Пожалуйста, дайте знать, если это работает/застрял/ошибка.. ( :
Надеюсь, это поможет.
Ответ или решение
Для того чтобы создать динамическую таблицу в Excel, которая будет обновляться автоматически при изменении исходных данных, можно воспользоваться формулами, позволяющими динамически извлекать информацию и перенаправлять её в новый формат, как описано в вашем запросе.
Шаги для реализации задачи
1. Подготовка исходной таблицы
Предположим, ваша исходная таблица находится в диапазоне A1:C9, где A — это Название товара, B — Цвет, C — Цена.
2. Создание динамического столбца для каждого цвета
Нам нужно создавать новую таблицу в диапазоне F1:K9, где для каждого столбца мы разместим соответствующий цвет и его цену:
- В ячейку F1 поместите текст «Красный», в G1 — «Синий», в H1 — «Зеленый» и так далее.
3. Ввод формул в новые колонки
Теперь мы заполним ячейки F2 и G2 формулами, которые будут динамически извлекать данные из вашей исходной таблицы.
Формула для ячейки F2:
=IF(ROW()=2,IFERROR(INDEX($A$2:$A$9,MATCH(F$1,$B$2:$B$9,0)),""),IFERROR(INDEX(OFFSET($A$2:$A$9,MATCH(F1,$A$2:$A$9,0),0),MATCH(F$1,OFFSET($B$2:$B$9,MATCH(F1,$A$2:$A$9,0),0),0)),""))Формула для ячейки G2:
=IF(F2="","",IFERROR(INDEX($C$2:$C$9,MATCH(F2,$A$2:$A$9,0)),""))4. Копирование формул
Перетащите формулы из ячеек F2 и G2 до K9, чтобы заполнить всю новую таблицу.
5. Обработка результата
Эти формулы позволят вам автоматически подтягивать соответствующие значения из исходной таблицы в новую таблицу, где значения будут акумулироваться по цветам. Формула в ячейке F2 ищет названия товаров по исходному цвету, а формула в ячейке G2 извлекает цены для соответствующих товаров.
6. Как обновления влияют на динамичность таблицы
При изменении значений в вашей исходной таблице автоматическое обновление нового представления будет осуществляться благодаря динамическим формулами.
Рекомендации
-
Фильтрация данных: Если вы хотите фильтровать данные по нескольким критериям, рассмотрите использование функций фильтрации, таких как
FILTERилиUNIQUE(в зависимости от версии Excel). -
Проверьте работоспособность формул: Всегда проверяйте, верно ли работают формулы на малом объеме данных, прежде чем применять их к большому массиву.
-
Подход к структурированию данных: Убедитесь, что структура ваших данных остается постоянной; это значительно упростит работу с формулами.
Таким образом, с помощью простых формул вы сможете создать динамическое представление данных в Excel, что упростит работу с вашим массивом информации. Надеюсь, это руководство было полезным и поможет вам успешно реализовать вашу задачу.