Магия Excel

Быстрое скрытие столбцов Чтобы скрыть столбцы (выделенные столбцы или столбцы, относящиеся к выделенным ячейкам), нажмите Ctrl + 0 Сработает и для несмежных столбцов (если вы заранее выделите ячейки, зажав Ctrl). А как показать скрытые столбцы? Это сочетание Ctrl + Shift + 0. Но оно может не работать. И тогда вам придется чуть пошаманить в настройках Windows — см скриншот :) Для строк: то же самое, но с девяткой. Ctrl + 9 и Ctrl + Shift + 9.

❗️Коллеги, если не успеваете за развитием нейросетей, слышите о новых возможностях, но пока не применяете их в работе, обязательно изучите: ➡️14 инструментов по работе с нейросетями⬅️ Внутри готовые инструкции и видеоуроки от финдира с опытом 16+ лет, основателя самого большого сообщества финансистов в России – Софьи Бурцевой. Каждый Инструмент основан на личной практике: ✅ Как оплатить нейросети и западные сервисы из России ✅ Какую нейросеть выбрать под конкретную задачу финансиста ✅ Примеры качественного финансового анализа отчетности ✅ Как создавать финансовые модели с помощью нейросетей ✅ Как писать большие макросы и скрипты ✅ Как создать сильное резюме на hh помощью ИИ ✅ Как установить Claude в Excel и др. 7 инструментов. 🔥 Более 13 000 финансистов уже внедрили инструменты и отметили, что нейросети реально экономят время и позволяют выделиться среди конкурентов! Старая цена: 4 990₽ Сейчас: 0 ₽ Не отставайте! Забирайте 14 ИИ инструментов ➡️ https://t.me/ai_finansist/696

Горячие клавиши по понедельникам 🔥 Сегодня про скрытие / отображение разных элементов, а именно: Ctrl + F1 сворачивает (остаются только названия вкладок) и разворачивает ленту инструментов. Но для этой же цели проще использовать двойной клик по названию вкладки! Ctrl + 8 скрывает и отображает кнопки группировки (сама группировка остается в том же состоянии, строки/столбцы не отображаются — см скриншот на картинке) Ctrl + Shift + U разворачивает и сворачивает строку формул (если нужно больше места для просмотра и написания многоэтажной формулы или текста с переносами строк)

ПРОСМОТРX / XLOOKUP может возвращать ссылку, а не значение Такое поведение будет спровоцировано одним из операторов — двоеточием (диапазон), пробелом (пересечение диапазонов), запятой или точкой с запятой (объединение диапазонов) То есть ПРОСМОТРX(...) : ПРОСМОТРX(...) будет ссылкой на диапазон от ячейки, найденной первой функцией, до ячейки, найденной второй. Как в примере на скриншоте, где возвращается диапазон или сумма (если добавляется функция СУММ / SUM) "от и до".

Перемещаем столбец Если выделить столбец и потянуть его вправо или влево за границу, зажав левую кнопку мыши, мы вырежем и вставим данные — то есть исходный столбец останется пустым, а тот, куда мы перетащили, заполнится его данными. Поэтому Excel сначала предупредит вас в диалоговом окне о том, что данные будут заменены (если они есть) Ну а если надо переместить столбец, зажимаем клавишу Shift, тащим — и он просто перемещается. Уже без предупреждений :)

Рад сообщить, что моя новая 4-я книга "Аналитические отчёты в Microsoft Power BI" наконец-то вышла в продажу (в бумаге и PDF). По сути, это книга-тренинг. В основе лежит материал двух моих продвинутых курсов по аналитике - "Создание дашбордов в Power BI" и "Погружение в DAX", но широта и проработка материала тут значительно глубже и объем получился приличный - аж 420 страниц А4. Солидный такой кирпич 😁 Что внутри: ✔️ обзор экосистемы Power BI в текущих российских реалиях и всех этапов построения отчёта, включая правильное общение с заказчиком; ✔️ пошаговый процесс сборки "движка" любого отчёта - семантической модели данных, со всеми нюансами и "граблями"; ✔️ разбор создания и настройки внешнего вида всех основных типов визуализаций: карточек, таблиц, диаграмм, графиков, срезов, географических карт и т.д. ✔️ примерно половина(!) книги посвящена языку DAX - основному инструменту анализа в Power BI и Power Pivot. На примерах изучаем все основные функции агрегации, даты-времени, ранжирования, табличные функции, итераторы и т.д. ✔️ понятным языком объясняю про контексты, управление ими и преобразования одного контекста в другой; ✔️способы прогнозирования в Power BI, чтобы в ваших отчётах был не только факт, но и прогноз; ✔️ план-факт анализ: как правильно вводить в модель плановые данные и реализовать расчёт выполнения плана и отклонения от него ✔️ публикация готового отчёта и последующая настройка в облаке (права доступа и т.д.) В комплекте с книгой идут все файлы исходных данных и примеры отчетов в версиях "до" и "после", чтобы вы смогли либо сразу поковырять готовое решение, либо открыть исходник и проработать весь материал руками, повторяя все упражнения из книги. 📖 Страница книги у меня на сайте: https://www.planetaexcel.ru/books/bi-book.php Там же можно купить и тут же скачать электронную версию (цветной PDF с примерами) или заказать бумажную (отправим вам через СДЭК с оплатой при получении). Ещё есть на ОЗОН, но там подороже из-за комиссий https://ozon.ru/t/3Q1NiYI Буду рад любой обратной связи, критике, найденным ошибкам и т.д. И большое спасибо всем, кто терпеливо ждал выхода этой книги так долго 🙏

Генерируем QR-код формулой Общая схема такова: 1 находим сервис, который это делает 2 копируем ссылку на скачивание куар-кода 3 в этой ссылке заменяем ту часть, в которой будет ссылка. 4 текстовой формулой склеиваем фиксированные части ссылки на куар-код и ссылки на сайты/страницы из ячеек 5 все это отправляем в функцию IMAGE (в Excel она есть только в 365, на русском называется ИЗОБРАЖЕНИЕ) В случае с сервисом из примера (спасибо Николаю Павлову за рекомендацию — кстати, там можно и штрих-коды разные делать, включая ISBN и многие другие, и чего только не) формула будет такой: =IMAGE("https://barcode.tec-it.com/barcode.ashx?data="& ссылка &"&code=QRCode")

Горячие клавиши по понедельникам 🔥 Ctrl + Shift + стрелки — вечная классика, очень полезное сочетание для выделения ячеек "до упора" в любом направлении, до последней заполненной в строке / столбце. Но этим вариантом все не ограничивается! Без Ctrl будете менять выделение на одну строку / столбец. Если нажать F8, то менять размеры выделенного диапазона можно будет просто стрелками. Shift + Home расширит выделенный диапазон до первого столбца на листе, а с Ctrl — до первой ячейки листа.

Фильтр в сводной таблице по сумме Допустим, мы хотим посмотреть на тех клиентов, которые принесли нам миллион. В фильтре выбираем "Фильтр по значению" — "Первые 10..." — вводим сумму, которая нас интересует — меняем "элементов списка" на "Сумма" — нажимаем ОК. Получаем фильтрацию: только самые крупные клиенты, которые суммарно формируют нужную (введенную нами) сумму. Если бы выбрали "наименьших", а не "наибольших" в диалоговом окне фильтра, то получили бы самых маленьких по сумме выручки клиентов, которые вместе принесли нам миллион. Короткое видео с демонстрацией без звука.

Проверка данных: разрешаем вводить в ячейках только формулы Заходим на вкладку "Данные" — "Проверка данных" Выбираем тип данных "Другой", это возможность ввести формулу для проверки данных — и можно будет вводить только такие значения, при которых формула будет возвращать ИСТИНА / TRUE (это похоже на условное форматирование с формулами) Если нужно разрешить вводить только формулы, то формула будет такой: =ЕФОРМУЛА(первая ячейка диапазона) Почему мы вводим ссылку только на первую ячейку диапазона? Как и в условном форматировании, формула будет виртуально — не в ячейках — "протягиваться" (вычисляться) для каждой ячейки. Ссылки будут меняться, если они абсолютные — нам в данном случае это и нужно, так как мы проверяем, есть ли формула в каждой очередной ячейке. Если хотим, наоборот, запретить вводить формулы, то добавляем функцию НЕ / NOT: =НЕ(ЕФОРМУЛА(первая ячейка диапазона))

Навигация по листам в книге Excel В книге много листов? Щелкните правой кнопкой мыши на стрелки в левом нижнем углу. Откроется список всех листов. Там смотреть удобнее, чем просто по ярлыкам. А к следующему и предыдущему листу можно переходить с помощью сочетаний клавиш Ctrl + PgDn и Ctrl+PgUp.

Номер текущего листа, число листов в книге и число листов в открытых книгах Номер листа возвращает функция ЛИСТ / SHEET. Если оставить скобки пустыми Число листов — ЛИСТЫ / SHEETS. Если аргумента нет, это будет число листов в книге, а иначе — в ссылке (здесь смотрите про то, как проверять число листов в 3D-ссылке). Соответственно, если хотим номер текущего листа в формате "Лист N из M", где M — кол-во листов в книге: ="Лист " & ЛИСТ() &" Из " & ЛИСТЫ() А число всех листов в открытых книгах Excel — функция ИНФОРМ / INFO с аргументом "ЧИСЛОФАЙЛОВ" / "NUMFILE". ="Листов в открытых книгах: " & ИНФОРМ("ЧИСЛОФАЙЛОВ") Функции будут подсчитывать видимые, скрытые и очень скрытые (через редактор VBA) листы. у ИНФОРМ есть и другие аргументы — номер версии Excel (в формате 16.0), текущая папка, ячейка в левом верхнем углу окна, версия и тип операционной системы, тип пересчета (автоматически или вручную).

Горячие клавиши по понедельникам 🔥 Сегодня для диалоговых окон Excel! В них можно перемещаться по вкладкам с помощью сочетаний клавиш. Для этого даже есть два варианта. Напоминание: Ctrl + PgUp и PgDn — это еще и перемещение на следующий/предыдущий рабочий лист (если диалоговых окон не открыто).

Раскрашиваем N дней до и после сегодняшней даты Для этого дела создаем правило условного форматирования с формулой (Главная — Условное форматирование — Создать правило — Использовать формулу...) Формула в общем виде: =ABS(СЕГОДНЯ()-первая ячейка с датой)<N+1 Например, у нас первая дата в A2, а красить хотим 5 дней до и после: =ABS(СЕГОДНЯ()-A2)<6 На скриншоте дополнительно еще создано обычное (без формулы) правило — полужирное начертание для сегодняшней даты. Выделить сегодняшнюю дату можно так: Главная — Условное форматирование — Правила выделения ячеек — Дата — "сегодня"

Горячие клавиши по понедельникам 🔥 Конечно, ничего лучше двойного клика для "протягивания" формул или копирования значений вплоть до последней заполненной ячейки в столбце не придумано Но если вам очень нужно с клавиатуры — то Ctrl + D = заполнение вниз (Down). А еще мышкой не заполнишь вправо, а с клавиатуры можно. Это, соответственно, Ctrl + R (Right)

Объединение ячеек: почему это не очень хорошо и чем заменить с тем же визуальным эффектом Объединение ячеек в Excel приводит к тому, что значение хранится только в одной из объединенных ячеек. Если мы рассчитываем использовать эти ячейки в формулах, мы будем иметь дело с пустыми значениями. Поэтому, если мы предполагаем производить какие-то манипуляции с формулами, лучше избегать объединения. А сохранить его визуальный эффект (убрать повторы) можно с помощью условного форматирования — как, смотрим в видео (4 минуты со звуком)

Горячие клавиши по понедельникам🔥 На графическом слое — отдельно от ячеек — в Excel может быть много объектов: диаграммы, срезы и временные шкалы, фигуры, изображения. Вы хотите выровнять объекты — зажимайте ALT и двигайте их / меняйте размеры — это будет происходить не плавно, а по границам ячеек. Удобно, например, когда нужно несколько диаграмм выстроить ровно. Для создания копии объекта можно нажать Ctrl + D, а можно, удерживая Ctrl, потащить его левой кнопкой мыши, тогда вы сразу отправите созданную копию в нужное место. Кстати, Ctrl + левая кнопка мыши работает и для создания копий рабочих листов!

Хотите добавить маркеры (буллеты как в Word) к тексту в ячейках? Выделяем ячейки, заходим в окно форматирования (Ctrl + 1) Вводим маркер сочетанием Alt + 7 (семерка на цифровой клавиатуре) Добавляем @ — это символ, обозначающий текст (значение ячейки).

Горячие клавиши по понедельникам🔥 Сегодня про выделение текста при редактировании ячеек. с Shift'ом можно добавлять к выделению / убирать по символу (все как с выделением ячеек) Добавляем Ctrl — и выделяем слова. Ctrl + Delete удалит весь текст от курсора до конца строки (то есть если у вас в ячейке есть переносы строк, то текст в следующих строках останется). Заодно напоминаем: Alt + Enter используется для переноса строки в ячейках, можно применять и для читаемости формул. Ну а за пределами Excel есть еще одно хорошее сочетание — например, в Word / Google Документах — Ctrl + Backspace, это удаление слова до курсора.

Когда при вводе формулы вы выделяете диапазон, появляется вот такая подсказка с числом строк (R) и столбцов (C) в нем. Удобно, когда нужно понять, из скольки вариантов выбирать случайный, из какого по счету столбца тянуть данные его высочество ВПР'ом и т.д.