The Guide to Excel

Морской бой в 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.
Бывает, что они мешают работе, и их не удается убрать. Скрыть их можно так:

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

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