Как используют функцию СЦЕПИТЬ для чартов дашборда в Excel
Пример создания простых формул для поиска, сегментирования, фильтрации и выборки значений из исходных данных без использования сводных таблиц. Таким образом новые данные могут автоматически обновляться на всех чартах дашборда Excel. Даже в режиме потока.
Формула ИНДЕКС и ПОИСКПОЗ с несколькими условиями в Excel
Функция СЦЕПИТЬ позволяет объединять значения из разных ячеек в одну текстовую строку. Достаточно лишь в аргументах функции указать ссылки на ячейки значения, которых нужно сцепить в одну строку.
Где и как применять функцию СЦЕПИТЬ? Например, в формулах ИНДЕКС и ПОИСКПОЗ для поиска значения в таблице по нескольким условиям.
Для исходной таблицы с помощью функции СЦЕПИТЬ для каждой строки сгенерируем свой уникальный ID на основе исходных данных этой же таблицы. ID состоит из двух частей. Значение – Год и значение – Месяц.
Определим два условия для поиска и выборки значений из исходной таблицы. Выборку значений по условиям реализуем с помощью функции ИНДЕКС. В первом аргументе функции укажем ссылку на диапазон значений. Это последний столбец в исходной таблице, из которого будем выбирать значения. Для второго аргумента нам необходимо указать номер строки, но его сначала нужно найти.
Теперь чтобы найти значение по двум условиям нам нужно в первом аргументе функции ПОИСКПОЗ также использовать функцию СЦЕПИТЬ, которая объединит оба условия в один код ID строк исходной таблицы. В результате функция ПОИСКПОЗ найдет по ID коду нужный номер строки и передаст ее в функцию ИНДЕКС, которая вернет нам найденное значение соответственное двум условиям.
Чтобы каждый раз не генерировать столбец с кодами ID для строк таблицы мы можем использовать функцию СЦЕПИТЬ и во втором аргументе функции ПОИСПОЗ. Только в таком случае в аргументах указываем уже ссылки не на ячейки, а на целые диапазоны ячеек со значениями годов и месяцев.
Для удобной работы добавим два выпадающих списка для двух условий и условное форматирование.
Основы этого принципа будем использовать в алгоритмах формул для подготовки данных перед выводом на визуализацию.
Шаблон формул обработки данных для чартов дашборда
Для визуализации данных часто приходится использовать функцию СЦЕПИТЬ, особенно при разработке интерактивных дашбордов. Ведь там часто приходиться выполнять поиск и делать выборку данных из таблиц по нескольким условиям. Обратите внимание на формулы шаблона дашборда для выборки исходных данных с листа «Data» на основе условий, определенных на листе «Processing». Там используется функция СЦЕПИТЬ.
Сам дашборд выглядит вполне презентабельно. Имеет два вида дизайна:
- Темный для ночного режима.
- Светлый для работы в дневное время.
Скачать дашборд для трекинга криптовалютного портфеля в Excel 
Все формулы максимально упрощенные и находятся на листе «Processing». На рабочем листе «Data» заполняем таблицу исходными значениями. Дашборд очень простой все его чарты без сложных особенностей. Шаблон можно свободно использовать и расширять под свои потребности.
