Магический Excel-чат

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

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

Рад сообщить, что моя новая 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/3

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

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

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

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

Объединяем умные таблицы в одну: формулы и Power Query В видео разбираем такую задачу: собрать данные из нескольких умных таблиц. Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР / FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки. Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Горячие клавиши по понедельникам 🔥 Сегодня — "Переход" Это Ctrl + G или F5. И далее: Можно ввести адрес ячейки и нажать Enter — попадете в эту ячейку. Так можно добраться даже до скрытой ячейки и ее редактировать в строке формул или просто посмотреть там на содержимое. А можно нажать "Выделить" и далее выделить все формулы / отличия / пустые ячейки и так далее.

Как избежать вставки ссылок в диалоговых окнах Вот редактируете вы какую-то формулу или диапазон в окне условного форматирования или в диспетчере имен Excel. И нажимаете стрелку влево или вправо на клавиатуре, чтобы... переместить курсор. И в этот момент Excel вставляет ссылки на ячейки. А-а-а-а-а! Как от этой гадости избавиться? Нажать F2. И тогда стрелки будут перемещать курсор. При вводе формул в ячейках это тоже работает. Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там "Правка" (Edit), то можно смело нажимать на стрелки :)

Видео: разбор формулы, которая вычисляет серию игр без ничьих по каждой команде. Новые функции Excel во всей  красе Маэстро Михаил Музыкин поделился со мной формулой, которую он выдал в качестве решения на одном из форумов по Excel Задача в том, чтобы вычислить, сколько матчей к данному моменту у каждой команды было без ничейных результатов подряд. Тут во всей красе новые и сверхновые функции, а я их обожаю, так что решил записать видео с разбором формулы по шагам, надеюсь, это поможет новичкам понять силу функций и лучше с ними познакомиться! Видео на Kinescope Ссылка на тему на форуме Канал Михаила

Горячие клавиши по понедельникам 🔥 Ctrl с минусом: удаление. Удаление чего? Если у вас выделены столбцы или строки полностью, они и будут удаляться без лишних вопросов! Если ячейка или диапазон — то Excel уточнит, хотите ли вы удалить ячейки, строки или столбцы. В умной таблице будет удаляться строка, если выделена одна ячейка, и столбец, если несколько в одном столбце. В сводной таблице выделенные элементы (в области строк или столбцов) будут скрыты (отфильтрованы). Ну а если мы добавим Shift, то будем удалять границы ячеек.

За последние пару-тройку лет в Excel 365 появилось много мелких и не очень нововведений (не считая новых функций листа). Большинство из них не тянут на отдельное видео, так что я решил рассказать про них оптом 😁 ✔️ Фокусировка на ячейке ✔️ Копирование итогов из строки состояния ✔️ Улучшенный выпадающий список ✔️ Отображение сразу нескольких скрытых листов ✔️ Панель навигации ✔️ Улучшенный тёмный режим ✔️ Проверка производительности ✔️ Устаревшие значения ✔️ Контрастные цвета ✔️ Поддержка эмодзи и юникода ✔️ Распознавание текста с изображений ✔️ Вставка картинок в ячейку ✔️ Инсайты от ИИ Понятно, что в текущих российских реалиях наличие установленной на компьютере последней версии Microsoft Office 365 по подписке - это роскошь и есть далеко не у всех. Но то, что появляется сначала эксклюзивом только в подписке Excel 365, спустя какое-то время Microsoft обычно добавляет и в регулярные версии Excel, так что надежда есть 😉 Например, некоторые из описанных фич уже есть в Excel 2024. ▶️ Смотреть видео и читать статью у меня на сайте (без VPN) https://www.planetaexcel.ru/techniques/11/72537/ ▶️ Смотреть видео на YouTube https://youtu.be/uZW4ICrRnFY