Магия Excel

М

Магия Excel@lemur_excel

Функция ЛИСТ / SHEETВозвращает она порядковый номер (индекс) листа.И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются. Функция без аргументов будет возвращать номер листа, на котором находится:=ЛИСТ()С аргументом (ссылкой) будет возвращать номер листа, на который ссылка:=ЛИСТ(Лист2!A1)Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео):=ЕСЛИ(ЛИСТ()>1; "Ошибка!Переместите лист в начало книги";"Оглавление")

М

Магия Excel@lemur_excel

У нас есть два списка, каждый из которых в отдельной ячейке. Нам нужно получить общие для обоих списков значения. С новыми функциями 365 это можно сделать формулой.Сначала разделяем каждый из списков на отдельные значения с помощью ТЕКСТРАЗД / TEXTSPLIT. Получаем два массива (для наглядности на скриншоте этот и промежуточные шаги показаны отдельно в ячейках, а вся формула видна в строке формул).Затем ищем весь первый список (каждое значение из него) во втором. Для этого подходит ПОИСКПОЗ / MATCH или ПОИСКПОЗX / XMATCH, отличаются они только тем, что у первой (старой) функции нужно задать третий аргумент = 0 для точного поиска.Она выдаст либо порядковые номера найденных значений, либо ошибки. Мы превратим с помощью ЕЧИСЛО / ISNUMBER числа в ИСТИНЫ, а ошибки в ЛОЖЬ.И по полученному массиву отфильтруем с помощью ФИЛЬТР / FILTER — получим только те значения из первого списка, для которых ИСТИНА, то есть которые были найдены ПОИСКПОЗОМ во втором списке.Останется склеить это с помощью ОБЪЕДИНИТЬ / TEXTJOIN.Функция LET позволяет задать переменные для списков и потом ссылаться на них, а не повторять вычисление с функцией ТЕКСТРАЗД. Также мы объявляем переменную для списка элементов, который получается в результате работы функции ФИЛЬТР.

М

Магия Excel@lemur_excel

Точку данных в диаграмме (например, в линейчатой) можно заменить изображениемДля этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).И Ctrl + V — вставляем изображение.После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше.И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить".

М

Магия Excel@lemur_excel

Горячие клавиши по понедельникам🔥Сегодня перемещение по листам и вкладкам в диалоговых окнах ExcelВсе просто:Ctrl + PgUp (Page Up) — впередCtrl + PgDn (Page Down) — назадПри этом в случае с окнами движение будет идти по кругу, то есть если вы на последней вкладке — нажав Ctrl + PgUp, окажетесь на первой.А в комменты выложим PDF с горячими клавишами за все недели!

М

Магия Excel@lemur_excel

Выводим все остальные книги автора в одной ячейке списком с буллетамиВот такая была задачка от коллег.Что делаем:1 добавляем маркер ко всем названиям книг из исходного диапазона "• " & 'Книги'!A2:A2 Находим имя автора конкретной книги, выбранной в выпадающем списке ПРОСМОТРX(A2; 'Книги'!A:A; 'Книги'!C:C)3 Находим все его книги функцией FILTER. Условие на столбец с именем автора:('Книги'!C2:C = ПРОСМОТРX(A2; 'Книги'!A:A; 'Книги'!C:C)4 Исключаем ту книгу, которая уже выбрана — у нас ведь "другие" книги автора:('Книги'!A2:A <> A2)Перемножаем эти два условия и отправляем в FILTER (это в Excel, а в Google Таблицах условия можно перечислять как отдельные аргументы — подробнее про FILTER и условия в большой статье по ссылке). На выходе будет список книг, но это будет массив. FILTER( "• " & 'Книги'!A2:A; ('Книги'!C2:C = ПРОСМОТРX(A2; 'Книги'!A:A; 'Книги'!C:C)) * ('Книги'!A2:A <> A2))А нам надо все в одной ячейке. Так что объединяем функцией JOIN — да еще добавляем перенос строки через функцию СИМВОЛ / CHAR — его код 10:=JOIN( СИМВОЛ(10); FILTER(... ))Google Таблица с примеромВ новом Excel будет аналогично, только вместо JOIN — функция ОБЪЕДИНИТЬ / TEXTJOIN.

М

Магия Excel@lemur_excel

Голосовой ввод в Windows 11Нажимаем Win + H и диктуем текст. На русском можно! Это не про Excel, а про Windows в целом, так что можно использовать по-разному: вводить текст в ячейки или наговаривать задачки для ИИ, чтобы писал вам формулы.Хотя я все-таки за старый добрый десятипальцевый метод печати :) Но кому-то наверняка подойдет и такой вариант. Если включить опцию Automatic punctuation ("Автоматическая пунктуация"), можно будет проговаривать и знаки препинания.

М

Магия Excel@lemur_excel

Горячие клавиши по понедельникам🔥Сегодня одно сочетание — вставка строк и столбцов. Это Ctrl + Shift + плюсЕсли вы выделили строку или несколько — вставится соответствующее число строк.Аналогично со столбцами, если выделены они.А если выделена ячейка или диапазон, то сначала вам будет предложено уточнить в диалоговом окне — что будем вставлять, строки/столбцы/ячейки.Вставили вы, допустим, 10 строк, а вам надо еще столько же?Вспоминаем про повтор последнего действия — F4 или Ctrl + Y.Ну и напоминание: удалять строки и столбцы еще проще — Ctrl + минус.

М

Магия Excel@lemur_excel

Несколько ИИ-лайфхаков и заметокСмотрел тут зарубежный курс по ИИ в Excel — в целом ничего хитрого, но несколько вещей зафиксировал и делюсь:— В конце запроса предложить моедли задать вопросы — уже писал про это. "Могут ли быть проблемы с этой задачей", "Есть ли у тебя вопросы?"— у Excel долгая история, и модели могут учиться на старых статьях, не зная про новые решения— Галлюцинации! Все равно вам нужно понимать основы.— ИИ может пригодиться для расстановки отступов в длинных формулах (я вот иногда использую для своих уже написанных многоэтажных так)— Может прокомментировать и разобрать чужую сложную формулу— Можно записать макрос рекордером, а потом попросить сделать динамическим— Функция COPILOT хороша для задач с текстом, категоризировать (по эмоциям, например). Но не для вычислений. Есть лимит на вызовы, поэтому лучше сразу кормить ее массивами, а не прописывать много отдельных формул ! При пересчете весь результат, возвращаемый COPILOT, может измениться, даже если поменялась только одна ячейка во влияющем диапазоне.

М

Магия Excel@lemur_excel

Так-так-так... Новые функции для импорта текстовых файлов (txt, cst, tsv)IMPORTTEXT и IMPORTCSVПока только выкатывают на инсайдеров в 365.Первая имеет побольше опций, вторая попроще и только для CSV.Что в аргументах (на примере IMPORTTEXT):1 указываем путь к файлу. Обращаю внимание, что это может быть и локальный путь, и в сети. На скрине пример импорта случайной CSV с сайта Росстата, 180 тыс. строк нормально импортировалось, но не сразу — пришлось вручную указать разделитель (точка с запятой, а по умолчанию tab).2 собственно разделитель.3-4 можно пропускать и оставлять заданное число строк, это хорошо! Отрицательные числа = пропускаем и оставляем с конца.5 кодировка, по умолчанию UTF-8, у меня не сработала — поменял на 1251 — заработало6 локаль (региональные настройки)Здорово, конечно, что будут такие функции, но Power Query, кажется, таки удобнее, а главное, куда доступнее :)В Google Таблицах, кстати, еще со времен, когда в них вели учет жилых пещер и числа убитых мамонтов, была функция IMPORTDATA, которая возвращает данные из CSV или TSV по ссылке.

М

Магия Excel@lemur_excel

Задачка в Google Таблицах: собираем данные с разных листов в другой таблице, чиним и добавляем к данным дату из названия листаВот с таким помогал коллегам недавно и решил поделиться примером на учебных данных, но с рабочей формулойСмотрим в открытом всем и доступном в России видео на Sponsr по ссылке(там же ссылки на гуглотаблицы с примером)В видео:— генерируем с помощью SEQUENCE названия листов— двумя способами вытаскиваем из текста короткую дату и делаем ее настоящей— создаем пользовательскую функцию внутри формулы для импорта данных с добавлением столбца с этой датой— применяем эту функцию для импорта пачкой всех листов одной формулой.