The Guide to Excel

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

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

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

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

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

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

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

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

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

#Литература

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

Сервис идеально подойдет не только для новичков, но и для опытных юзеров. Внутри:

— Куча книг по Excel и VBA;
— Готовые формулы, таблицы и диаграммы;
— Обучающие видосы;
— Справочники по функциям листов;
— Тесты для закрепления навыков.

А еще там очень удобный и красивый дизайн, покажите его своему фронтендеру. Юзаем библию Excel — здесь.

@exploitex

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: 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 автоматически разобьёт текст на столбцы.

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

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

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

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

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

#Таблицы

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

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

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

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

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

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

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

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

#Приемы

Сложный запрос с 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(данные; запрос; [заголовки])

#Формулы

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

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

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

#Формулы

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

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

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

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

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

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

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

#Таблицы

Прогноз в Excel 🔮

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

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

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

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

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

#Формулы

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

Кроме диаграмм, отличным инструментом визуализации служит условное форматирование. Например, благодаря ему и формуле СЧЁТЕСЛИМН (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)

#Приемы

Морской бой в Google таблицах 🛳

Сегодня делюсь с вами своей реализацией игры "Морской бой" внутри Google таблиц.

Игра написана на Apps Script, который позволяет не только автоматизировать расчеты в классическом смысле, но и создавать такие мини-игры, или мини-приложения.

В репозитории на Github подробно описано приложение, порядок его настройки и даны все исходные материалы.

Поделитесь идеями, какие еще игры можно было бы воплотить в электронных таблицах? 😀

https://github.com/ice-avalanche/battleship-google-sheets/tree/main

Хитрые приемы, чтобы скрыть данные 😈

Помимо обычного скрытия строк и столбцов, есть еще варианты, как защитить информацию в файле Excel. Предлагаю разделить способы по уровням защиты.

Уровень 1
Когда файлом делиться не нужно (например, показываете файл по видеосвязи)
– Обычное скрытие строк и столбцов.
– Формат шрифта под цвет ячейки.
– Нестандартные форматы ячеек: выделение ячейки, "Формат ячеек" -> "(все форматы)" -> установка формата, например:

;;; "★★★★★"

Уровень 2
Когда файлом делиться нужно
– Скрыть строки и столбцы, а сам лист защитить паролем, отключив возможность выделять диапазоны.
– Сделать лист "очень скрытым". Для этого войти в редактор VBA, выделить в списке нужный лист, и в окне Properties указать в свойстве Visible xlSheetVeryHidden.

Но вы спросите, человек ведь может открыть VBA и поменять это свойство?
Ответ: войдите в редактор VBA, перейдите в "Tools" -> "VBAProject Protection" -> "Protection", установите галочку в "Lock project for viewing" и задайте пароль. Теперь, если кто-то попытается открыть проект VBA, ему нужно будет ввести пароль.

P.S. Еще была мысль с удалением всех данных, если файл открыл посторонний. Но это не сработает, если человек отключил макросы 🤷🏻‍♀️ Поэтому если нужно защитить весь файл, используйте пароль для защиты книги.

#Приемы

Python для Excel: читаем данные 🐍

Если ваши задачи направлены на автоматизацию расчетов, но возможностей Excel недостаточно, можно реализовать это с помощью Python.

Существует несколько библиотек, направленных на работе с файлами Excel. Одной из них является openpyxl, и в сегодняшнем посте мы используем ее функционал для того, чтобы прочитать данные из файла xlsx.

Ниже – реализация (комментарии в коде):

#импорт библиотеки
import openpyxl

# загружаем файл Excel
book = openpyxl.load_workbook("C:\\Folder\\test.xlsx")

# выбираем лист
sheet = book.active

# выводим значения из столбца A
for row in sheet["A"]:
    print(row.value)

#Python

Фильтры через формулы 💡

Встроенный функционал фильтрации в Excel можно сделать еще более гибким – если задавать фильтры формулами (ФИЛЬТР, FILTER).

Например, у вас есть список задач с указанием их приоритетов и сроков выполнения. И вы хотели бы вывести только задачи с высоким приоритетом на эту неделю.

Можно воспользоваться формулой такого вида:

=ФИЛЬТР(A3:D7; (B3:B7="Высокий") * (C3:C7>=СЕГОДНЯ()) * (C3:C7<=СЕГОДНЯ()+7); "Нет задач")

В условиях используется знак умножения - это позволяет выбрать строки, удовлетворяющие всем условиям сразу. Если использовать знак "плюс", то будут выведены строки, удовлетворяющие хотя бы одному из условий.

#Формулы

Поиск по всей таблице 🔎

Сегодняшний пост посвящен поиску по строкам и столбцам одновременно. Это особенно актуально при работе с очень большими таблицами, когда они даже не умещаются на экране, и в них тяжело ориентироваться.

Чтобы найти значение на пересечении определенной строки и определенного столбца, можно воспользоваться функциями ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH).
ПОИСКПОЗ найдет индексы нужных строки и столбца, а ИНДЕКС возвратит по ним значение из нужной ячейки.

В итоге:

=ИНДЕКС(таблица_без_закреп; ПОИСКПОЗ(столбец; столбец_закреп; 0); ПОИСКПОЗ(строка; строка_закреп; 0))

Таблица_без_закреп - это диапазон без учета закрепленных строки (шапки) и столбца (первого)
Столбец - значение, которое ищем в закрепленном столбце (столбец_закреп)
Строка - значение, которое ищем в закрепленной строке (строка_закреп)

#Формулы

Функции динамических массивов. СОРТ ↗️

Функция СОРТ (SORT) - это одна из функций, предназначенных для работы с динамическими массивами (Dynamic Array Functions, DAF).

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

СОРТ получает на вход диапазон, и выдает его в отсортированном виде.

=СОРТ(массив;[индекс_сортировки];[порядок_сортировки];[по_столбцу])

Индекс_сортировки - это номер строки или столбца, по которым нужно сортировать.
Порядок_сортировки - по возрастанию (1) или убыванию (-1).
По_столбцу - по столбцам (ИСТИНА) или по строкам (ЛОЖЬ).

P.S. DAF доступны пользователям Office 2021 и Office 365.
#Формулы

Основы форматирования чисел🛍

Предлагаю вспомнить форматирование с использованием пользовательских форматов, так как это – очень даже полезная вещь)
Например, когда нужно отобразить число с текстом в виде «Количество: 150», «Мемов: 100500», «Оттенков серого: 50» 😉

Для этого:
🔹Выделите ячейку с числом.
🔹Нажмите правой кнопкой мыши и выберите "Формат ячеек".
🔹Во вкладке «Число» и меню «Числовые форматы» выберите «(все форматы)».
🔹В поле «Тип» введите формат в виде: "Количество:" #

#Данные

Полезные книги 📚

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

#️⃣ Работа в Excel
— Microsoft Excel 2019. Библия пользователя [2019] Джон Уокенбах
— Эффективная работа в Microsoft Excel [2021] Алан Мюррей
— Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 [2023] Билл Джелен

#️⃣ Аналитика
— Погружение в аналитику данных. От Excel к Python и R [2021] Джордж Маунт
— Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel [2021] Уэйн Л. Винстон
— Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel [2020] Альберто Феррари, Марко Руссо
— Power Query в Excel и Power BI: сбор, объединение и преобразование данных [2021] Гил Равив
— Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel [2020] Марко Руссо, Альберто Феррари

#️⃣ Макросы и программирование
— Программирование на VBA в Excel. Самоучитель [2024] Нина Комолова, Елена Яковлева
— Python для Excel. Современная среда для автоматизации и анализа данных [2021] Феликс Зумштейн

👋 Привет всем! Я вновь на связи!

После небольшого перерыва, связанного с рабочими проектами, я снова с вами, и уже подготовила новые посты ↗️ Вас ждёт много полезного контента:

💬 Советы и лайфхаки для работы в Excel и Google Sheets – от базовых функций до продвинутых формул и аналитики
✍️ Пошаговые инструкции и примеры для решения повседневных задач
🐍 Использование Python для автоматизации задач и работы с Excel – подключаем новые возможности!
❓ Еженедельные тесты и задачи, чтобы проверить знания и освоить что-то новое
⚡️ Разбор популярных инструментов и новых возможностей в таблицах

Поделитесь в комментариях, что вам будет особенно интересно, и до встречи в новых постах!

P.S. У нас по-прежнему активны категории: #Формулы , #Приемы , #Диаграммы , #Таблицы , #Данные , #Анализ , #VBA , #Полезное , #НепростыеЗадачи .

Еженедельные итоги 📅

Сегодня предлагаю вам решение для Google Таблиц, чтобы каждую неделю автоматически подсчитывать итоги.

Предположим, есть дата и определенная сумма, потраченная в этот день.
Но наша цель – получать суммарные расходы за неделю каждое воскресенье.

Простой путь, чтобы это сделать:
1. Определить день недели с помощью ДЕНЬНЕД (WEEKDAY).
2. Определить номер недели с помощью НОМНЕДЕЛИ (WEEKNUM).
3. Используя ЕСЛИ (IF) и СУММЕСЛИ (SUMIF), вычислить сумму за неделю, при условии, что это последняя строка недели.

=ЕСЛИ(день_недели=7; СУММЕСЛИ(все_номера_недель; данный_номер_недели; все_расходы); "")

#Формулы

Машинное обучение в Google Sheets ⚙️

Вы интересуетесь искусственным интеллектом, и хотели бы использовать машинное обучение прямо в Google таблицах?

Разработчики из TensorFlow Decision Forest Team создали специальную надстройку Simple ML for Sheets. Ее можно использовать для прогнозирования значений, выявления аномалий, поиска закономерностей и др.

Инструмент доступен бесплатно на Google Workspace Marketplace.

#Полезное

Собственное меню в Google Sheets. Часть 2 🖥

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

В примере из видео:

1. Новые элементы меню добавляются через addItem() и addSeparator().

2. Окно для ввода информации создается с помощью prompt(заголовок, текст, набор кнопок).

3. Результат из диалогового окна (getResponseText) записывается в ячейку активного листа или выводится в сообщении (alert).

#GoogleAppsScript

Собственное меню в Google Sheets 🖥

Для удобства при запуске своих приложений можно создать пользовательское меню с помощью Google Apps Script за пару минут.

Для этого нужно перейти в Apps Script и добавить функции для описания интерфейса и функционала.

🔴Чтобы отобразить свое меню, когда вы открываете файл таблиц, создайте функцию onOpen(), и добавьте в нее меню (createMenu) и кнопки (addItem).

🔴Каждой кнопке может быть назначено свое действие, поэтому понадобится создать дополнительные функции для каждой из них.

В примере из видео я создаю меню с единственной кнопкой "Информация", которая открывает диалоговое окно с сообщением.

Развитие темы - в следующем посте ✏️

#GoogleAppsScript

Сегодня – рубрика "По заявкам", или снова про Notion 🤩

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

К слову, существует масса готовых шаблонов для организации ваших данных в данном сервисе. Подобрать свой можно здесь.

P.S. В новых постах вернемся к электронным таблицам. Если вы хотели бы рассмотреть что-то конкретное (простое или сложное), напишите мне @theyear1703 .

#Приемы

Функция агрегатных результатов ➕

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

Функция АГРЕГАТ (AGGREGATE) является более мощным аналогом функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL). Она позволяет применять агрегатные функции с возможностью пропускать скрытые строки и ошибки.

Варианты функций внутри АГРЕГАТ охватывают вычисления количества, средних значений, максимума и минимума, суммы, медианы, стандартного отклонения и дисперсии, и т.д.

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

#Формулы

И снова привет! 👋
Возвращаюсь с полезными постами про Excel и Google Sheets!

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

Как, например, сегодня – пунктирные линии разрыва страниц в Excel.
Бывает, что они мешают работе, и их не удается убрать. Скрыть их можно так:

Файл - Параметры - Дополнительно - Параметры отображения листа - Показывать разбиение на страницы.

До встречи в новых постах!
#Приемы