Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели - не "01.01.2023", как по умолчанию, а "01.01.2023 Вс".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) - воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику - двойка и так далее.

И остается добавить условие - день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
Все показываем на видео!

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

Попробуйте себя в роли бизнес-аналитика на бесплатном мини-курсе Skillbox! За 5 дней вы научитесь оценивать эффективность процессов в компании и оптимизировать их, а ещё сделаете 4 проекта, которые положите в портфолио.

Регистрация и подробная программа: https://epic.st/bLlngn

Мини-курс подойдёт новичкам без опыта, начинающим аналитикам, а также тем, кто хочет работать удалённо, руководителям, product- и project-менеджерам.

👀 Смотрите видеоматериалы в удобное время
Мы сделали акцент на практике, поэтому в роликах много подробных примеров работы.

📚 Получайте полезные материалы
Мы подготовили для вас чек-листы, которые пригодятся в работе и помогут больше узнать о профессии.

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

🎁 Сделайте себе подарок, который поможет круто повернуть жизнь!

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

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

Как заполнить пустые ячейки (вниз, то есть значениями сверху): макросы

Здесь может быть много вариантов.
Допустим, вы хотите заполнять все пустые ячейки в активном диапазоне — это тот диапазон, который будет выделен по нажатию Ctrl+A (до пустых строк/столбцов).
К нему в коде можно обращаться так:

ActiveCell.CurrentRegion

Вся команда будет выглядеть так: выделяем пустые (как в предыдущем варианте вручную делали через F5 — Выделить) — это метод SpecialCells с параметром (типом выделяемых ячеек) xlCellTypeBlanks. И вставляем во все пустые формулу R[-1]C — то есть ссылаемся на ячейку сверху. Это один из множества примеров того, как формулы со стилем ссылок R1C1 пригождаются в макросах. Не переживайте, если у вас стиль ссылок A1, формулы на листе будут выглядеть именно так: =A2, =A3 и так далее.

ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Так как при отсутствии пустых ячеек в диапазоне будет выдаваться ошибку (ибо выделять будет нечего) — лучше добавить строку для игнорирования ошибок
On Error Resume Next

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

ActiveCell.CurrentRegion.Value = ActiveCell.CurrentRegion.Value

———
Если вы хотите заполнять ячейки только в текущем столбце от активной ячейки вниз, то CurrentRegion не подойдет.
В таком случае лучше взять диапазон от активной ячейки (ActiveCell) до последней ячейки в активной области листа (на нее можно ссылаться так — UsedRange).

С помощью свойства Resize увеличиваем диапазон от одной активной ячейки, добавляя столько строк, сколько есть до конца активной области листа.

АктивнаяЯчейка. Увеличиваем (строк на листе — строка активной ячейки + 1)

Например, активна ячейка A10. На листе 3000 строк. Значит, мы добавляем к активной ячейке 3000-10+1 = 2991 строку и получаем диапазон A10:A3000 (в Resize указывается общее число строк (и столбцов во втором аргументе, если нужно), которое нужно включить в получаемый диапазон).

Узнать число строк в диапазоне можно с помощью свойства Rows, строку активной ячейки — Row.

Все вместе будет выглядеть так:

ActiveCell.Resize(ActiveSheet.UsedRange.Rows.Count - ActiveCell.Row + 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

———
Оба макроса в прикрепленном файле в соседнем посте!

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

Как заполнить пустые ячейки (вниз, то есть значениями сверху)?

Первый вариант — формулами (любая версия, быстро и на один раз)

Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)

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

Нажимаем F5 и далее «Выделить» — «пустые ячейки»

Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)

И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)

То есть в каждой пустой теперь ссылаемся на ячейку сверху

Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)

Алгоритм в коротком видео (без звука).

Второй вариант — Power Query (2010-2013 с надстройкой, 2016+, если нужно неоднократно применять такое и, возможно, другие манипуляции с источником данных)

Добавляем данные в Power Query (Данные — Из таблицы / диапазоны).
Правой кнопкой по столбцу — Заполнить — Вниз.

Третий вариант — макросы (любая версия, если нужно часто применять в работе — работает мгновенно по нажатию кнопки/сочетанию клавиш)
Про макросы напишем в следующем посте!

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

Уникальные пары значений

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

Если столбцы рядом — то можно просто сослаться функцией УНИК / UNIQUE на эти два столбца.

Если они не рядом — то предварительно выбрать их функцией ВЫБОРСТОЛБЦ / CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
=УНИК(ВЫБОРСТОЛБЦ(Сделки;1;3))

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

Теперь формулы со вспомогательными функциями LAMBDA можно записывать короче.

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

=BYROW(диапазон;LAMBDA(строка;СРЗНАЧ(строка)))

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

А теперь будет работать и следующая форма:

=BYROW(диапазон;СРЗНАЧ)

=BYROW(диапазон;AVERAGE)

Подробнее про LAMBDA и BYROW можно узнать в бесплатном мини-курсе на Stepik:
https://stepik.org/course/182713

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

Успейте попасть на бесплатный онлайн практикум по дизайну презентаций от топовой Академии дизайна Bonnie&Slide

3 дня, за которые вы откроете для себя востребованную удалённую профессию.

✔️ Узнаете пошаговый план «Как зарабатывать 200 тыс. в месяц на презентациях». Даже если никогда их не делали
✔️ Разберётесь, как получать 3-5 клиентов, не потратив на это ни копейки
✔️ Поймете, что мешает новичкам зарабатывать и как избежать этих же ошибок

И, конечно, сделаете свои первые слайды в PowerPoint
Кстати, средняя цена за такой слайд — 1500₽!

Записывайтесь на бесплатный практикум прямо сейчас, такой возможности больше не будет!

Реклама. ИП Фирсова С.Ф. ИНН 673110370434 erid:LjN8JzcH3

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

Получаем только четные или нечетные строки

Для такой задачи нам понадобится функция ВЫБОРСТРОК / CHOOSEROWS. Она может извлечь (вернуть) строки по их номерам.

А вот как достать эти номера? Получается, нам нужны строки с первой (если нечетные) до последней в таблице.
Число строк в таблице можно узнать функцией ЧСТРОК / ROWS.

Нечетных строк в таблице половина — то есть мы поделим общее число строк пополам.
Получается, что нам нужна последовательность 1, 3, 5, ..., Число строк в таблице / 2

Иначе говоря, от единицы с шагом 2, количество — половина строк в таблице.

Засунем все эти параметры в функцию ПОСЛЕД / SEQUENCE — она и выдаст нам нужную последовательность:
ПОСЛЕД(ЧСТРОК(Таблица)/2;1;1;2)

А дальше останется использовать ее как аргумент (точнее, аргументы — ведь это много номеров строк) функции ВЫБОРСТРОК / CHOOSEROWS:
=ВЫБОРСТРОК(Таблица;ПОСЛЕД(ЧСТРОК(Таблица)/2;1;1;2))

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

Аргументами функции ВЫБОР / CHOOSE могут и... другие функции!

Можно сделать выпадающий список, в котором будет несколько вариантов вычислений.
В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество.

Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции.

Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!

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

Выбираем случайный элемент из списка

Для начала надо сгенерировать случайное число — с этим поможет функция СЛУЧМЕЖДУ / RANDBETWEEN. Она выдает случайное целое число в заданном диапазоне.

Например, если список из трех элементов:

=СЛУЧМЕЖДУ(1;3)

А чтобы выбрать элемент по сгенерированному номеру, понадобится функция ВЫБОР / CHOOSE. В первом аргументе у нее задается порядковый номер, а далее по порядку элементы, из которых нужно выбрать.
=ВЫБОР(номер; первый элемент; второй; ...)

В нашем случае номером будет случайное число, ну а все следующие аргументы ВЫБОРа — это элементы списка.

В новой версии Excel можно сгенерировать не одно значение, а сразу целый массив, ведь теперь есть функция СЛМАССИВ / RANDARRAY:

=СЛМАССИВ(число строк; число столбцов; от; до; целое)

В нашем случае 10 строк, столбцы пропускаем (1 по умолчанию), от 1 до 3, целые числа. И получим список из 10 случайных элементов списка одной формулой.

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

Новинка: флажки в Excel

Лучше поздно, чем никогда 😺 В Excel — пока только ранним пташкам, получающим обновления первыми — доступны флажки в ячейках. Как в Google Таблицах, где они появились уже давно.

Флажки — переключатели, меняющие значения с ИСТИНА / TRUE на ЛОЖЬ / FALSE и наоборот. Их можно использовать для чек-листов, списков, ссылаться на них в формулах (включили флажок — начисляем скидку в этой строке с помощью функции IF / ЕСЛИ, например) и в условном форматировании (поставили флажок — зачеркнули или покрасили цветом строку)

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

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

Научитесь работать с 6 топовыми нейросетями на бесплатной конференции по искусственному интеллекту от Skillbox 14–16 ноября в 18:00 по московскому времени: 👉 https://epic.st/Zd9wbW

Рассмотрите, как применять ИИ:
— в программировании
— дизайне, моде, искусстве
— видеопродакшне
— маркетинге
— играх
— бизнесе

✔️ Узнаете, как нейросети делают нас эффективнее, креативнее и помогают больше зарабатывать
✔️ Рассмотрите тренды и последние новости нейросетей.
✔️ Поймёте, к какой нейросети обращаться в конкретной ситуации и как грамотно сформулировать свой запрос
✔️ Научитесь на практике работать с ChatGPT-4, Dalle-3, Midjourney, Stable Diffusion, Gen-2

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

🎁 Всех ждут подарки:
— Инструкции по доступу к нейросетям
— Возможность выиграть личную консультацию с экспертом по работе с ИИ
— Розыгрыш мерча от Skillbox
— Скидка на новый курс по нейросетям и специальные условия обучения

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

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

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

Для этого:
1 вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)

2 Засунем это число в функцию ПОСЛЕД / SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде

3 отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности.

Формула такая:

=РАБДЕНЬ(начальная дата-1;ПОСЛЕД(ЧИСТРАБДНИ(начальная дата ;конечная дата)))

На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день (кроме выходных 😉)

P.S. G.S. В Google Sheets тоже будет работать, только не забудьте нажать Ctrl+Shift+Enter, чтобы добавилась функция ArrayFormula.

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

Как разделить текст по нескольким разделителям?

Например, по косой черте и дефису, как в примере.

В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT.
А чтобы она работала с несколькими разделителями, отправим их в массив:

{"первый разделитель"; "второй"; ... }

Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем:

"/-"

А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи:

=SPLIT(A2; "/-")

или
=SPLIT(A2; "/-"; 1)

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

Хотите видеть какую-то книгу Excel всегда наверху в списке последних файлов?

На стартовом экране (Backstage) наводите курсор на нужный файл — справа появится кнопка (которая выглядит как... кнопка) "Закрепить" (Pin). Нажимайте и книга будет всегда наверху.
Точно так же можно открепить обратно.

Хотите, чтобы стартовый экран не показывался при включении Excel (или другого приложения Office)?
Заходите в Параметры — Общие — отключайте "Показывать начальный экран при запуске этого приложения"

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

Убираем выбросы формулой: например, 10% самых маленьких и 10% больших заказов.

Для этого: посчитаем (для расчета числа значений используем СЧЁТЗ / COUNTA), а сколько вообще в таблице заказов и умножим на 10% или 0,1, чтобы получить число заказов, которые нужно убрать:
СЧЁТЗ(столбец из таблицы)*10%

Отсортируем (функция СОРТ / SORT) таблицу с заказами по столбцу, на основе которого убираем выбросы — например, в нашем случае по сумме заказа в третьем столбце

СОРТ(Таблица; номер столбца)

И далее дважды применяем функцию СБРОСИТЬ / DROP — она убирает строки из начала (если второй аргумент положительный) или из конца (если аргумент отрицательный) массива/таблицы/диапазона.
Нам придется два раза — первые и последние 10%.
Здесь число строк = сколько строк нужно удалить (вычислили на первом шаге):
=СБРОСИТЬ(СБРОСИТЬ(отсортированная таблица; число строк); -число строк)

Все вместе для нашей таблицы будет выглядеть так:
=СБРОСИТЬ(СБРОСИТЬ(
СОРТ(Заказы;3);
СЧЁТЗ(Заказы[Код заказа])*10%);
-СЧЁТЗ(Заказы[Код заказа])*10%)

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

Коллеги порадовали хорошими новостями — первый тираж "Магии таблиц" ушел менее чем за полгода — и издательство будет делать новый, уже 3000 экземпляров (первый был 2500) 🔥

Из первого тиража книги еще остаются на маркетплейсах, а также в розничных магазинах (как Читай-город или Буквоед), если хотите сделать полезный подарок друзьям/коллегам — поспешите! Новый тираж в любом случае будет печататься пару месяцев и до НГ, естественно, книг не прибавится.

На сайте издательства (там же электрическая книга)
Озон
Wildberries

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

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

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

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

Как добавлять макросы в личную книгу:
/channel/lemur_excel/30

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

Сортируем данные по имени, даже если это не первое слово в ячейке

Дано: хотим сортировать в режиме реального времени таблицу по именам. Но имена у нас в столбце с фамилией и именем — на втором месте, после пробела. То есть просто сортировать по этому столбцу не получится — будет сортировка по первому слову (точнее, всему тексту, Фамилия+имя).

Выход: сортируем функцией СОРТПО / SORTBY по виртуальному столбцу с именами.

Виртуальный столбец получим функцией ТЕКСТПОСЛЕ / TEXTAFTER — будем извлекать текст после пробела. Но не для отдельной ячейки, а сразу для всего столбца ФИО.

Как водится с новыми функциями, магия доступна в Microsoft 365 / Excel Online.

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

Считаем количество ответов на форму... формулой

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

Можно поступить так:
1. выведем список уникальных ответов (функция UNIQUE)

2. для каждого ответа посчитаем, сколько раз он встречается в столбце (COUNTIF / СЧЁТЕСЛИ)

3. Объединим в одну текстовую строку через дефис или другой разделитель:
COUNTIF(диапазон;ответ)&" - "&ответ

4. Чтобы все было одной формулой, проделаем это через MAP — массивом значений для этой функции и будет список уникальных ответов. Для каждого из них мы будем считать, сколько раз он встречается, и склеивать число с текстом ответа.
Можно добавить проверку на пустоту через ISBLANK / ЕПУСТО, чтобы не выводить пустые ответы и ссылаться на открытый диапазон (если ждете новых ответов на форму).

=MAP(UNIQUE(диапазон с ответами);LAMBDA(ответ;IF(ISBLANK(ответ);"";COUNTIF(диапазон;ответ)&" - "&ответ)))

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

На горе Фернандо-По применяли мы ГРУПППО

Мощная новинка в Excel: функции, которые позволяют агрегировать данные, де-факто — строить сводные, но формулами. То есть с автоматическим обновлением. К тому же функции можно использовать как аргументы других функций (и им передавать в качестве аргументов не диапазоны, а другие функции), что еще мощнее расширяет возможности.

Новые функции: GROUPBY / ГРУПППО и PIVOTBY / СВОДПО.

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

В видео (11 мин, со звуком) обсуждаем, как вообще можно сводить данные и чем отличаются разные способы:
— в любой версии Excel: формулы (не очень гибко и без обновления в случае появления новых данных)
— в любой версии Excel: сводная таблица (очень гибко, но сводную надо обновлять вручную/макросом)
— в 2016 и новее (а также 2010-2013 с установкой надстройки): Power Query (обычно этой надстройкой данные предварительно обрабатывают и потом строят сводную, но в ней можно сразу группировать) (относительно гибко, обновлять вручную или по расписанию раз в N минут)
— пока только с бета-каналом обновлений в Microsoft 365, позже у всех подписчиков 365: новые функции (гибко и обновляется все автоматом)

Видео на Youtube: https://www.youtube.com/watch?v=1xN7Hly-oc0
(про новые функции с 6 минуты)

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

Когда ты навертел такого в формулах, что даже Microsoft уже интересно посмотреть, что же там происходит 😺

Вот такое уведомление появилось при работе над одним мини-проектом для большой компании.

До этого не встречали :) а вы?

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

Выделяем всю строку при наличии в столбце слова (буквы, текста)

Как покрасить всю строку, если в одном из столбцов встречается какое-то слово (то есть не занимает ячейку целиком, просто знаком равно = сравнивать нельзя)?

Можно функцией ПОИСК / SEARCH или НАЙТИ / FIND. Первая работает без учета регистра, вторая — с.

=НАЙТИ (что ищем; где ищем)

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

В тех случаях, когда они не находят искомое значение, возвращается ошибка. В случае условного форматирования при ошибках в формуле правило не сработает, а это то что нам нужно.

Главное в условном форматировании — не запутаться со строками и столбцами. Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в B2 и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными.

Смотрим на видео!

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

FREE WEBINAR День открытых дверей Loginom Skills 🔊

Loginom ⎼ low-code платформа, которая позволяет обрабатывать большие объемы данных, реализовывать сложную логику, сохраняя высокое быстродействие, выходящее за рамки привычных Excel и Google Sheets

Почему Loginom?

◾️Акцент на автоматизацию, включающую переиспользование наработок на других датасетах

◾️Обработка сотни миллионов строк за несколько секунд

◾️Быстрый обмен данными с помощью веб-сервисов с поддержкой REST JSON или SOAP XML

Как быстро и качественно освоить Loginom?

Представляем Loginom Skills — это курсы, воркшопы, навыки, а также библиотеки готовых компонентов от команды методистов и экспертов Loginom Company

📢23 ноября в 16:00 по московскому времени мы наглядно продемонстрируем, из чего состоят наши обучающие материалы, как их оценивают слушатели, поделимся информацией о тарифах и расскажем, как получить доступ ко всем материалам

Продолжительность ~ 1 час. Участие бесплатное. Регистрация по ссылке

Реклама. Erid LjN8KJsHL

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

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

Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить?

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

— Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.

— Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, итоговый показатель — факт — делим на базисный — план — и вычитаем единицу) и пользовательский формат:

+0%* 🔥;-0%* 👎

(смайлики выберите по вкусу; чтобы зайти в окно настройки формата, нажмите Ctrl+1)

— Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например).

P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!

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

Тем временем большая часть тиража книги "Магия таблиц" уже распродана за 4 месяца — более 2100 экземпляров из 2500, в издательстве осталось совсем чуть-чуть, и в магазинах тоже — в Лабиринте книга и вовсе кончилась, например.

Так что если планируете сделать полезные и табличные подарки коллегам/друзьям на Новый год или по другим поводам, поторопитесь!
Книгу можно заказать тут:
На сайте издательства (там же электрическая книга)
Book24
Лабиринт (потрачено)
Озон
Wildberries
Читай-город
Буквоед

За это время прибавилось отзывов! На Озоне 50 оценок с рейтингом 4.92, на WIldberries 22 оценки — 4.8.

Вот наш с Лемуром любимый отзыв из свежих 😺
Вся информация очень поверхностная, без деталей. Чисто о возможностях , примеров формул практически нет

Ну и несколько других (просто копируем из магазинов как есть):
— Настоящий справочник, очень пригодился в работе
— Отличная книга!
— Отличная полезная книга
— Книга бесценна по своему содержанию.
— уровень знания excel неплохой, знания математики забыты. люблю, когда все объясняют. для меня книга очень полезная. автору, продавцу и озону спасибо ❤️
— содержание: описан полезный функционал
— Купил книгу так как до этого проходил два курса от автора. Очень доволен, пользуюсь в том числе как справочником-шпаргалкой, если что-то подзабылось. Настоятельно рекомендую.
— Книга-мечта! Отлично дополняет курс "Магия Excel"! Полностью книгу не читала, но обращаюсь, когда нужно быстро найти функцию или освежить в памяти комбинацию клавиш для определенных действий!

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

А как быть в старых версиях Excel (до 2019 включительно), где функции ПОСЛЕД / SEQUENCE нет?

Там можно воспользоваться прогрессией (Series). Ищите инструмент на вкладке "Главная" в коллекции команд "Заполнить" (стрелка вниз).
Увы, это будет статичная история, а не динамическая, как в случае с формулами... но тоже неплохо!

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

🤔 Только 6% пользователей знают все фишки Excel

Возможно, все функции таблиц знать не обязательно, но часть из них сделает вашу жизнь проще. На курсе Skillbox «Excel для рабочих и личных задач» вы сможете оценить, насколько проще становится жизнь. Например, если вы:

🤓 Студент. В Excel можно использовать формулы, настраивать макросы.

📈 Бухгалтер или экономист. Сможете собрать и оформить шаблон авансового отчёта, добавить в него гиперссылки на договоры.

💻 Для личных задач. Научитесь использовать формулы для быстрого подсчёта расходов, а затем сгруппируете результаты в сводные таблицы и диаграммы.

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

На курс действует скидка до 60% и при покупке вы получаете ВТОРОЙ КУРС в подарок!

Переходите по ссылке, чтобы узнать подробности: https://epic.st/JbHB-

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

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

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

Эта чудо-функция возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент).

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 30.06.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 44742") — потому что вот так даты хранятся в Excel и Таблицах.

И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в окне "Формат ячеек" (Ctrl + 1).

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "ДД.ММ.ГГ")

Подробнее про пользовательские числовые форматы можно посмотреть в видео — оно на основе Google Таблиц, но все работает практически идентично.

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

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

Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец.

1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

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