Магия Excel

А вот еще одна новинка, но несопоставимого масштаба... код Python будет работать прямо в Excel! Пока все только начинается — с бета-каналом обновлений группа Python не появилась сама, ее можно добавить как на скриншоте — через параметры ленты инструментов. Но все равно ничего еще не работает. Работать будет прямо в ячейках — можно будет начинать ввод с =PY в формуле или через ленту инструментов. А вычисления будут производиться в облаке. То есть будет нужен доступ к сети. Для ссылок на объекты (ячейки и диапазоны, данные в "умных" таблицах) Excel будет использоваться функция Python xl(). Ссылки: Официальная новость Общие сведения о Python в Excel Начало работы с Python в Excel Python in Excel (на странице дистрибутива Anaconda)

Заходит как-то в бар (Formula Bar) курьер (а точнее, какой-то другой моноширинный шрифт)... Небольшое, но приятное обновление в Excel: теперь в строке формул моноширинный шрифт! Такие шрифты в основном используются в программировании; название намекает, что все символы одной ширины, что упрощает выравнивание, чтение кода. А чем длинные формулы в Excel не код? Обновление появилось у тех, кто подписан на бета-канал обновлений и потихоньку будет выкатываться на всех пользователей, получающих обновления. Заодно Лемур хочет вам напомнить, что с помощью Alt+Enter можно вставлять в формуле перенос строки и таким образом делать сложные формулы читабельнее. Официальная новость тут.

Сравнение списков Небольшое видео (со звуком) для новичков. Сравниваем списки двумя способами: — через условное форматирование (визуально) — формулами (с помощью функции СЧЁТЕСЛИ / COUNTIF) Также обсуждаем, что делать, если вдруг в рамках одного списка будут дубликаты.

Как вычислить стаж (возраст, разницу в годах), если в данных то полная дата, то один год? Вот такой вопрос был от одного из участников корпоративного обучения. Задачка решается в общем виде так: Проверяем функцией ЕСЛИ / IF, какие данные у нас — год или дата, и в зависимости от этого просто вычитаем из сегодняшнего года тот, что в ячейке, либо считаем разницу между датой в ячейке и сегодняшней датой. Как выяснить, что в ячейке? Можно по числу цифр — функция ДЛСТР / LEN — если их 5, то дата, если 4, то год. Но тогда получится, что 9999 будет считаться годом. А мы помним, что любая дата в Excel — это число (число дней с 1 января 1900). Так что 9999 — это 17 мая 1927, и если у вас в данных могут быть такие даты рождения, то лучше второй способ). Можно просто по числу — если оно больше 2100, то это дата. Тут порог надежнее: 2100 — это 30 сентября 1905, вряд ли у вас будут такие даты. Итак, задачу решили такой формулой: =ЕСЛИ(ячейка>2100;РАЗНДАТ(ячейка;СЕГОДНЯ();"y");ГОД(СЕГОДНЯ())-ячейка) Проверяем значение в ячейке и если оно больше 2100, то с помощью РАЗНДАТ / DATEDIF находим разницу в годах ( "y" —третий аргумент — полные годы) между датой и сегодняшним числом. Если меньше — значит, это год. Вычитаем из текущего года — который вычисляем с помощью функции ГОД / YEAR от текущей даты — год в ячейке.

В новом Excel (365 или 2021) можно делать то, что делают сводные — формулами 😺 Если хотим считать сумму продаж по каждому клиенту (или другому условию — городу, товару), можно вывести всех клиентов функцией УНИК / UNIQUE, и посчитать сумму продаж по каждому через СУММЕСЛИМН / SUMIFS. При этом мы ссылаемся на список клиентов, выводимый функцией УНИК, через ссылку с решеткой (писали о таком типе ссылок выше), то есть при появлении новых клиентов в исходных данных все обновится автоматически. Отличие от сводных в том, что обновляться тут все будет автоматически, так как это формулы! А в сводных данные загружаются в кэш, нет прямой связи с ячейками, их надо обновлять. И работать это все будет при добавлении новых строк и/или клиентов. Подробнее в коротком видео со звуком. P.S. В старых версиях тоже можно решить эту задачу, но без обновления: получить список уникальных клиентов через инструмент "Удалить дубликаты", протянуть по каждому отдельную функцию СУММЕСЛИМН. Так что там лучше сводными :)

Не устаем рассказывать про одно из наших любимых сочетаний Ctrl+Backspace — это возвращение к активной ячейке. Удобно при работе с формулами, расширенным фильтром, другими окнами. Например. Многие из вас знают одно из любимых Лемуром сочетаний клавиш Ctrl + Shift + стрелки (⌘ + ⇧ + стрелки). Оно позволяет (если ловкости лап хватит все это нажать одновременно) выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео. Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится чуть менее часто используемое (ну, как нам кажется) сочетание — Ctrl + Backspace (⌃ + Delete).

Друзья, если пользуетесь "Дзеном", теперь можно подписаться на нас и там — все новые публикации будут выходить и там тоже! https://dzen.ru/excel_magic

Форматирование устаревших значений А вот совсем новая вещь, анонсированная буквально пару недель назад. Теперь можно автоматически выделять устаревшие значения — речь о формулах, которые выдают неактуальный результат, так как влияющие на них ячейки (хотя бы одна) поменялись, а автоматический пересчет формул при этом выключен (если бы он был включен, то формулы обновлялись сразу при изменениях влияющих ячеек, конечно). Для этого достаточно включить флажок "Формат устаревших значений" (Format Stale Values) на вкладке "Формулы". Работать это будет, конечно, при ручном пересчете формул.

Показать сразу несколько листов Относительно новая штука (появилась в конце 2020 года для подписчиков Microsoft 365) — можно показать сразу несколько скрытых листов. Когда вы щелкаете правой кнопкой по любому ярлыку видимого листа и выбираете "Показать..." (Unhide...) — появляется этот список. Теперь в нем можно выбрать несколько отдельных листов (с зажатой Ctrl) либо список идущих подряд листов (для этого зажмите Shift и щелкните на лист, до которого включительно вам нужно выделить листы в списке).

Друзья, если вы работаете не только в Excel, но и в Google Таблицах, то подписывайтесь на одноименный, дружественный и вообще лучший канал по теме. К которому в комплекте идет чат, населенный настоящими профессионалами, готовыми помочь (при понятной формулировке задачи и наличии примера 😺) https://t.me/google_sheets @google_spreadsheets_chat Вот на днях выложил там большую статью про функцию IMPORTRANGE, которая позволяет загружать данные из одной таблицы в другую: Главная статья импорта

Связанные выпадающие списки Если вы хотите, чтобы список возможных значений в одном списке определялся на основе другого — можно использовать функцию ФИЛЬТР / FILTER для формирования списка — например, товаров только одной, выбранной категории. И далее можно на эту формулу ссылаться из проверки данных (Data Validation) с помощью нового типа ссылок с решетками, о которых мы рассказывали здесь: https://t.me/lemur_excel/165 Подробно — в коротком видео со звуком. Отдельным сообщением прикрепляем файл с формулой выше. P.S. Радость эта будет доступна, увы, только в новом Excel.