Тепловая карта активности сотрудников ↗️

Кроме диаграмм, отличным инструментом визуализации служит условное форматирование. Например, благодаря ему и формуле СЧЁТЕСЛИМН (COUNTIFS) можно за считанные минуты создать тепловую карту, иллюстрирующую активность сотрудников в течение недели.

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

Чтобы автоматически подсчитать количество задач для определенного сотрудника в определенный день, используйте такую формулу:

=СЧЁТЕСЛИМН(диапазон_1; условие_1; диапазон_2; условие_2)

Например,

=СЧЁТЕСЛИМН($A$2:$A$6; "25.11.2024"; $B$2:$B$6; "Анна")

или

=СЧЁТЕСЛИМН($A$2:$A$6; F$1; $B$2:$B$6; $E2)

#Приемы

Прогноз в Excel 🔮

Помимо диаграмм, сводных таблиц и отчетов, в MS Excel есть возможность заглянуть в будущее 😲
А именно – посчитать прогнозные значения с помощью ПРЕДСКАЗ (FORECAST) на основе метода линейной регрессии.

Функцию ПРЕДСКАЗ можно применить, например, если необходимо:
– оценить доход при изменении количества клиентов;
– вычислить объем производства при увеличении времени работы;
– спрогнозировать продажи в зависимости от расходов на рекламу (как в примере на картинке);
– и др.

Синтаксис функции:

=ПРЕДСКАЗ(X; Известные_значения_Y; Известные_значения_X)

где X - значение, для которого выполняется прогноз.

#Формулы

Умные таблицы в Excel 👍

Если вы работаете с таблицами, в которых все время нужно обновлять данные, фильтровать, сортировать и т.д., и это занимает больше времени, чем хотелось бы, то умные таблицы – это инструмент, который сделает работу удобнее.

❓ Что это такое умные таблицы?

Это специальный формат таблиц в Excel, который включает в себя:

🔘именование элементов таблицы (например, в формулах пишется не =СУММ(A1:A10), а =СУММ(Таблица1[Продажи]));
🔘автозаполнение формул (введите формулу в одну ячейку, а Excel автоматически заполнит остальные, не надо растягивать ячейку с формулой);
🔘автоматические фильтры (сразу добавляются на заголовках);
🔘стили таблиц (можно не форматировать вручную, а просто выбрать красивый стиль оформления);
🔘срезы для интерактивной фильтрации (специальные визуальные фильтры в виде панелей с кнопками);
🔘подключение автоматического подсчета итогов (строка итогов в конце таблицы с возможностью выбора функции по каждому столбцу – сумма, среднее, количество и т.д.).

❓ Как создать умную таблицу?

1. Выделите диапазон данных.
2. Выберите "Вставка" → "Таблица".
3. Убедитесь, что включена галочка "Таблица с заголовками".
4. Profit!

#Таблицы

Как найти и исправить ошибки в формулах ✅

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

Для этого перейдите на вкладку "Формулы", и выберите "Проверка ошибок" в разделе "Зависимости формул". Далее вы сможете:
🔘просмотреть детальную справку по текущей ошибке;
🔘проверить все этапы вычисления (значения частей формулы, вычисляемых в порядке расчета формулы);
🔘пропустить ошибку;
🔘изменить выражение в строке формул.

#Формулы

Сложный запрос с QUERY 🔤

Функция QUERY — один из мощных и очень полезных инструментов в Google Sheets, который позволяет работать с таблицами как с базой данных.

В сегодняшнем примере приведу вариант запроса для QUERY, объединяющий фильтрацию, сортировку и группировку.

➡️ Дана таблица с именами сотрудников, отделами и значениями премий.
➡️ Нужно вывести суммарные значения премий по отделам, отсортировав их по убыванию.

=QUERY(A1:D5; "SELECT B, SUM(C) WHERE C > 0 GROUP BY B ORDER BY SUM(C) DESC LABEL SUM(C) 'Сумма продаж'"; 1)

Здесь:
SELECT B, SUM(C) - выбор столбцов с отделом (B) и премиями (C)
WHERE C > 0 - фильтрация строк, где есть премия (больше 0)
GROUP BY B - группировка по отделу
ORDER BY SUM(C) DESC - сортировка по убыванию суммы
LABEL SUM(C) 'Сумма продаж' - заголовок для столбца с суммами

В самой функции QUERY:

=QUERY(данные; запрос; [заголовки])

#Формулы

"Текст по столбцам" – пошагово 💬

Сегодня речь пойдет об инструменте "Текст по столбцам" (Text to Columns), позволяющем разделить информацию в ячейках по нескольким отдельным столбцам на основе выбранного разделителя.

Пользователь выделяет столбец, над ячейками которого нужно произвести процедуру разделения по столбцам, и переходит в "Данные" -> "Текст по столбцам". Появляется "Мастер распределения текста по столбцам", включающий несколько шагов:

1. Указание формата данных
- "с разделителями", если нужно разделить по определенному знаку (символу)
- "фиксированной ширины", если разделить по ширине полей

2. Указание разделителя
Выбираете символ-разделитель. Также здесь доступна опция учета последовательных разделителей как одного, т.е. если у вас много пробелов, они будут считаться как один. Это полезно, когда у вас ситуация как в примере (и на картинке):

Иванов  Иван    Иванович

где нужно разделить на три столбца, а не на семь (с учетом пробелов).
"Ограничитель строк" используется, чтобы текст в пределах указанного ограничителя не делился по разделителю.

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

#Приемы

Группировка по датам в сводной таблице ☀️

Один из плюсов сводных таблиц заключается в том, что в них доступна мгновенная группировка данных по годам, месяцам, дням или даже часам. Конечно, только в том случае, если вы работаете с таблицей, содержащей даты :)

💬 Как это делается:
1. Выделите исходную таблицу → "Вставка" → "Сводная таблица".
2. Добавьте столбец с датой (в примере – "Дата") в строки, а столбец со значениями ("Сумма") – в значения.
3. Нажмите правой кнопкой мыши на любой дате, и выберите "Группировать".
4. Укажите уровень группировки (например, "Месяцы" и/или "Годы").

#Таблицы

Как разделить текст в Excel: 4 способа для любой ситуации 🔧

Представьте, что у вас есть задача – извлечь год и размер из артикулов:

PRD-2023-XL  
PRD-2022-M  
PRD-2023-L  

Предлагаю 4 способа, которыми это можно реализовать.

1️⃣ ПСТР (MID): базовый способ
Если структура текста предсказуема, используйте следующую формулу. Для года:

=ПСТР(A1; 5; 4)

Для размера:

=ПСТР(A1; 10; 2)

2️⃣ ПСТР + ПОИСК (SEARCH): когда структура непредсказуема
Если позиции данных могут меняться, используйте ПОИСК. Для года:

=ПСТР(A1; ПОИСК("-"; A1)+1; 4)

Для размера, и здесь еще подключается ДЛСТР (LEN):

=ПСТР(A1; ПОИСК("-"; A1; ПОИСК("-"; A1)+1)+1; ДЛСТР(A1)-ПОИСК("-"; A1; ПОИСК("-"; A1)+1))

3️⃣ Мгновенное заполнение (Flash Fill): быстрый способ
– Введите вручную результат для первой строки (например, 2023 в столбце B).
– Выберите "Данные" → "Мгновенное заполнение".
– Excel распознает шаблон и заполнит остальные строки.

4️⃣ Текст по столбцам: если нужно разделить вручную
– Выделите диапазон с данными.
– Перейдите во вкладку "Данные" → "Текст по столбцам".
– Выберите разделитель (например, дефис "-").
– Excel автоматически разобьёт текст на столбцы.

А как вы обычно разделяете текст? Поделитесь вариантами в комментариях 😉

#Формулы #Приемы

10 полезных статистических формул в Excel ➕

В сегодняшнем посте собраны 10 формул для расчета статистических показателей и метрик, которые позволяют анализировать данные.

1. СРЗНАЧ (AVERAGE) - среднее арифметическое:

=СРЗНАЧ(A1:A10)

2. МЕДИАНА (MEDIAN) - центральное значение отсортированного набора данных:

=МЕДИАНА(A1:A10)

3. МОДА.ОДН (MODE.SNGL) - наиболее часто встречающееся или повторяющееся значение в массиве:

=МОДА.ОДН(A1:A10)

4. ДИСП.В (VAR.S) – дисперсия по выборке (среднее арифметическое квадратов отклонений всех вариантов выборки от её средней):

=ДИСП.В(A1:A10)

5. СТАНДОТКЛОН.В (STDEV) – стандартное отклонение по выборке (степень изменчивости или разброса данных):

=СТАНДОТКЛОН.В(A1:A10)

6. ЭКСЦЕСС (KURT) - относительная остроконечность или сглаженность распределения по сравнению с нормальным распределением:

=ЭКСЦЕСС(A1:A10)

7. КВАРТИЛЬ.ВКЛ (QUARTILE.INC) - квартиль заданного набора данных (число, которое делит набор на четыре равные части):

=КВАРТИЛЬ.ВКЛ(A1:A10; 1)

8. КОВАРИАЦИЯ.В (COVARIANCE.S) - ковариация (совместная изменчивость двух случайных величин):

=КОВАРИАЦИЯ.В(A1:A10; B1:B10)

9. КОРРЕЛ (CORREL) - корреляция (взаимосвязь между разными показателями):

=КОРРЕЛ(A1:A10; B1:B10)

10. СРГАРМ (HARMEAN) - гармоническое среднее (среднее для набора чисел, которые лучше представлены их обратными значениями):

=СРГАРМ(A1:A10)

#Формулы

Машинное обучение сквозь призму Excel 🔖

Такая книга выйдет в январе 2025 г. в издательстве ДМК Пресс. Отличная возможность приобрести опыт в создании моделей машинного обучения прямо в Excel.

Изучение датамайнинга на примерах и упражнениях позволит вам заглянуть за кулисы сложных методов и научиться эффективно работать с данными. В отличие от большинства специализированных инструментов, которые работают по принципу «черного ящика», Excel позволяет просматривать промежуточные результаты в процессе применения метода и досконально понимать происходящее.
Вы узнаете о преимуществах различных подходов машинного обучения на примере данных относительно небольшого объема и научитесь использовать встроенные инструменты Ехсеl для создания визуальных представлений, что поможет вам находить скрытые закономерности в данных.

Предыдущий пост о книгах был тут.

#Литература

Дорогие друзья, с наступающим Новым годом! 🔔

Желаю вам успехов во всех ваших проектах и легкости в изучении нового!

Спасибо, что читаете мой канал (к слову, ему уже больше 3 лет! 😎). Я, как и прежде, стремлюсь делиться с вами полезной информацией, и очень рада, когда это помогает вам в работе.

Отдельное спасибо тем, кто оставляет комментарии – так появляются новые темы для постов, а заодно и мотивация узнавать все больше об инструментах и приемах электронных таблиц 👍

Ура! И до встречи в 2025 году! 🥂

Формулы массива в Excel 📄

Для первого поста в 2025 году выбор пал на тему массивов. Диапазоны ячеек можно обрабатывать как массивы (одномерные – если это всего одна строка или один столбец, и двумерные – если это матрица). Но использовали ли вы когда-нибудь формулы массива? Это может быть вполне удобно для ряда задач.

В примере мы собираемся рассчитать сумму продаж, превышающих 1000. Для этого используем формулу:

=СУММ((B2:B5>1000)*B2:B5)

При этом фрагмент B2:B5>1000 дает количество подходящих под условие элементов (т.е. 2), а дальше мы выбираем именно их, и применяем к ним формулу суммы.

#Формулы

Выбор линии тренда 📈

Линии тренда используются для выявления общих тенденций в данных, сглаживания колебаний, прогнозирования и анализа взаимосвязей между переменными.

Существует несколько вариантов линий тренда, которые можно использовать в Excel. Чтобы добавить линию тренда на график, нажмите правой кнопкой мыши по нужному ряду точек и выберите "Добавить линию тренда".

Варианты:
🔘Линейная (для данных, которые изменяются равномерно, с постоянной скоростью)
🔘Логарифмическая (для данных, которые сначала быстро растут или убывают, а затем постепенно стабилизируются)
🔘Полиномиальная (для данных с колебаниями, и анализа большого набора данных)
🔘Степенная (для данных с зависимостью, где скорость изменения постоянна)
🔘Экспоненциальная (для данных с ускоряющимся ростом)
🔘Скользящее среднее (для сглаживания резких колебаний в данных)

Чтобы оценить разницу визуально, взгляните на картинку, прикрепленную к этому посту.

Что же касается уравнений, которые используют линии тренда: они приведены в этом материале от MS Office.

#Анализ

Excel — в МУСОР. Собрали для вас нейронки, которые сгенерят ЛЮБЫЕ таблицы за секунду. Вы не утонете в формулах и цифрах и сэкономите КУЧУ ВРЕМЕНИ.

Заклинателем таблицем становимся так:

1️⃣ ChatCSV — личный дата-аналитик. В него можно закинуть CSV-файлы и задавать вопросы. Он проанализирует таблицы и шустро выкатит результат.

2️⃣ SheetAI — генерит ЛЮБЫЕ таблицы по одному промпту. Работает очень точно, а готовый результат можно сразу редачить.

3️⃣ Genius Sheets — запускает сложнейшую аналитику за один клик и подключается к данным с помощью текстовых запросов.

4️⃣ Rows — ИИ-агент, который проанализирует, сделает саммари данных и ответит на любые вопросы по ним.

5️⃣ Equals — таблица для связи и работы с данными в РЕАЛЬНОМ времени. Помогает для различных онлайн-демонстраций.

6️⃣ Numerous AI — на изи интегрируем ChatGPT в Google Sheets и получаем ультимативного аналитика.

7️⃣ Julius AI — «съедает» файлы всевозможных форматов и мгновенно вытаскивает из них экспертные данные.

Сохраняем себе и больше не боимся таблиц.

👍 Бэкдор