Магия Excel

Магия Excel pinned a photo

Горячие клавиши по понедельникам🔥 Сегодня про умные таблицы! Создать их можно разными способами, включая два горячих: — Ctrl + T — Ctrl + L (раньше они назывались списками) — Вставка — Таблица — Главная — Форматировать как таблицу Вставить строку итогов можно так: — Ctrl + Shift + T А вставить новую строку в таблицу можно как с ленты, так и из контекстного меню (правая кнопка мыши), но самые быстрые способы: — просто ввести данные в одну из ячеек под таблицей (но это сработает только без строки итогов, а если таковая есть, то просто дописать данные не получится) — нажать Tab в правой нижней ячейке тела таблицы (последней ячейке с данными, а не в строке итогов)

Функция LAMBDA в Excel и Google Таблицах: подробное руководство + видео + файлы с примерами Содержание статьи: — Где LAMBDA доступна — Видео: Основы, LAMBDA и MAP, LAMBDA в Google Таблицах, сравнение формул массива и LAMBDA + MAP — LAMBDA: первый подход. Создаем свою функцию — LAMBDA и необязательный аргумент пользовательской функции с ISOMITTED / ПРОПУЩЕНО — LAMBDA и MAP — MAP внутри нашей пользовательской функции — LAMBDA внутри функции GROUPBY. Краткая форма записи eta lambda — Строим "сводную таблицу" на формулах в Google Таблицах — Обработка столбцов и строк — функции BYROW и BYCOL — Функции SCAN и REDUCE: работаем с промежуточными итогами и каждым значением — Переменная как ссылка на ячейку — Функции для формирования массивов — EXPAND и MAKEARRAY — Рекурсия в LAMBDA. Лимиты вызова — Заключение: не формулами едиными — Ссылки на дополнительные примеры — Ссылки на файлы с примерами https://shagabutdinov.ru/tpost/lambda Спасибо Михаилу Музыкину за помощь и обсуждение предварительного варианта статьи! Читайте, изучайте, задавайте вопросы в комментариях и делитесь статьей с коллегами и товарищами, работающими в Excel 2024 / 365 / Google Spreadsheets.

Хороший новогодний подарок для ваших знакомых экселье Напоминаем — в продаже третье издание "Магии таблиц". Это 500 с лишним страниц, только свежак и все актуальное, файлы со всеми примерами, от интерфейса до введения в PQ и PP, все новые функции, включая GROUPBY и PIVOTBY, твердый переплет. Сейчас бумажная версия в издательстве стоит всего 850 рублей. Сравните с книгами про Excel по 30 долларов (+доставка) с ужасным качеством бумаги и скриншотов и мягкой обложкой (но великолепным содержанием, тут без вопросов) и хватайте подарок для всех, кто живет и работает в таблицах! Вот где можно купить: Озон, ВБ, Литрес (электро), МИФ (бумага и электро) Отзыв Николая Павлова, автора проекта "Планета Excel" и замечательных книг: Уникальность этой книги в том, что впервые под одной обложкой собрана коллекция наиболее эффективных и полезных приемов и функций сразу из Microsoft Excel и Google Sheets – самых мощных, на сегодняшний день, инструментов для работы с электронными таблицами. Это сравнение двух программ, показ их различий и общих возможностей позволит пользователям гибко переключаться на нужный инструмент при необходимости. А это дорогого стоит.

Нарастающий итог: на старых и новых формулах Старые формулы: Вводим формулу, в которой суммируем диапазон, начинающийся и заканчивающийся в одной и той же ячейке — первой ячейке с данными. Но закрепляем первую из них (начало): =СУММ($C$2:C2) И при протягивании диапазон будет меняться на $C$2:C3, $C$2:C4 и так далее — то есть будем суммировать вплоть до той строки, в которой формула. Новые формулы: вводим одну формулу, которая работает для всего столбца =MAP(C2:.C1048576; LAMBDA(x; СУММ(C2:x))) Что тут происходит? Обрабатываем все ячейки в столбце C, начиная со второй строки. Но исключаем пустые ячейки — за это отвечает точка после двоеточия в ссылке C2:.C1048576. И для каждого значения x в этом массиве вычисляем сумму от C2 до этого значения. Спасибо Николаю за комментарий — на больших объемах данных гораздо быстрее будет работать вариант со SCAN: =SCAN(0; C2:.C1048576; LAMBDA(acc;val; acc + val)) Мой пример — для демонстрации того, что переменная в LAMBDA может быть ссылкой. Это может пригодиться в том числе и в SCAN — например, для нарастающих итогов с условием. Пример можно посмотреть в этой статье: https://shagabutdinov.ru/scanexcel

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

Функция ПОСЛЕД / SEQUENCE — видео Продолжительность: 15:40 — Синтаксис функции — Нумерация списка + новая функция TRIMRANGE и новый тип ссылок A.:.A — ПОСЛЕД + ВЫБОРСТРОК для извлечения части строк из таблицы — Разделение текста на символы — Последовательность дат с выбором “Неделя / день” Видео (это и другие) на сайте — доступно в России Ютуб Ссылка на файл с примерами

Отличия по строкам Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)? 1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз) 2 Ctrl + G —> Выделить (Special) 3 Отличия по строкам (Row differences) 4 Красим выделенные ячейки нужным цветом. Готово! Смотрим на GIF (без звука)

Горячие клавиши по понедельникам🔥 Сегодня два сочетания для выделения столбца/строки. Обратите внимание, что они работают и в официальных ("умных") таблицах, и просто на листе. В случае Таблиц с большой буквы Т сначала выделяются данные в пределах таблицы (если столбец — то сначала тело столбца, потом с заголовками и итогами и только потом уже весь столбец листа) И эти сочетания работают при вводе формул!

Спасибо Ренату 🙂 Друзья, мой соавтор по каналу Ренат Шагабутдинов решил сосредоточиться на своем проекте @lemur_excel, поэтому этот канал дальше буду вести только я, Евгений Намоконов. Мы с Ренатом вместе написали книгу "Google Таблицы. Это просто. Функции и приемы" Также мы вместе делали курс на Скиллбоксе и много лет вели этот канал бок о бок. Ренат больше писал про формулы, а я про скрипты. У Рената в его канале @lemur_excel есть рубрики, например "хоткеи по понедельникам" и разные полезности для тех, кто работает с Excel. Пожелаем Ренату удачи 👏

Функция LAMBDA в Google Таблицах — видео (для Excel — нового — тоже актуально, ибо принципы работы максимально похожи) — Зачем нужна LAMBDA — MAP и столбец / двумерный массив / несколько массивов — BYROW: обработка строк — LAMBDA vs старые формулы массива — MAP + INDIRECT: обработка данных с нескольких листов по списку Ссылка на таблицу со всеми примерами Это мой кусок сегодняшнего вебинара на канале "Google Таблицы". Там были технические сложности, так что перезаписал с нуля с хорошим звуком. Даже больше, чем было) Наслаждайтесь: Kinescope (доступно в России) Ютуб

ФИЛЬТР.XML - xpath #ExcelFunctions Всем привет! Все думаю знают, что я упоротый эксельщик, поэтому периодически буду складывать сюда полезности, про которые "в книжках не пишут". Во вложении мой краткий мануал по использованию аргумента xpath в функции ФИЛЬТР.XML - это конечно не замена регулярок, но позволяет многое. Пы.Сы. Не пугайтесь - M остается основной тематикой канала. Вечером обязательно будет привычный пост - сегодня разберем Folder.Contents. Надеюсь, будет полезно. Всех благ! @buchlotnik