Автоматом из одной таблицы в другую 🖥

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

В примере выгружаем информацию из дневника (из предыдущего поста). Диапазон выгружаемой информации можно указать любой.

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

#Формулы

Преобразование текста 😉

Текстовые формулы Excel помогают решать самые разные задачи.
Например, вот так можно изменить формат телефонного номера ("+7" вместо "8"):
🔹сохранить все символы строки, кроме первой цифры (ПРАВСИМВ / RIGHT и ДЛСТР / LEN);
🔹дописать в начале "+7".

#Формулы

"Бесполезные" формулы ссылок ❓

В группе формул "Ссылки и массивы" есть АДРЕС (ADDRESS) и ДВССЫЛ (INDIRECT). Функционал у них такой:
🔹 АДРЕС даёт возможность получить адрес ячейки на листе по номерам строки и столбца.
🔹 ДВССЫЛ возвращает ссылку на ячейку, заданную текстовой строкой.
Например:

=АДРЕС(2;3)

Результат: "$C$2"

=ДВССЫЛ("А3")

Результат: то, что вписано в ячейку А3.

=ДВССЫЛ(А3)

Результат: то, что вписано в ячейку с адресом, указанным в А3.

Так получилось, что только сегодня я впервые использовала их в работе.
Задача: отрисовка обычного графика.
Сложность: ячеек (из которых формируется ряд значений) очень много, и они расположены не рядом, а по одному значению через каждые 15 строк.
Решение: использовать АДРЕС, чтобы сформировать список адресов; использовать ДВССЫЛ, чтобы прочитать значения из этих адресов.

#Формулы

Мощь Google таблиц в одной функции ☄️

Максимально полезный и мощный инструмент Google Sheets - функция QUERY.
QUERY позволяет сделать выборку из таблицы с помощью SQL-запроса, произвести вычисления, отсортировать, сгруппировать. Работает со знакомыми словами SELECT, WHERE, GROUP BY, ORDER BY и проч.

Большое преимущество QUERY - не нужно ничего дополнительно нажимать, чтобы обновить результаты. Все происходит мгновенно! Лично мне очень пригодилось для построения интерактивных дашбордов.

#Формулы

Если без "если" 👨‍💻

Удобной альтернативой функции ЕСЛИ (IF) может служить функция ПЕРЕКЛЮЧ (SWITCH). В ней задается выражение, список его возможных значений и результаты для вывода в случае совпадения. Если совпадения нет, можно вывести необязательное стандартное значение.

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

#Формулы

Трекер задач в Google Sheets 👌

Встретился на просторах интернета отличный способ создать простой трекер задач с прогрессом (и всего за минуту!).

Для этого нужно вставить флажки в ячейки, а рядом поместить формулу спарклайна:

SPARKLINE(диапазон; параметры_спарклайна)

В этом видео добавила условие для цвета, чтобы внести разнообразие в визуальной части.

Функции:
SPARKLINE, СЧЁТЕСЛИ - COUNTIF, ЕСЛИ - IF, ИСТИНА - TRUE.

P.S. Ещё много приемов - на странице exceldictionary в известной социальной сети.

#Формулы

Точный возраст 🗓

В Excel есть функция ДОЛЯГОДА, которая подсчитывает количество дней между двумя датами, и выдает результат в годах с дробной частью.

Функция часто применяется для расчета возраста, времени между двумя событиями, а также финансовых расчетов. Ее особенность в том, что можно задать "базис", чтобы изменить способ вычисления дня (например, считать не 365, а 360 дней в году).

#Формулы

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

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

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

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

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

#Формулы

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

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

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

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

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

#Формулы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В итоге:

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

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

#Формулы

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

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

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

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

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

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

#Формулы

Прогноз в Excel 🔮

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

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

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

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

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

#Формулы

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

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

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

#Формулы

Сложный запрос с 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: 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 году выбор пал на тему массивов. Диапазоны ячеек можно обрабатывать как массивы (одномерные – если это всего одна строка или один столбец, и двумерные – если это матрица). Но использовали ли вы когда-нибудь формулы массива? Это может быть вполне удобно для ряда задач.

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

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

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

#Формулы