Горячие клавиши по понедельникам 🔥

На этой неделе у нас сочетания для создания, закрытия, открытия и сохранения книг.

На что обращаем внимание:
— Если книга новая, то "Сохранить" и "Сохранить как" будут одним и тем же — сначала нужно сохранить книгу первый раз под каким-то именем, чтобы потом просто сохранять

— Для сохранения есть и еще одно сочетание — Alt + цифра (а какая — зависит от того, какой по счету у вас дискетка на панели быстрого доступа). Конечно, и любую другую команду, в том числе "Открыть" или "Создать" можно добавить на панель быстрого доступа, чтобы получить возможность вызывать команду по сочетанию Alt + цифра.

Как изменить шаблон новой книги (которая создается по Ctrl + N)?
Читайте здесь.

Версия совместимости функций

Во все времена разработчики Excel дорабатывали функции, если что-то работало не так — но обычно им приходилось создавать новую функцию, а не менять старую.
Ведь если изменить поведение старой функции, это затронет миллионы рабочих книг, в которых она уже работает в формулах.

Вот и появлялась ОКРВВЕРХ.МАТ (CEILING.MATH) в дополнение к ОКРВВЕРХ (CEILING).

А что теперь? Теперь есть два режима — поведение по умолчанию (вариант 1) и последняя версия функций.

Получается, что теперь разработчики смогут менять поведение существующих функций, исправляя какие-то проблемы. В старых рабочих книгах можно будет оставлять старое поведение функций. А в новых использовать новые функции.

Смотрим, как это уже происходит воочию на примере ДЛСТР / LEN, которая определяет число символов в текстовой строке.

В старом исполнении она считает один эмодзи за два символа (так называемая суррогатная пара в Unicode). Это технически верно, но мы воспринимаем эмодзи за один символ.
И теперь функция считает именно так — смотрим в прикрепленном видео на несколько секунд без звука.

Где находится новый переключатель?
Формулы — Параметры вычислений — Версия совместимости — Версия 1 / Версия 2
Formulas — Calculation Options — Compatibility Versions — Version 1 / Version 2

Автозавершение функции в Power Query

Есть одна раздражающая вещь в PQ — это автозавершение функций

Вот написали вы Text..
Нашли в списке нужную вам функцию Text.Proper, допустим
Нажали Tab...

И получили

TextText.Proper

А-А-А-А-А 😵
И что еще печальнее, эта гадость все портит, когда у вас вложенные функции. Старая функция стирается.

Как быть?
Начинайте вводить с точки — как в прикрепленном видео (без звука).

Будет работать и в настраиваемом столбце, и в строке формул, и в расширенном редакторе.

Кавычки в формулах

Вам нужно склеить текстовые строки и добавить к ним кавычки.

Просто кавычки обозначают границы текста в рамках формулы. То есть следующее выражение — это просто пустая текстовая строка:

=""    

А следующая формула вернет текст "Лемур" без кавычек:

="Лемур"    

А если вам нужны кавычки — есть два варианта — ввести пару внутри внешних кавычек:

="""Лемур"""    

Такая формула вернет слово "Лемур" в кавычках.

Или использовать функцию СИМВОЛ / CHAR (в VBA — Chr) и код кавычек 34. Следующая формула добавит кавычки вокруг текста из ячейки A2:

=СИМВОЛ(34)&A2&СИМВОЛ(34)

Выделяем только видимые ячейки
Длительность: 5 мин

Выделяем только видимые ячейки (разными способами), чтобы потом:
— скопировать только их (например, только итоги) и вставить на другой лист
— отформатировать их (применить заливку только к итогам, а не ко всем строкам)
— вставить только в эти ячейки формулы — например, чтобы изменить шаблонные заголовки промежуточных итогов.

Это же видео на Kinescope (доступно в России без ограничений)

И оно же на Youtube

Оно же и десятки других бесплатных видео на сайте

А для подписчиков на Sponsr есть расширенная версия этого урока, как и десятки других подробных видео (и каждую неделю новое!)

Хотите, чтобы нули было сразу видно (чтобы они отличались от букв О)?

Используйте шрифт Consolas — на скриншоте он справа, а слева стандартный на данный момент шрифт Aptos.

10 лайфхаков Excel: вы точно найдете тут что-то новое для себя!
Длительность: 12 мин

— Ссылка на несколько листов
— Мгновенное заполнение
— Быстрое удаление столбцов
— Выделение пустых ячеек или отличий по строкам
— Быстрая специальная вставка
— И другое!

Смотреть видео на Youtube
Или на сайте (доступно в России) — там есть и десятки других видеоуроков

Горячие клавиши по понедельникам 🔥

Сегодня у нас все, что связано с именами.

Ctrl + F3 — это диспетчер имен. Можно посмотреть, какие именованные диапазоны у вас в книге есть. Удалять их, менять, создавать новые.

Когда имена уже имеются, их можно вставлять в формулу, нажимая F3 — откроется окно "Вставка имени", где будет список имен.

Ctrl + Shift + F3 — окно "Создать из выделенного". Это когда у вас есть заголовки в столбцах / строках и вы хотите данные сделать именованными диапазонами, используя эти заголовки в качестве имен. Удобно, чтобы массово присвоить имена, а не делать по одному.

Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам)

Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:
=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки])
Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.

Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:
НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:
--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк:

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))

Повторное применение фильтра

Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.

Просто нажимайте Ctrl + Alt + L . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).

Друзья, искренне рекомендую руководство по функции ФИЛЬТР.XML от Михаила Музыкина ⤵️В формате книги Excel ниже в отдельном его посте.

Это уже не такая новая функция, она доступна начиная с Excel 2013, и может заменить функции для работы с регулярками, которые доступны только в последней версии. Основное ее назначение — импорт XML из сети, но можно использовать ее и для магических манипуляций с текстовыми строками.

ФИЛЬТР.XML - xpath
#ExcelFunctions

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

Пы.Сы. Не пугайтесь - M остается основной тематикой канала. Вечером обязательно будет привычный пост - сегодня разберем Folder.Contents.

Надеюсь, будет полезно.
Всех благ!
@buchlotnik

Функция LAMBDA в Google Таблицах — видео
(для Excel — нового — тоже актуально, ибо принципы работы максимально похожи)

— Зачем нужна LAMBDA
— MAP и столбец / двумерный массив / несколько массивов
— BYROW: обработка строк
— LAMBDA vs старые формулы массива
— MAP + INDIRECT: обработка данных с нескольких листов по списку

Ссылка на таблицу со всеми примерами

Это мой кусок сегодняшнего вебинара на канале "Google Таблицы".
Там были технические сложности, так что перезаписал с нуля с хорошим звуком. Даже больше, чем было)

Наслаждайтесь:
Kinescope (доступно в России)
Ютуб

Спасибо Ренату 🙂

Друзья, мой соавтор по каналу Ренат Шагабутдинов решил сосредоточиться на своем проекте @lemur_excel, поэтому этот канал дальше буду вести только я, Евгений Намоконов.

Мы с Ренатом вместе написали книгу "Google Таблицы. Это просто. Функции и приемы"

Также мы вместе делали курс на Скиллбоксе и много лет вели этот канал бок о бок. Ренат больше писал про формулы, а я про скрипты.

У Рената в его канале @lemur_excel есть рубрики, например "хоткеи по понедельникам" и разные полезности для тех, кто работает с Excel.

Пожелаем Ренату удачи 👏

Горячие клавиши по понедельникам 🔥

Сегодня два сочетания для выделения столбца/строки.

Обратите внимание, что они работают и в официальных ("умных") таблицах, и просто на листе.

В случае Таблиц с большой буквы Т сначала выделяются данные в пределах таблицы (если столбец — то сначала тело столбца, потом с заголовками и итогами и только потом уже весь столбец листа)

И эти сочетания работают при вводе формул!

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

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

Смотрим на GIF (без звука)

Функция ПОСЛЕД / SEQUENCE — видео
Продолжительность: 15:40

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

Видео (это и другие) на сайте — доступно в России
Ютуб
Ссылка на файл с примерами

⚠️ 13 умных шаблонов Google-таблиц, которые сделают вашу работу проще и эффективнее

Как бы вы оценили свое умение работать с таблицами по 10-балльной шкале? Даже если вы уверены в своих навыках — всегда есть пространство для новых инструментов, которые сделают вашу работу еще эффективнее.

Рекомендуем подписаться на канал Мастер CFO. Автор канала — Софья, эксперт в области фин. и бух. учета со стажем 15 лет.

Создала самое крупное сообщество для финансистов и бухгалтеров, численностью 40 600 человек.

➡️ Что вас ждет на канале:
– готовые шаблоны таблиц для автоматизации рутины
– уроки по внедрению ChatGPT в финансовую работу
– лайфхаки, которые помогут зарабатывать в 2 раза больше
– подробные инструкции по оптимизации процессов
– реальные кейсы и экспертные советы
– ежедневное общение с коллегами

📎 Сохраняйте «13 умных шаблонов Google-таблиц»

✅ К каждой таблице подготовлена подробная инструкция по работе с ней.

Нумеруем только видимые строки

Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.

Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.

В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).

А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.

Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)

$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.

_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки

Нарастающий итог: на старых и новых формулах

Старые формулы:
Вводим формулу, в которой суммируем диапазон, начинающийся и заканчивающийся в одной и той же ячейке — первой ячейке с данными.
Но закрепляем первую из них (начало):

=СУММ($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

Хороший новогодний подарок для ваших знакомых экселье

Напоминаем — в продаже третье издание "Магии таблиц". Это 500 с лишним страниц, только свежак и все актуальное, файлы со всеми примерами, от интерфейса до введения в PQ и PP, все новые функции, включая GROUPBY и PIVOTBY, твердый переплет.

Сейчас бумажная версия в издательстве стоит всего 850 рублей. Сравните с книгами про Excel по 30 долларов (+доставка) с ужасным качеством бумаги и скриншотов и мягкой обложкой (но великолепным содержанием, тут без вопросов) и хватайте подарок для всех, кто живет и работает в таблицах!

Вот где можно купить: Озон, ВБ, Литрес (электро), МИФ (бумага и электро)

Отзыв Николая Павлова, автора проекта "Планета Excel" и замечательных книг:

Уникальность этой книги в том, что впервые под одной обложкой собрана коллекция наиболее эффективных и полезных приемов и функций сразу из Microsoft Excel и Google Sheets – самых мощных, на сегодняшний день, инструментов для работы с электронными таблицами. Это сравнение двух программ, показ их различий и общих возможностей позволит пользователям гибко переключаться на нужный инструмент при необходимости. А это дорогого стоит.