Магия Excel

Новинка: флажки в Excel Лучше поздно, чем никогда 😺 В Excel — пока только ранним пташкам, получающим обновления первыми — доступны флажки в ячейках. Как в Google Таблицах, где они появились уже давно. Флажки — переключатели, меняющие значения с ИСТИНА / TRUE на ЛОЖЬ / FALSE и наоборот. Их можно использовать для чек-листов, списков, ссылаться на них в формулах (включили флажок — начисляем скидку в этой строке с помощью функции IF / ЕСЛИ, например) и в условном форматировании (поставили флажок — зачеркнули или покрасили цветом строку) В коротком видео смотрим: — на новые флажки и как их использовать в формулах и условном форматировании — на старые флажки Excel — элементы управления формы (они размещаются не в ячейках, а на отдельном слое, и каждый нужно вручную связывать с ячейкой) — на флажки в Google Таблицах

Тем временем большая часть тиража книги "Магия таблиц" уже распродана за 4 месяца — более 2100 экземпляров из 2500, в издательстве осталось совсем чуть-чуть, и в магазинах тоже — в Лабиринте книга и вовсе кончилась, например. Так что если планируете сделать полезные и табличные подарки коллегам/друзьям на Новый год или по другим поводам, поторопитесь! Книгу можно заказать тут: На сайте издательства (там же электрическая книга) Book24 Лабиринт (потрачено) Озон Wildberries Читай-город Буквоед За это время прибавилось отзывов! На Озоне 50 оценок с рейтингом 4.92, на WIldberries 22 оценки — 4.8. Вот наш с Лемуром любимый отзыв из свежих 😺 Вся информация очень поверхностная, без деталей. Чисто о возможностях , примеров формул практически нет Ну и несколько других (просто копируем из магазинов как есть): — Настоящий справочник, очень пригодился в работе — Отличная книга! — Отличная полезная книга — Книга бесценна по своему содержанию. — уровень знания excel неплохой, знания математики забыты. люблю, когда все объясняют. для меня книга очень полезная. автору, продавцу и озону спасибо ❤️ — содержание: описан полезный функционал — Купил книгу так как до этого проходил два курса от автора. Очень доволен, пользуюсь в том числе как справочником-шпаргалкой, если что-то подзабылось. Настоятельно рекомендую. — Книга-мечта! Отлично дополняет курс "Магия Excel"! Полностью книгу не читала, но обращаюсь, когда нужно быстро найти функцию или освежить в памяти комбинацию клавиш для определенных действий!

А как быть в старых версиях Excel (до 2019 включительно), где функции ПОСЛЕД / SEQUENCE нет? Там можно воспользоваться прогрессией (Series). Ищите инструмент на вкладке "Главная" в коллекции команд "Заполнить" (стрелка вниз). Увы, это будет статичная история, а не динамическая, как в случае с формулами... но тоже неплохо!

Выводим формулой список всех рабочих дней — от заданной до сегодняшней (так в примере; но можно и наоборот — как вам нужно) Для этого: 1 вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS) 2 Засунем это число в функцию ПОСЛЕД / SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде 3 отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности. Формула такая: =РАБДЕНЬ(начальная дата-1;ПОСЛЕД(ЧИСТРАБДНИ(начальная дата ;конечная дата))) На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день (кроме выходных 😉) P.S. G.S. В Google Sheets тоже будет работать, только не забудьте нажать Ctrl+Shift+Enter, чтобы добавилась функция ArrayFormula.

Как разделить текст по нескольким разделителям? Например, по косой черте и дефису, как в примере. В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT. А чтобы она работала с несколькими разделителями, отправим их в массив: {"первый разделитель"; "второй"; ... } Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем: "/-" А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи: =SPLIT(A2; "/-") или =SPLIT(A2; "/-"; 1)

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате Эта чудо-функция возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент). Для чего нужна? Допустим, вы хотите "склеить" в одну текстовую строку текст и число. Чтобы получить в таблице надпись вида "По состоянию на: 30.06.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты. Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 44742") — потому что вот так даты хранятся в Excel и Таблицах. И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в окне "Формат ячеек" (Ctrl + 1). Итак, для даты в нашем примере нужна будет такая формула: ="По состоянию на: " & ТЕКСТ (дата; "ДД.ММ.ГГ") Подробнее про пользовательские числовые форматы можно посмотреть в видео — оно на основе Google Таблиц, но все работает практически идентично.

Хотите видеть какую-то книгу Excel всегда наверху в списке последних файлов? На стартовом экране (Backstage) наводите курсор на нужный файл — справа появится кнопка (которая выглядит как... кнопка) "Закрепить" (Pin). Нажимайте и книга будет всегда наверху. Точно так же можно открепить обратно. Хотите, чтобы стартовый экран не показывался при включении Excel (или другого приложения Office)? Заходите в Параметры — Общие — отключайте "Показывать начальный экран при запуске этого приложения"

Нарастающий итог: закрепляем только первую ячейку в диапазоне Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец. 1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить") 2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться. =СУММ($B$2:B2) 3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

Избранное: актуальная и обновленная подборка самых сочных материалов нашего канала Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке Макрос для сравнения двух файлов (книг Excel) Удаляем строки с пустыми ячейками в одном из столбцов Макрос: создаем оглавление в книге Анализируем сезонность в сводной таблице Поиск по двум критериям План-факт через комбинированную диаграмму Видеоурок: "старые" и новые формулы массивов Функция СУММЕСЛИМН / SUMIFS: сумма по условиям Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический) "Протягивание формул" (двойной щелчок и сочетания клавиш) Объединяем умные таблицы в одну: формулы и Power Query Сравнение списков (Видео)

Удаляем пустые строки Выделяем диапазон, в котором нужно удалить пустые ячейки (в видео - все данные на листе с помощью Ctrl+Shift+End). Для этого нужен инструмент "Найти и выделить" (на ленте на вкладке "Главная", Home — Go To) — выбираем там "Выделить группу ячеек" и в появившемся диалоговом окне — "Пустые ячейки" (Blanks). Еще можно нажать F5 или Ctrl + G и в появившемся окне нажать "Выделить". После этого остается нажать Ctrl + - (Ctrl и минус) — это удаление ячеек/строк/столбцов. И выбрать "строку". P.S. Если у вас пустые ячейки только в одном столбце, и нужно удалить строки с такими ячейками — выделите один столбец, а не всю таблицу, а далее алгоритм такой же.

Когда вы отправляете поле в область значений сводной таблицы (туда, где ведется собственно агрегирование — на пересечении строк и столбцов), применяется один из двух вариантов: — если в исходных данных в этом поле (столбце) только числа — суммирование — если есть хотя бы одно текстовое значение — количество (подсчет) Изменить вычисление можно разными способами. 1 Самый простой — правая кнопка по любому значению — Итоги по — выбрать вариант подсчета (тут не все варианты, но основные есть — среднее, максимум и минимум, количество и сумма). 2 Двойной щелчок по заголовку поля ("Сумма по полю...") 3 Щелчок по полю в редакторе сводной таблицы — "Параметры полей значений" Видите неактивную опцию "Число разных элементов"? Это подсчет уникальных значений. Он будет доступен, если сводная будет построена на основе модели данных. Так можно сделать, даже если исходные данные — всего одна таблица. При построении сводной включите флажок "Добавить эти данные в модель данных"

Вот такая табличка: сравнение старого и нового Excel и Google Таблиц по доступности новых функций. Это один из многих слайдов практикума "Новые функции", который пройдет 7, 14 и 20 ноября. На самих занятиях слайды смотреть не будем — это дополнительный материал, а во время вебинаров будет много практики и ответы на вопросы. Практиковаться можно будет и в Google Таблицах (если нет подписки 365), и в Excel! Присоединяйтесь! https://www.mann-ivanov-ferber.ru/courses/practicum-excel/ Лемур принес вам скидочку 35% — по промокоду LEMUREC, действующему до 7 ноября. А тут, напоминаем, подробное сравнение Excel и Google Таблиц по всем нюансам.