Магия Excel

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

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

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

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

10 лайфхаков Excel: вы точно найдете тут что-то новое для себя! Длительность: 12 мин — Ссылка на несколько листов — Мгновенное заполнение — Быстрое удаление столбцов — Выделение пустых ячеек или отличий по строкам — Быстрая специальная вставка — И другое! Смотреть видео на Youtube Или на сайте (доступно в России) — там есть и десятки других видеоуроков

Хотите, чтобы нули было сразу видно (чтобы они отличались от букв О)? Используйте шрифт Consolas — на скриншоте он справа, а слева стандартный на данный момент шрифт Aptos.

Выделяем только видимые ячейки Длительность: 5 мин Выделяем только видимые ячейки (разными способами), чтобы потом: — скопировать только их (например, только итоги) и вставить на другой лист — отформатировать их (применить заливку только к итогам, а не ко всем строкам) — вставить только в эти ячейки формулы — например, чтобы изменить шаблонные заголовки промежуточных итогов. Это же видео на Kinescope (доступно в России без ограничений) И оно же на Youtube Оно же и десятки других бесплатных видео на сайте А для подписчиков на Sponsr есть расширенная версия этого урока, как и десятки других подробных видео (и каждую неделю новое!)

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

Автозавершение функции в Power Query Есть одна раздражающая вещь в PQ — это автозавершение функций Вот написали вы Text.. Нашли в списке нужную вам функцию Text.Proper, допустим Нажали Tab... И получили TextText.Proper А-А-А-А-А 😵 И что еще печальнее, эта гадость все портит, когда у вас вложенные функции. Старая функция стирается. Как быть? Начинайте вводить с точки — как в прикрепленном видео (без звука). Будет работать и в настраиваемом столбце, и в строке формул, и в расширенном редакторе.

Версия совместимости функций Во все времена разработчики Excel дорабатывали функции, если что-то работало не так — но обычно им приходилось создавать новую функцию, а не менять старую. Ведь если изменить поведение старой функции, это затронет миллионы рабочих книг, в которых она уже работает в формулах. Вот и появлялась ОКРВВЕРХ.МАТ (CEILING.MATH) в дополнение к ОКРВВЕРХ (CEILING). А что теперь? Теперь есть два режима — поведение по умолчанию (вариант 1) и последняя версия функций. Получается, что теперь разработчики смогут менять поведение существующих функций, исправляя какие-то проблемы. В старых рабочих книгах можно будет оставлять старое поведение функций. А в новых использовать новые функции. Смотрим, как это уже происходит воочию на примере ДЛСТР / LEN, которая определяет число символов в текстовой строке. В старом исполнении она считает один эмодзи за два символа (так называемая суррогатная пара в Unicode). Это технически верно, но мы воспринимаем эмодзи за один символ. И теперь функция считает именно так — смотрим в прикрепленном видео на несколько секунд без звука. Где находится новый переключатель? Формулы — Параметры вычислений — Версия совместимости — Версия 1 / Версия 2 Formulas — Calculation Options — Compatibility Versions — Version 1 / Version 2

Горячие клавиши по понедельникам🔥 На этой неделе у нас сочетания для создания, закрытия, открытия и сохранения книг. На что обращаем внимание: — Если книга новая, то "Сохранить" и "Сохранить как" будут одним и тем же — сначала нужно сохранить книгу первый раз под каким-то именем, чтобы потом просто сохранять — Для сохранения есть и еще одно сочетание — Alt + цифра (а какая — зависит от того, какой по счету у вас дискетка на панели быстрого доступа). Конечно, и любую другую команду, в том числе "Открыть" или "Создать" можно добавить на панель быстрого доступа, чтобы получить возможность вызывать команду по сочетанию Alt + цифра. Как изменить шаблон новой книги (которая создается по Ctrl + N)? Читайте здесь.

Как разделить текст по нескольким разделителям? Например, по косой черте и дефису, как в примере. В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT. А чтобы она работала с несколькими разделителями, отправим их в массив: {"первый разделитель"; "второй"; ... } Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем: "/-" А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи: =SPLIT(A2; "/-") или =SPLIT(A2; "/-"; 1)