Оберни колонки: новая (относительно) функция WRAPCOLS

Итак, нам с вами нужно превратить одномерный массив — например, столбец, в котором данные цикличные (время начала мероприятия + N строк с выступающими в нашем примере) — в двумерный, разместив каждый повторяющийся "блок" в отдельный столбец.

Засунем диапазон в WRAPCOLS, вторым аргументом укажем, сколько ячеек отправлять в каждый столбец. Необязательный третий аргумент — как возвращать пустые ячейки из исходника, если они там будут. Иначе будет выводиться ошибка #N/A (#Н/Д).

=WRAPCOLS(A1:A;N; [чем заменить пустые])

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

=WRAPCOLS(FILTER(A1:A;A1:A<>"");N)

P.S. Раз есть функция WRAPCOLS — значит — это кому-нибудь нужно? есть и WRAPROWS.
P.P.S. В Excel (365) при русскоязычном интерфейсе — СВЕРНСТОЛБЦ и СВЕРНСТРОК.

Читать полностью…

Как проверить, есть ли в текстовом значении кириллица / латиница?

Способ второй, формульный.

С помощью функции КОДСИМВ / CODE определим коды каждого символа в нашем тексте. По коду можно однозначно определить, латиница это (коды 65-90 и 97-122) или кириллица (192-255).
В старых версиях Excel можно протянуть такую формулу, которая в каждом столбце (функция СТОЛБЕЦ / COLUMN дает номер столбца, в котором находится формула) будет извлекать (функция ПСТР / MID) код (функция КОДСИМВ / CODE) каждого очередного символа из ячейки. Когда символы закончатся, вместо ошибки функция ЕСЛИОШИБКА / IFERROR выдаст пустоту.
=ЕСЛИОШИБКА(КОДСИМВ(ПСТР(ячейка с текстом;СТОЛБЕЦ()-1;1));"")

В новых версиях (2021, 365) можно одной формулой извлечь все символы. Функция ПОСЛЕД / SEQUENCE выдаст последовательность символов от единицы до числа символов в тексте (определяется функцией ДЛСТР / LEN):
=КОДСИМВ(ПСТР(ячейка с текстом;ПОСЛЕД(1;ДЛСТР(ячейка с текстом));1))

Ну а далее можно проверять (например, через функции ЕСЛИ / IF и И / AND), к какому диапазону относится символ.
=ЕСЛИ(И(ячейка с символом>=192;ячейка с символом<=255);"кириллица"; "латиница/символ")

Файл с примером в отдельном сообщении выше.

Читать полностью…

Итак, вопрос от подписчика: как разделить целое число на N неравных частей в соотношении, определённом случайным образом.

Генерируем случайные числа, потом делим каждое из них на их сумму (чтобы получить в сумме не произвольное число, а ровно единицу, 100%, это будут случайные доли, на которые мы потом умножим наше число).

Генерировать случайное число в любой версии Excel можно с помощью функции СЛЧИС / RAND, а целый массив чисел с помощью новой функции СЛМАССИВ / RANDBETWEEN.

Далее просто делим каждое на сумму всех чисел. В примере на скриншоте обратите внимание на ссылку с решеткой: это новый тип ссылок в Excel 2021 / 365. Так как теперь есть функции вроде СЛМАССИВ и других, которые, будучи в обычной ячейке, выдают массив на несколько, то есть и возможность ссылаться на них. A2# — это ссылка на массив, который возвращает формула, находящаяся в A2.
В данной задаче это не обязательно, конечно, можно решать ее через обычную СЛЧИС и ссылки на ячейки и диапазоны как раньше.

Когда вы получили «случайные» доли, остается умножить число на них.

Читать полностью…

Быстрая фильтрация в сводной таблице

Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).

Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

P.S. Если тема сводных для вас актуальна и вы любите запрыгивать на подножку уезжающего поезда, то завтра начинается практикум по сводным таблицам. Три встречи по 2 часа, возможность выиграть призы за лучшую домашку, десятки слайдов, файлы с примерами в исходном и готовом состоянии, кот Лемур и пес Штрудель в качестве соавторов ДЗ — вот такой комплект!

Ловите промокод, с ним совокупная скидка на практикум составит 35%: Lemur_35
Записываться здесь:
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Читать полностью…

Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT

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

У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.

=ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2)

Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засовываем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.

В общем виде будет так:

=ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2)

Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.

Читать полностью…

А еще можно фильтровать по значению, цвету заливки / шрифта и даже значку (из наборов условного форматирования) с помощью контекстного меню. Щелкайте правой кнопкой по ячейке с нужным значением/цветом и выбирайте "Фильтр" — и далее нужный вариант.

Читать полностью…

Схватка двух ёкодзун: сравнение Excel и Google Spreadsheets.

Друзья, в этой (регулярно обновляемой) табличке — подробное сравнение Excel (не Online, а обычной версии) и Google Таблиц: какие функции есть только в одном из редакторов, что ломается и что сохраняется при переносе файлов, какие есть нюансы в сводных, пользовательских форматах и других темах.

По некоторым темам и функциями есть ссылки на дополнительные материалы (видео, посты).

https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

Читать полностью…

У вас есть список в несколько столбцов, а вам нужно сделать его одномерным?

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

Тогда используем функцию TOCOL / ПОСТОЛБЦ (увы, недоступную в старых версиях Excel).
Чтобы сделать список без повторов (то есть если человек выступает в разное время или разные дни, мы все равно упоминаем его один раз) и отсортировать, можно добавить функции УНИК / UNIQUE (только уникальные значения) и СОРТ / SORT (сортировка).

Читать полностью…

Серая тема для черно-белой печати диаграмм и других объектов в Excel

При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная.
Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий.

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

Итак, если вы планируете печатать в ч/б ваш отчет:
Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая
Page Layout — Themes — Colors — Grayscale

Читать полностью…

Хотите, чтобы сводная таблица обновлялась автоматически при открытии файла? Например, если вы отправляете книгу Excel с отчетом коллегам и не уверены, что все обновят сводную сами.

1 Правая кнопка по сводной — "Параметры сводной таблицы" (PivotTable Options)

2 Переходим на вкладку "Данные" (Data)

3 Включаем флажок "Обновить при открытии файла" (Refresh Data When Opening The File)

Читать полностью…

Заменяем в сводной таблице пустые значения нулями

По умолчанию в области значений в сводной нулевые значения будут пустыми (допустим, если по конкретному параметру или сочетанию параметров нет остатков / не было продаж и т.п.)

Если хотите заменить пустые ячейки нулями:
1 Вкладка "Анализ сводной таблицы" (PivotTable Analyze) —> Параметры или правая кнопка мыши по сводной и в контекстном меню "Параметры сводной таблицы" (PivotTable Options).

2 На первой вкладке "Макет и формат" (Layout & Format) вводим значение 0 в поле "Для пустых ячеек отображать" (For empty cells show).

Читать полностью…

Если в диалоговом окне нет подчеркнутых букв в названии вкладок, все равно можно перемещаться с помощью сочетаний клавиш Ctrl + PgDn (к следующей) и Ctrl + PgUp (к предыдущей, налево).

Движение идет по кругу. То есть если вы на первой вкладке, Ctrl + Page Up откроет последнюю.

Читать полностью…

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

Чем полезны стили?
— Можно настроить совокупность параметров форматирования (числовой формат, выравнивание, заливка, шрифт, границы) и использовать в будущем для разных ячеек «в один клик».
— Сам стиль можно поменять в любой момент (нажмите для этого в списке стилей правой кнопкой мыши на тот, что хотите настроить), и изменения будут применяться ко всем ячейкам с этим стилем (например, можно не переживать, что заголовки в документе будут разные — если применять к ним один стиль, то сможете регулировать внешний вид всех заголовков через настройку этого стиля).

Стили существуют в рамках одной рабочей книги Excel. Можно забрать стили из другой открытой книги ("Объединить стили", Merge Styles).

Читать полностью…

А еще в окне «Найти и заменить» (как и в случае с рядом других инструментов и функций Excel) можно использовать символы подстановки!

* — любой текст, в том числе нулевой длины (то есть на месте звездочки может не быть ничего);
? — один любой символ (на месте знака вопроса обязательно должен быть символ).

Например, если вам нужно найти/заменить/удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести:

(*)

А если нужно найти все скобки, в которых внутри слова строго из 4 букв (или 4 цифры или же 4 любых символа), нужно указать четыре знака вопроса в скобках:

(????)

Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~).
~* — поиск звездочки,
~? — поиск знака вопроса,
~~ — поиск самой тильды.

Читать полностью…

В функциях СУММЕСЛИМН / SUMIFS и других для вычислений с условиями диапазоны могут быть и строками, а не столбцами.

Например, если нам нужно суммировать не все столбцы, а только те, в которых есть слово "количество" и год 2023 (то есть продажи в штуках, а не деньгах, и за 2023 год, а не другие) — диапазоном условий будет строка с заголовками. А диапазоном суммирования — текущая строка с числовыми данными.

Условие будет в нашем примере такое:

количество*2023

У нас задано начало и окончание ячейки, а месяц между "количество" и годом может быть любой.

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

Читать полностью…

Как проверить, есть ли в текстовом значении кириллица / латиница?

Способ третий, одной формулой для пользователей нового Excel с функцией LAMBDA.

Одной формулой проверяем каждый символ по порядку — и если это латиница, то извлекаем его и его порядковый номер в текстовой строке.
В общем виде:

=REDUCE(пустота как начальное значение ; формула для извлечения всех символов; LAMBDA для последовательной проверки каждого символа и склеивания всех латинских)

Формула для извлечения всех символов — это как обсуждали в посте выше, сочетание ПСТР / MID и ПОСЛЕД / SEQUENCE.

ПСТР(ячейка с текстом;ПОСЛЕД(1;ДЛСТР(ячейка с текстом));1)

На выходе будет массив из отдельных символов.

И далее мы лямбдой последовательно проверяем каждый символ из этого массива символов — ЕСЛИ буква (вторая переменная в LAMBDA, у нас так и называется — буква) попадает в диапазон латинских символов, то мы ее "забираем" в результат (первая переменная в LAMBDA, у нас называется итог), приклеивая к предыдущим собранным буквам, разделяя их переводом строки (функция СИМВОЛ / CHAR с кодом 10) или любым другим символом по вашему вкусу.
Опционально можно добавить позицию буквы в текстовой строки (ее можно вычислить функцией НАЙТИ / FIND).

Итоговая формула:

=REDUCE("";ПСТР(ячейка;ПОСЛЕД(1;ДЛСТР(ячейка));1);
LAMBDA(итог;буква;
ЕСЛИ(И(КОДСИМВ(буква)>=65; КОДСИМВ(буква)<=122); итог&СИМВОЛ(10)&буква&"(позиция "&НАЙТИ(буква;ячейка)&")";итог)))

Файл с формулой в отдельном сообщении выше.

Читать полностью…

Как проверить, есть ли в текстовом значении кириллица / латиница?

Способ первый, простой и визуальный — поменять шрифт на какой-либо, не поддерживающий оба языка. Пример на скриншоте — русские "с" и "е" выделяются визуально на фоне остальных.

Читать полностью…

Что почитать про сводные таблицы?
Конечно, книги Билла Джелена. У него выходит издание по каждой версии Excel.

Шикарная книга, без альтернатив для глубокого погружения в сводные таблицы. Тут и все мыслимые нюансы "классических сводных" (построенных по данным в Excel), и сводные диаграммы, и Power Query, и работа со сводными через макросы, и функция GETPIVOTDATA, и сводные по модели данных (Power Pivot), и функции кубов (превращение сводной, основанной на модели данных, в формулы).

Слева обновление по последней версии 365. То есть это и для тех, у кого "коробочная" версия Excel 2021, и для подписчиков 365, и для тех, кто использует Excel Online в браузере, где сводные наконец появились (последние два пункта будут вместе, скорее всего).

Есть пара новых техник в главе с лайфхаками, но главное — новые главы. Про сводные таблицы в Excel Online, про построение "сводных таблиц" без собственно сводных - с помощью новых формул с динамическими массивами (которые как раз появились в 365 и 2021) или внутри Power Query. Также есть глава про "анпивот" — unpivoting в Power Query.

Книга по версии 2021 должна выйти на русском уже буквально на днях. Обратите внимание, что в российском издании скриншоты будут с оригинальным (англоязычным) интерфейсом, как и файлы-примеры. Команды и функции будут на 2 языках. Это особенно удобно для тех, кто работает в Excel с англоязычным интерфейсом, но читать хочет на русском.

В книге по 2019 версии, выходившей в другом издательстве, скриншоты и файлы на русском, команды в тексте только на русском.

Ссылка на полный обзор книг по Excel:
renat_shagabutdinov/excellent_books" rel="nofollow">https://teletype.in/@renat_shagabutdinov/excellent_books

Читать полностью…

Считаем уникальные значения в сводной таблице

Допустим, у нас есть таблица со сделками: в разных городах с разными клиентами. Мы хотим понять, сколько в каждом городе у нас клиентов.
Если в сводной считать "Количество" по городам, то это будет количество строк, то есть сделок, а не уникальных значений.

Увы, в стандартном наборе вычислений (из 11 операций) в сводных подсчета уникальных значений нет. Но если добавить наши данные в модель данных (Power Pivot) при создании сводной, то такая возможность появится! В сам Power Pivot можно даже не заходить, и не обязательно собственно строить модель данных, добавляя туда еще какие-то таблицы. Если вам нужна только эта возможность — просто включите флажок "Добавить эти данные в модель данных" (Add this data to the Data Model) при вставке сводной. И далее в параметрах поля значений выбирайте операцию "Число разных элементов" (Distinct Count).

P.S. А в Google Таблицах функция для подсчета уникальных в сводных есть — COUNTUNIQUE (как и обычная функция рабочего листа, не в сводных, с таким именем).

Читать полностью…

Друзья, привет! Обновляем подборку самых сочных постов канала, посмотрите, не пропустили ли чего полезного:

Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке

Макрос для сравнения двух файлов (книг Excel)

Удаляем строки с пустыми ячейками в одном из столбцов

Макрос: создаем оглавление в книге

Анализируем сезонность в сводной таблице

Поиск по двум критериям

План-факт через комбинированную диаграмму

Видеоурок: "старые" и новые формулы массивов

Функция СУММЕСЛИМН / SUMIFS: сумма по условиям

Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

Читать полностью…

Фильтр по выделенному

Итак, вы хотите фильтровать данные по значению выделенной ячейки. Для этого можно добавить кнопку на панель быстрого доступа. Называется она "Автофильтр", но это именно фильтр по выделенному значению.

Параметры Excel — Настроить панель быстрого доступа (панель инструментов — все команды или команды не на ленте — Автофильтр — Добавить

Customize Quick Access Toolbar — Commands Not in The Ribbon — AutoFilter — Add

Теперь ставим фильтр в диапазоне (если его еще не было), выделяем ячейку, по которой нужно отфильтровать, и нажимаем на добавленную на панель быстрого доступа кнопку!
А еще кот Лемур напоминает: команды на панели можно вызывать сочетанием Alt (или косой черты или F10) и цифры (соответствующей расположению нужной команды по порядку). Так что у нас с вами теперь есть сочетание клавиш для фильтрации по выбранному значению!

Читать полностью…

Пользуетесь окном поиска в Excel?

Если нет, уберите его в параметрах:
Файл — Параметры — Общие — Свернуть поле Поиска

File — Options — General — Collapse the Microsoft Search Box

В любом случае справа у вас останется иконка лупы для раскрытия этого поля. Но зато оно не будет все время занимать часть панели наверху.

Читать полностью…

Если мы склеиваем текстовые значения и числа формулой, например, к фиксированному тексту "Средние продажи:" добавляем функцию СРЗНАЧ/AVERAGE, которая будет возвращать среднее значение:

="Средние продажи: "&СРЗНАЧ(C2:C13)

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

Формат ячейки с формулой менять бесполезно: в ней уже хранится текст (потому что на выходе в результате работы операции "склеивания", конкатенации, которая происходит благодаря символу &, мы получаем именно текстовое значение). И отформатировать числовой кусочек текста отдельно через формат ячейки нельзя.

Выход — использовать функцию ТЕКСТ / TEXT. Она может любое значение (в том числе число, взятое из ячейки или полученное вычислением) отформатировать по заданному числовому формату и возвращает результат в виде текста.

Синтаксис:

=ТЕКСТ(число; формат).

Формат указывается как в пользовательских форматах (подробнее о них см видео).

Так что в нашем случае, если мы хотим, чтобы все средние значения были с одним знаком после запятой, понадобится следующая формула:

="Средние продажи: "&ТЕКСТ(СРЗНАЧ(B2:B13);"0,0")

Читать полностью…

Один из множества слайдов, которые мы с котом Лемуром готовим к июньскому практикуму по сводным таблицам (самому мощному инструменту для анализа данных).

Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :)
Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания.

Еще будут домашки, их разбор (и подарки чемпионам), файлы-примеры до и после, ответы на вопросы.

Приходите, встречи будут 14, 20 и 23 июня (а если есть вопросы по формату обучения — спрашивайте в ЛС или комментариях):
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Читать полностью…

Еще один вариант для комментариев в формуле — функция LET. Но ее нет в Excel вплоть до версии 2019 😿

Комментарии — лишь повод про нее напомнить, так как функционал у нее шире.

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

LET(имя1; значение_имени1; [имя2; значение_имени2]; …; вычисление)

Давайте посмотрим на совсем простой пример — зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET:
=LET(a;50;b;10;a*b)
На выходе будет 500.

В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a:
=LET(a;50;b;10*a;a*b)
На выходе будет 25000.

Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.

Возвращаясь к нашей теме с "комментариями": можно задать переменную (с любым названием) и присвоить ей текстовое значение.
=LET(переменная; "комментарий"; [другие переменные для вычислений]; ... ; вычисление).

Читать полностью…

Добавляем комментарий к функции

Немного экзотики. Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.

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

Например:

=E2*15% + Ч("Вычисляем комиссию менеджера как 15% от суммы сделки")

Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри функции текст есть, а к результату эта часть ничего не добавляет.

Заодно кот Лемур напоминает: в формулах можно использовать переход на следующую строку для лучшей читаемости (Alt + Enter).

Читать полностью…

У вас открыто диалоговое окно в Excel и есть несколько вкладок/разделов?

Если в их названиях есть подчеркнутые буквы, то можно нажимать Alt в сочетании с этими буквами для перемещения без мышки.

На скриншоте окно вставки гиперссылки (вызывается по сочетанию Ctrl + K).

Читать полностью…

Группировка нескольких текстовых элементов в сводной

Допустим, в ваших исходных данных есть категория товара. Мониторы, ноутбуки и прочее, прочее.
Вы хотите объединить несколько категорий в одну группу в сводной таблице, чтобы смотреть на общие данные (продажи, остатки...) по всем сразу.

Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);

2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке "Анализ сводной таблицы" (PivotTable Analyze) — "Группировка по выделенному" (Group Selection)

3 Щелкаем на название группы (по умолчанию будет "Группа1") и переименовываем.

Если хотите научиться всем основным заклинаниям в сводных таблицах, приходите на практикум в июне, который мы с Лемуром проведем в МИФе. Будет три очень интенсивных учебных дня с домашкой!

Читать полностью…

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.

Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).

Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).

— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?

— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

Читать полностью…

Табличка с примерами записи условий в функциях СУММЕСЛИМН / SUMIFS и других подобных функций.

Если вам нужно брать условие из ячейки и при этом добавлять к нему знаки сравнения, то приходится склеивать общее условие из двух частей:
— знаки сравнения, буду текстом, который "живет" в формуле, берутся в кавычки
— мы добавляем знак & (амперсанд), объединяющий текстовые строки в одну
— добавляем ссылку на ячейку.

Если вам нужно суммировать (усреднять, подсчитывать) данные за период, то условий будет два — на один и тот же столбец с датами. Одно — нижняя граница, второе — верхняя. Например, если в столбце B даты продаж, а нам нужны продажи за 2 квартал 2023, функция будет выглядеть так:

=СУММЕСЛИМН(диапазон суммирования; B:B; ">=01.04.2023"; B:B; "<=30.06.2023")

Читать полностью…