Функции EXPAND / РАЗВЕРНУТЬ и MAKEARRAY + комплексный пример: собираем ТОП-N сделок из всех таблиц по списку одной формулой
Новый видеоурок в курсе "Магия новых функций Excel"
Если вы покупали этот курс, заглядывайте в личный кабинет — вас ждет новый урок про функцию EXPAND / РАЗВЕРНУТЬ и большой пример на многие функции из курса.
Также недавно был добавлен урок "Функция ПРОСМОТРX / XLOOKUP: все нюансы "новой ВПР" для объединения таблиц".
https://shagabutdinov.ru/magic-excel
Новый видеоурок в курсе "Магия новых функций Excel"
Если вы покупали этот курс, заглядывайте в личный кабинет — вас ждет новый урок про функцию EXPAND / РАЗВЕРНУТЬ и большой пример на многие функции из курса.
Также недавно был добавлен урок "Функция ПРОСМОТРX / XLOOKUP: все нюансы "новой ВПР" для объединения таблиц".
https://shagabutdinov.ru/magic-excel
shagabutdinov.ru
Ренат Шагабутдинов | Магия новых функций Excel
Для новичков и опытных пользователей. Создать интерактивный отчёт с выбором параметров. Сводные таблицы. Разделить текст на отдельные символы и многое другое. Доступ к курсу сразу после оплаты.
❤4
Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа Панель быстрого доступа (Quick Access Toolbar, QAT) — простейший инструмент для настройки интерфейса "под себя". Туда можно добавить любую команду — как с ленты, так и из списка вообще всех команд и инструментов Excel.
Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.
Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.
👍16
This media is not supported in your browser
VIEW IN TELEGRAM
Нумеруем только видимые строки
Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.
Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.
В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).
А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.
Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:
$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.
_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки
Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.
Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.
В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).
А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.
Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)
$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.
_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки
🔥28❤8👍4👎1
Магия табличных формул: видеоуроки по подписке
Друзья, на Sponsr уже более 40 видеоуроков (10-30 минут) с исходными и готовыми файлами!
Здесь полный список всего, что уже есть:
Формулы
Вступительное видео. Книги по табличным формулам
1.1 Формулы, ссылки и имена
1.2. Ссылки на таблицы, листы и книги
1.3 Копирование и перенос формул. Скрытие формул
1.4 Операторы в формулах. Приоритет операторов
1.5 Функции и их аргументы. Автосумма. Волатильные функции
1.6 Ошибки в формулах
Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек
1.7 Формулы в Google Таблицах
Даты
2.1 Дата и время: основы. Текущие дата и время: вручную и функциями
2.2 Функция ДАТА / DATE. Формируем дату из элементов, создаем последовательность дат, «собираем» дату из текста
2.3 Извлекаем элементы из даты: месяц и год, номер недели, день недели номером и текстом, квартал
2.4 Функции для вычислений с рабочими днями. Производственный календарь
2.5 Время. Текущее время, извлечение часов, форматирование времени и продолжительности
Тексты
3.1 Объединение текста. Функция ТЕКСТ. Мгновенное заполнение
3.2 Замена текста: окно «Найти и заменить» и функции ПОДСТАВИТЬ, ЗАМЕНИТЬ
3.3 Извлекаем фрагменты из текста. Мгновенное заполнение, текст по столбцам и «старые» формулы
3.4 Разделяем текст формулами. Функции ТЕКСТРАЗД / TEXTSPLIT и SPLIT
3.5 Регулярные выражения
Логика и условия
4.1 Логические значения и флажки
4.2 Функции И (AND), ИЛИ (OR). Проектная диаграмма
4.3 Функции ЕСЛИМН / IFS, ПЕРЕКЛЮЧ / SWITCH, ВЫБОР / CHOOSE
4.4 Условное форматирование с формулами
4.5 Проверка данных с формулами
4.6 СУММЕСЛИМН / SUMIFS и компания
4.7 Элементы управления и СУММЕСЛИМН. Флажок и полоса прокрутки
4.8 Функция СУММПРОИЗВ / SUMPRODUCT
А впереди формулы массива, LAMBDA, Google Таблицы, пользовательские функции и многое другое.
Есть еще только 4 места со скидкой за 390 рублей в месяц. При подписке на год еще + скидка. А еще на Sponsr можно приходить в "личку" с табличными вопросами. Подписываться можно по ссылке:
https://sponsr.ru/excel_magic/
Друзья, на Sponsr уже более 40 видеоуроков (10-30 минут) с исходными и готовыми файлами!
Здесь полный список всего, что уже есть:
Формулы
Вступительное видео. Книги по табличным формулам
1.1 Формулы, ссылки и имена
1.2. Ссылки на таблицы, листы и книги
1.3 Копирование и перенос формул. Скрытие формул
1.4 Операторы в формулах. Приоритет операторов
1.5 Функции и их аргументы. Автосумма. Волатильные функции
1.6 Ошибки в формулах
Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек
1.7 Формулы в Google Таблицах
Даты
2.1 Дата и время: основы. Текущие дата и время: вручную и функциями
2.2 Функция ДАТА / DATE. Формируем дату из элементов, создаем последовательность дат, «собираем» дату из текста
2.3 Извлекаем элементы из даты: месяц и год, номер недели, день недели номером и текстом, квартал
2.4 Функции для вычислений с рабочими днями. Производственный календарь
2.5 Время. Текущее время, извлечение часов, форматирование времени и продолжительности
Тексты
3.1 Объединение текста. Функция ТЕКСТ. Мгновенное заполнение
3.2 Замена текста: окно «Найти и заменить» и функции ПОДСТАВИТЬ, ЗАМЕНИТЬ
3.3 Извлекаем фрагменты из текста. Мгновенное заполнение, текст по столбцам и «старые» формулы
3.4 Разделяем текст формулами. Функции ТЕКСТРАЗД / TEXTSPLIT и SPLIT
3.5 Регулярные выражения
Логика и условия
4.1 Логические значения и флажки
4.2 Функции И (AND), ИЛИ (OR). Проектная диаграмма
4.3 Функции ЕСЛИМН / IFS, ПЕРЕКЛЮЧ / SWITCH, ВЫБОР / CHOOSE
4.4 Условное форматирование с формулами
4.5 Проверка данных с формулами
4.6 СУММЕСЛИМН / SUMIFS и компания
4.7 Элементы управления и СУММЕСЛИМН. Флажок и полоса прокрутки
4.8 Функция СУММПРОИЗВ / SUMPRODUCT
А впереди формулы массива, LAMBDA, Google Таблицы, пользовательские функции и многое другое.
Есть еще только 4 места со скидкой за 390 рублей в месяц. При подписке на год еще + скидка. А еще на Sponsr можно приходить в "личку" с табличными вопросами. Подписываться можно по ссылке:
https://sponsr.ru/excel_magic/
Sponsr
Магия табличных формул. От A1 до LAMBDA
Видеоуроки по формулам Excel (и не только): все нюансы и правила для новичков, новые функции, файлы с данными для практики и готовыми примерами
❤7🔥1
This media is not supported in your browser
VIEW IN TELEGRAM
Автосумма: одним движением суммы по всем столбцам/месяцам.
Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.
А если — как в видео — выделить диапазон из нескольких столбцов и строк вместе с пустой строкой под ним и столбцом справа, то мы получим суммы по каждому столбцу и строке (и итоговую справа внизу).
Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.
А если — как в видео — выделить диапазон из нескольких столбцов и строк вместе с пустой строкой под ним и столбцом справа, то мы получим суммы по каждому столбцу и строке (и итоговую справа внизу).
👍20❤7