Начало текущей недели — формулой

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

=СЕГОДНЯ()-ДЕНЬНЕД(СЕГОДНЯ();2)+1

Что тут происходит?
1 Мы берем текущую дату (функция СЕГОДНЯ / TODAY)

2 Вычитаем из нее сегодняшний же день недели (функция ДЕНЬНЕД / WEEKDAY с аргументом 2 возвращает порядковый номер дня недели — например, 4 для четверга; аргумент 2 определяет привычную для нас систему нумерации, когда неделя начинается с понедельника — иначе, если этот аргумент пропустить, функция будет возвращать 1 для воскресенья, а не понедельника, как принято в некоторых странах, включая США).

3 Прибавляем единицу, чтобы получить не воскресенье предыдущей недели, а понедельник текущей.

А вот тут еще целая пачка формул для извлечения всякого-разного из даты.

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

Как анализировать массивы данных, и находить закономерности в цифрах? И как это может помочь бизнесу развиваться и находить новые перспективы? Об этом вы узнаете на курсе от Skillbox «Аналитик данных с нуля».

Бесплатный доступ к первым 3 модулям!

Курс поможет систематизировать разрозненные знания и применить их на практике. Даже если у вас нулевая подготовка! Всё доступно, доходчиво, на реальных кейсах и примерах. Контроль полученных знаний на каждом этапе и общение с коллегами и наставником.


Для первых 20 участников мы подготовили приятную скидку на весь курс! Также есть возможность оплатить обучение в рассрочку.
В подарок вы получаете курс по созданию эффектных презентаций PowerPoint!
А также помощь в трудоустройстве от Центра карьеры Skillbox.

Регистрация по ссылке: https://epic.st/it9CF

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

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

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

Это три вебинара минимум по 2 часа с возможностью после основной части задавать вопросы до победного, нескучные домашки с конкурсом (лучшие ДЗ = книги и курсы от МИФа), слайды по всем темам, файлы со всеми примерами в исходном и готовом виде. А еще чат на время всего практикума, где можно спрашивать про домашки, темы курса и про таблицы вообще. Функции везде на двух языках.

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

Сотрудники перестали ломать общие таблицы, выучили, как пользоваться фильтрами. Меньше стали приходить к аналитику, появилась конструктивная критика при создании/обновлении таблиц

Записываться тут, там же детали и программа:
https://www.mann-ivanov-ferber.ru/courses/gdrive_practicum/

Скидка 35% образуется путем ввода вот этих буков: GOEXC. После 7-го марта магия букв исчезнет.

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

Продолжать осваивать новое, чтобы сделать жизнь чуточку понятнее и проще, особенно важно в период неустойчивости. Так, автоматизировать регулярные рабочие задачи помогает владение инструментами Excel. Это мощный инструмент, который могут быстро освоить даже новички.

Пройти весь путь анализа: от подготовки данных до представления результатов, поможет бесплатный курс-симулятор «Excel: простые шаги для оптимизации работы с данными».

Вы изучите:
– основные функции Excel, которые помогут автоматизировать расчёты;
– базовые инструменты подготовки данных для анализа;
– сводные таблицы, чтобы самостоятельно составить отчёт;
– концепцию анализа данных.

Вас ждут 4 занятия: вебинары и практические задания. В результате вы сможете анализировать данные, чтобы оперативно и качественно выполнять рабочие задачи и принимать управленческие решения на профессиональном уровне.

Регистрация

Реклама. ООО "Нетология". Erid: LatgBybCY

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

Тепловая карта (шкала) без чисел

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

;;;

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

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

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

Определяем первую и последнюю даты квартала по его номеру.

Ловите формулы для определения первой и последней даты квартала по номеру года и номеру квартала. Может пригодиться, если у вас отчеты с функциями СУММЕСЛИМН и другими, куда вы отправляете даты в качестве условий, а пользователю предлагаете выбирать только номер года/квартала в ячейках.
Здесь мы используем функцию ДАТА / DATE, которая позволяет "собирать" дату по трем ее параметрам. Номер месяца мы определяем исходя из номера квартала. Для даты конца квартала используем функцию КОНМЕСЯЦА / EOMONTH (мы не можем использовать фиксированное число для конца квартала, потому что в разные месяцы разное число дней — поэтому находим первый день последнего месяца квартала как

ДАТА(год; квартал * 3 ;1)

, а потом определяем последнюю дату в этом месяце (функция КОНМЕСЯЦА определяет последнюю дату месяца со сдвигом от заданной — мы никуда не сдвигаемся, поэтому последний ее аргумент равен нулю).

Начало квартала:

ДАТА(год; квартал * 3 - 2 ;1)

Конец квартала:

КОНМЕСЯЦА(ДАТА(год; квартал * 3 ;1);0)

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

Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP. Старая и новая функции для объединения таблиц (поиска текста и чисел)

=VLOOKUP(что ищем; таблица, в которой поиск идет в первом столбце; номер столбца, из которого забираем данные; [режим поиска])

=XLOOKUP (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])

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

— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.

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

— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.

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

— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.

— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) - для этого задаем последний аргумент search_mode равным -1.

— В Excel XLOOKUP есть только в 2021 / 365. В старых версиях формулы с этой функцией будут возвращать ошибку #ИМЯ? / #NAME?

Посты по теме:
Видео про функцию ПРОСМОТРX / XLOOKUP
Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT
ПРОСМОТР (LOOKUP), да не X

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

Когда при вводе формулы вы выделяете диапазон, появляется вот такая подсказка с числом строк (R) и столбцов (C) в нем.

Удобно, когда нужно понять, из скольки вариантов выбирать случайный, из какого по счету столбца тянуть данные его высочество ВПР'ом и т.д.

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

Финансовый аналитик и бухгалтер — незаменимые профессии в крупном и малом бизнесе. Они отвечают за деньги компании, помогают избежать штрафов, сократить расходы и увеличить прибыль. А научиться основам этих профессий можно на мини-курсе Skillbox: https://epic.st/PLIBpx

В программе:
— hard и soft skills бухгалтера и финансиста
— карьерные и зарплатные перспективы
— основы бухгалтерского учёта и финансового менеджмента
— подготовка к собеседованию и составление сопроводительного письма

Мини-курс подойдёт как начинающим бухгалтерам и финансистам, так и практикующим специалистам.

Спикер — Надежда Лебедева, управляющий директор в крупном банке, 20 лет опыта в сфере финансов. Сертифицированный специалист (FCCA) в области финансов, учёта и аудита. Приглашённый преподаватель в РЭУ имени Плеханова и НИУ ВШЭ.

Всех участников ждут бонусы, успейте получить доступ к курсу бесплатно!

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 970408888

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

Друзья, новость про курс "Магия Excel" в МИФе: записали с Лемуром новый модуль про Power Pivot.

Это вводная информация для знакомства с моделью данных Excel (Power Pivot):
— Как настроить отношения между таблицами и построить сводную на основе нескольких таблиц, даже не открывая Power Pivot
— В чем преимущества сводной на основе модели данных: подсчет уникальных значений, использование функций DAX в мерах (можно, например, вычислить медиану), превращать сводную в формулы (функции кубов) и другие плюсы
— Импорт данных из текстовых файлов (в модуле пример с 2 миллионами строк, что в самом Excel даже не вставить), других книг Excel, любых источников — через Power Query (пример с курсами валют с сайта ЦБ)
— Вычисления в Power Pivot (вычисляемые столбцы и меры)
— Power Map: 3D-карты на основе модели данных

Как и во всех остальных модулях курса, помимо видео есть файлы-примеры в исходном и готовом состоянии и домашняя работа.

Всего в курсе теперь 66 видео и все темы — от форматирования и формул до LAMBDA, визуализации данных и Power Query с Power Pivot. Новые функции тоже есть, и новые видео постоянно добавляются — например, анонсированные в ноябре 2023 функции GROUPBY и PIVOTBY появились в виде видеоурока в курсе уже ... в ноябре 2023.

⭐️Слушатели поставили 436 оценок урокам курса. Из них только 4 четверки и ни одной двойки/тройки.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Ловите скидку:
Код LEMURY
-35% до 4 февраля включительно

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

Навигация по листам в книге Excel

В книге много листов?
Щелкните правой кнопкой мыши на стрелки в левом нижнем углу. Откроется список всех листов. Там смотреть удобнее, чем просто по ярлыкам.

А к следующему и предыдущему листу можно переходить с помощью сочетаний клавиш Ctrl + PgDn и Ctrl+PgUp.

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

Столбик в гистограмме можно заменить изображением

Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный столбик (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).

И Ctrl + V — вставляем изображение.

После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире. В нашем случае это поможет с пропорциями!

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

Код макроса для создания отдельных файлов для каждого значения в выбранном столбце

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

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

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

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

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

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

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

Магия двойных щелчков в Excel

Клац-клац 🐱Это действие много где может пригодиться, напоминает кот Лемур. В частности:

— Двойной щелчок по названию вкладки ленты инструментов скрывает и раскрывает ленту

— По кисточке "Формата по образцу". Если на нее щелкать один раз — то вы сможете применить формат выделенной ячейки один раз к другой ячейке / диапазону. А если дважды — то будете в режиме форматирования по образцу, пока не нажмете Esc. То есть сможете форматировать много отдельных ячеек и диапазонов, выделяя их.

— По названию (ярлыку) листа — можно его переименовать. По тексту в фигуре — отредактировать текст.

— По границе выделенной ячейки — перемещение в конец диапазона (например, если щелкнуть дважды на нижний край ячейки , то это будет аналог Ctrl + ↓, перемещение в конец диапазона вниз — до последней заполненной ячейки)

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

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

— Наконец, двойной клик по самому-самому левому верхнему углу окна Excel = закрытие книги.

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

Решаем задачу подписчицы: при выгрузке данных из 1С в числах есть пробелы, которые превращают числа в текстовые строки. Пробелы надо удалить.

Разбираемся, как с помощью функции КОДСИМВ / CODE понять, что за символ вообще перед нами — в данном случае это не обычный пробел, а неразрывный, поэтому его не удалишь вводом обычного пробела с клавиатуры в окне "Найти и заменить". Его нужно либо копировать, либо вводить с помощью кода — Alt+0160.

И делаем макрос, который позволит удалять ненужные символы одним нажатием кнопки или сочетанием клавиш Alt+цифра.

Код макроса можно сократить до такого:

Selection.Replace What:=" ", Replacement:=""

На месте пробела может быть другой символ, который вам нужно удалять в выделенном диапазоне.
Не забывайте: действие макросов отменить через Ctrl+Z нельзя!

Всю задачу разбираем в видео со звуком.

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

Быстро формируем список уникальных значений с помощью расширенного фильтра

Нужны только уникальные значения (без дубликатов)?
Вызываем расширенный фильтр (Данные — Дополнительно / Data — Advanced), выделяем только столбец, из которого нужны уникальные значения, выбираем "Скопировать результат в другое место" (Copy to another location) и выбираем это место, включаем "Только уникальные записи" (Unique records only).

Если формулой с обновлением — то это функция УНИК / UNIQUE, но на только в Microsoft 365😿

P.S. Еще про расширенный фильтр по ссылке.

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

Нумерация с помощью формул

В этом видео рассматриваем несколько вариантов автоматической нумерации строк в диапазоне / таблице:
— с помощью функции СТРОКА / ROW, в том числе с учетом положения таблицы на листе (то есть при вставке новых строк над таблицей нумерация все равно не будет меняться, она останется в пределах таблицы
— с помощью функции ПОСЛЕД / SEQUENCE (живущей в Excel 2021 и Microsoft 365)
— формулой из функций ЕСЛИ / IF, ЕПУСТО / ISBLANK, СТРОКА / ROW и ДВССЫЛ / INDIRECT для нумерации с пропуском пустых строк.

Файл с формулами из видео прикрепляем отдельным сообщением!

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

Какая у вас версия Excel?

Microsoft 365 — иконка с лупой (поиск) справа сверху
Excel 2016-2021 — текстовое поле "Что вы хотите сделать?" сверху
Excel 2013 — названия вкладок ленты инструментов ЗАГЛАВНЫМИ буквами
Excel 2010 — прямоугольная кнопка "Файл" (File) со скругленными углами слева сверху
Excel 2007 — круглая кнопка с логотипом Microsoft Office вместо кнопки "Файл"
Excel 2003 — панель инструментов вместо ленты

Или загляните сюда:
Файл — Учетная запись — О программе Excel
File — Account — About Excel

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

Дано: в ячейке есть символы, нам надо получить текстовую строку с ними же, но отсортированными.

Решение: создаем виртуальный массив из всех символов — для этого создаем последовательность (функция ПОСЛЕД / SEQUENCE) чисел от единицы до числа символов в ячейке (ДЛСТР/ LEN), извлекаем символы с помощью ПСТР / MID — сформированная последовательность выступает номерами символов, которые мы этой функцией извлекаем. То есть мы извлекаем 1, 2, ..., N символов из ячейки, где N — число символов в ней, определенное функцией ДЛСТР.

Далее сортируем полученный массив (СОРТ / SORT) и сразу же собираем его обратно в текстовую строку (СЦЕП / CONCAT).

Получится формула:
=СЦЕП(СОРТ(ПСТР(ячейка;ПОСЛЕД(ДЛСТР(ячейка));1)))

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

Как научиться работать с базами данных и упрощать задачи? Запишитесь на бесплатный мини-курс Skillbox. За 5 дней вы с нуля пройдёте основы SQL, научитесь обрабатывать, анализировать и красиво визуализировать данные на реальных кейсах. Подходит для любого уровня подготовки!

Зарегистрируйтесь прямо сейчас и получите полезный гайд о профессии: https://epic.st/yQpQRj

Что будем делать:

— Писать запросы на языке SQL.
— Проводить аналитику для бизнеса.
— Разрабатывать автоматизированную отчётность в Excel.
— Обрабатывать данные в Power Query.
— Визуализировать показатели в Excel: создавать красивые графики, диаграммы и метрики.
— Применять инструменты Excel для анализа данных.

Спикер — Мкртич Пудеян, специалист по анализу данных в «Газпромбанке». Сертифицированный SQL-разработчик от Microsoft, 8 лет работал специалистом по хранилищам данных в Tele2.

🎉 Всех участников ждут бонусы: 5 полезных статей по SQL и Excel, персональная карьерная консультация, год бесплатного изучения английского языка и скидка 10 000 рублей на любой курс.

🌟Учитесь с нами — откройте дверь в мир знаний за пару кликов!

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

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

7 с половиной вариантов, чтобы извлечь месяц из даты:

1 Функция МЕСЯЦ / MONTH — возвращает число, порядковый номер месяца

2-4 Функция ТЕКСТ / TEXT — она превращает число (в нашем случае дату) в текст нужного формата. Код формата "ММ" — это длинный номер месяца (01 для января, а не 1). Код "МММ" — короткое текстовое название ("янв"), "ММММ" — длинное ("Январь").

5 и 5.1 Функция ИНДЕКС / INDEX, которая извлекает из диапазона / массива с названиями нужное название по порядку. Определяем, какое нужно, по номеру месяца с помощью функции МЕСЯЦ / MONTH. Тут плюс в том, что названия могут быть любыми, какими мы их зададим.

6 — похожее решение, но с помощью функции ВЫБОР / CHOOSE. Определяем номер месяца, извлекаем соответствующее этому номеру текстовое значение, заданное внутри функции ВЫБОР.

7 — Функция ВПР / VLOOKUP с виртуальным массивом внутри. Если хотите превратить диапазон в формуле в статичный массив значений, выделите его и нажмите F9. Подробнее об этом тут.

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

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

Ссылка на несколько листов и функция SHEETS / ЛИСТЫ

Функция SHEETS возвращает число листов в ссылке — ее единственном аргументе.
Погодите-ка, какое еще число листов? Разве ссылка не на один лист всегда?

Нет, как многие знают, можно ссылаться на группу листов. При вводе формулы просто зажмите Shift и щелкните на ярлык листа, до которого вам нужно ссылаться — получите ссылку вида:

=СУММ(Январь:Июнь!A1)

(функция/функции могут быть любыми, не только СУММ, разумеется)

Такая ссылка — это ссылка на все ячейки A1 на листах от "Января" до "Июня" в том порядке, как их ярлыки идут в книге. То есть если между этими листами добавится новый — он попадет в сумму. А если вы перетащите "Апрель" правее "Июня", то он не будет участвовать в вычислении.

Функция SHEETS / ЛИСТЫ позволяет такие ссылки проверять и узнавать, сколько там листов.
Например, можно выдавать сумму диапазонов A2:A10 на всех листах, только если в ссылке 12 листов, а иначе — текст с сообщением об ошибке:
=ЕСЛИ(ЛИСТЫ(Январь:Декабрь!A1)=12 ;
СУММ(Январь:Декабрь!A2:A10);
"Ошибка! Проверьте, что все листы расположены в правильном порядке")

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

Панель быстрого доступа работает не только в самом Excel (и других приложениях Office!), но в и окнах Power Query и Power Pivot

Все работает аналогично: правой кнопкой по команде, которую используете часто —> Добавить на панель быстрого доступа (Add to Quick Access Toolbar)

После этого кнопки будут всегда наверху при любой активированной вкладке ленты инструментов. А еще вы получите возможность вызывать их сочетанием клавиш Alt + цифра (какая именно цифра — зависит от положения команды на панели).

Подробнее про панель быстрого доступа в интерфейсе Excel:
Сыграть на Alt'е - доступ к командам на ленте с помощью клавиатуры
Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа
Как добавить макрос на панель быстрого доступа

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

Отключаем фильтр в отдельных столбцах

Такая секретная магия доступна только через макросы.
Для отключения кнопки у отдельного столбца достаточно одной строчки кода:

ActiveCell.AutoFilter Field:=N, VisibleDropDown:=False


Где N — номер столбца в фильтруемом диапазоне.

Удивите коллег таким нестандартным фильтром 😺

Два нажатия Ctrl+Shift+L (или кнопки Фильтр на ленте) вернет все обратно.

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

Динамический именованный диапазон

Диапазонам и ячейкам в Excel можно присваивать имена (Ctrl+F3 или поле "Имя слева от строки формул или вкладка "Формулы" на ленте).

Что если мы хотим создать имя, за которым будут скрываться все заполненные ячейки в столбце, начиная с A2, например?
То есть чтобы одним и тем же именем (например, Компании, как в примере) назывался диапазон динамического размера — в зависимости от числа заполненных ячеек в столбце.

Тут можно воспользоваться тем, что несколько функций Excel могут возвращать не значения, а ссылку на последнюю ячейку диапазона, когда они следуют за двоеточием после ссылки на первую ячейку:

=$A$2:функция(...)

Это функции ЕСЛИ / IF, ВЫБОР / CHOOSE, ЕСЛИМН / IFS, ДВССЫЛ / INDIRECT, СМЕЩ / OFFSET, ПЕРЕКЛЮЧ / SWITCH и ПРОСМОТРX / XLOOKUP.

Для нашей задачи можно использовать ИНДЕКС — будем с помощью нее получать адрес последней заполненной ячейки в столбце A. Чтобы узнать, какая строка последняя — посчитаем, сколько заполненных ячеек в столбце A с помощью СЧЁТЗ / COUNTA.

=СЧЁТЗ($A:$A)

Значение из последней заполненной ячейки можно получить так:

=ИНДЕКС($A:$A;СЧЁТЗ($A:$A))

Но мы засунем эту конструкцию после ссылки на первую ячейку диапазона, и она не будет возвращать значение из последней заполненной ячейки, а ссылку на нее:

=$A$2:ИНДЕКС($A:$A;СЧЁТЗ($A:$A))

Все это остается отправить в поле "Диапазон" вновь созданного имени.

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

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце)

Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее:

1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса)

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

Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши)

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

Другие макросы:
Макрос для сравнения двух файлов (книг Excel)
Макрос: создаем оглавление в книге
Макрос: удаляем пустые листы
Два варианта макросов для заполнения пустых ячеек

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

Если применяете гистограммы, обращайте внимание на ширины столбцов!

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

В нашем примере получается, что за счет широкого столбца B 115 тыс. во Владимире в 2020 году выглядит как вдвое большее значение, чем те же 115 тыс. в Саранске в 2021 году!

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

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

Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.

Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.

Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть renat_shagabutdinov/wFymDX7fAN4">в статье.

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

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

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

Ловите пачку полезных формул для работы с датами в Excel!

Конец месяца:

=КОНМЕСЯЦА(дата;0)

Начало месяца:

=КОНМЕСЯЦА(дата;-1)+1

Месяц:

=МЕСЯЦ(дата)

День:

=ДЕНЬ(дата)

Год:

=ГОД(дата)

День недели цифрой:

=ДЕНЬНЕД(дата;2)

День недели текстом:

=ТЕКСТ(дата;"ДДДД")

10 рабочих дней от даты:

=РАБДЕНЬ(дата;10)

Рабочих дней в месяце:

=ЧИСТРАБДНИ(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0))

Кол-во вторников в месяце:

=ЧИСТРАБДНИ.МЕЖД(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0);"1011111")

Квартал - вариант 1:

=ЦЕЛОЕ((МЕСЯЦ(дата)+2)/3)

Квартал - вариант 2:

=ВЫБОР(МЕСЯЦ(дата);1;1;1;2;2;2;3;3;3;4;4;4)

Номер недели (ГОСТ):
=НОМНЕДЕЛИ.ISO(дата)

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