Магия Excel

Автосумма: одним движением суммы по всем столбцам/месяцам. Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM. Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ). Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа. А если — как в видео — выделить диапазон из нескольких столбцов и строк вместе с пустой строкой под ним и столбцом справа, то мы получим суммы по каждому столбцу и строке (и итоговую справа внизу).

Магия табличных формул: видеоуроки по подписке Друзья, на 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/

Нумеруем только видимые строки Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL. Ибо она умеет обрабатывать только видимые (не скрытые) ячейки. В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе). А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки. Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2) $B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную. _ _ _ Ну а заодно напоминание про 🔥 клавиши: Ctrl + 9 — скрыть строку Ctrl + Shift + 9 — отобразить скрытые строки

Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа Панель быстрого доступа (Quick Access Toolbar, QAT) — простейший инструмент для настройки интерфейса "под себя". Туда можно добавить любую команду — как с ленты, так и из списка вообще всех команд и инструментов Excel. Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.

Быстрое удаление столбцов/строк/ячеек Ctrl и минус (Ctrl -) И всего делов! Если выделена вся строка листа или умной таблицы — она будет удалена без вопросов. То же самое со столбцами. Если выделена одна или несколько ячеек — появится диалоговое окно, как на скриншоте.

Функции EXPAND / РАЗВЕРНУТЬ и MAKEARRAY + комплексный пример: собираем ТОП-N сделок из всех таблиц по списку одной формулой Новый видеоурок в курсе "Магия новых функций Excel" Если вы покупали этот курс, заглядывайте в личный кабинет — вас ждет новый урок про функцию EXPAND / РАЗВЕРНУТЬ и большой пример на многие функции из курса. Также недавно был добавлен урок "Функция ПРОСМОТРX / XLOOKUP: все нюансы "новой ВПР" для объединения таблиц". https://shagabutdinov.ru/magic-excel

Табличные формулы: 20 приемов и советов для эффективной работы Друзья, вашему вниманию большая статья с видео и картинками: пачка приемов, горячих клавиш и прочей пользы для всех, кто пишет формулы в Excel. Уверены, что даже опытные табличные формулье найдут для себя что-то новое. Пишите в комментариях, что для вас новое и актуальное! https://shagabutdinov.ru/tpost/9ejyeuoy41-tablichnie-formuli-20-priemov-i-sovetov

Формула не вычисляется, а отображается как текст! Почему? 1 вариант. В ячейке стоит апостроф `. Он делает значение текстовым. Удалите его. Ну а если, наоборот, надо временно отключить формулу, используйте апостроф. 2 вариант. Забыли добавить знак "равно" =. 3 вариант. Текстовый формат (или пользовательский формат @). Поменяйте на числовой или общий. Но этого будет недостаточно: надо будет зайти в ячейку (F2 или двойной клик или строка формул) и ввести формулу снова, нажав Enter. 4 вариант. Вы в режиме "Показать формулы" (Вкладка "Формулы"). Formulas — Show Formulas. Этот режим де-факто превращает лист в строку формул: вы видите формулы, а не результаты вычислений; и вы видите значения без форматирования, как они есть.

На днях компания Microsoft анонсировала, что в следующих версиях Excel появится возможность (наконец-то!) автоматического обновления сводных таблиц. За это будет отвечать кнопка Автообновление (Auto Refresh) на вкладке Анализ сводной таблицы (PivotTable Analyze). Для любой отдельно взятой сводной таблицы можно будет либо нажать, либо отжать эту кнопку, переключая, таким образом, сводную в режим автоматического обновления, когда любые изменения в исходном диапазоне данных приведут к обновлению сводной "на лету", без необходимости отдельно нажимать кнопку Обновить (Refresh), как это требуется сейчас. В общем и целом - это, конечно, приятная новость, но реализовать подобное, на самом деле, можно весьма легко на любой версии и прямо сейчас. Причем даже для сводных на основе Power Query и модели данных Power Pivot, обновлять которые майкрософтовский Autorefresh не умеет 😉 Суть способа в создании специального макроса, который будет: 1. Перехватывать событие изменения листа с исходными данными 2. Определять, находится ли изменённая ячейка в таблице, по которой построена сводная (ведь менять могут и произвольные ячейки за её пределами). 3. Обновлять сводную В новом видео подробно разбираемся, как это сделать: Читать статью и смотреть видеоурок https://www.planetaexcel.ru/techniques/8/58518/ Смотреть видео на YouTube https://youtu.be/Ytz-UgTbBFk

Данные, которые вы вставляете из текстового файла, иногда вставляются в один столбец (как на картинке справа), а иногда — в разные столбцы (слева). Почему? Если в рамках текущей сессии (после открытия Excel) вы использовали "Текст по столбцам" (Text to Columns) с разделителем, данные будут автоматически разделяться по этому символу при вставке (в примере это запятая, с тем же успехом мог бы быть пробел или другой символ). Если вас такое поведение не устраивает, придется либо закрыть Excel после использования "Текста по столбцам", либо открыть этот инструмент еще раз, чтобы убрать в нем разделители на втором шаге.

Готовлю очередной вебинар для лектория крупной федеральной компании и тут в личные сообщения приходит такой отзыв — приятно! ❤️ Это мой любимый формат: когда не большая подробная программа по всем темам (хотя это тоже интересно), а периодические насыщенные встречи на час-полтора с пачкой прикладных решений и инструментов, про которые большинство пока не знает. Сейчас у многих больших компаний есть формат, когда с некоторой периодичностью приглашают спикеров с разными темами, в том числе и "хард-скиллами", как Excel. В моем случае — и с возможностью приходить со своими рабочими задачами и вопросами между встреч. Хотите организовать подобное у себя в компании? Добро пожаловать в личные сообщения (или оставьте заявку на сайте)

VisiCalc и первые табличные функции Сегодня немного истории, друзья. Excel — это не первые электронные таблицы, да-да! Первым было приложение VisiCalc, созданная в 1979 году Дэном Бриклином и Бобом Фрэнкстоном. "Визуальный калькулятор" — идея была в том, чтобы не менять все вводные для расчетов, как это приходится делать на обычном калькуляторе. И это свойство электронных таблиц — автоматическое обновление формул и сами формулы — до сих пор ключевое. А что насчет формул? Сейчас в Excel более 500 функций, постоянно добавляются новые. В VisiCalc в 1979 году их было чуть меньше — 21. Многие вы узнаете: @SUM, @NA, @ ERROR, @MAX, @MIN, @ AVERAGE, @ COUNT, @NPV, @ LOOKUP, @ABS, @INT, @EXP, @ LOG10, @LN, @PI, @SIN, @COS, @TAN, @ ASIN, @ ACOS, @ ATAN. И никаких тебе LAMBDA и даже СУММЕСЛИМН 🤠 Если захотите секунд на 5 заглянуть в прошлое и найти связь Excel с VisiCalc — она есть не только в названиях функций. Введите @ (а именно так начинались формулы в VisiCalc) и вводите название функции (например, СУММ) — и увидите, что собачка работает как знак "равно", вы вводите формулу.