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

Вот такой вопрос был от одного из участников корпоративного обучения.
Задачка решается в общем виде так:
Проверяем функцией ЕСЛИ / IF, какие данные у нас — год или дата, и в зависимости от этого просто вычитаем из сегодняшнего года тот, что в ячейке, либо считаем разницу между датой в ячейке и сегодняшней датой.

Как выяснить, что в ячейке?
Можно по числу цифр — функция ДЛСТР / LEN — если их 5, то дата, если 4, то год. Но тогда получится, что 9999 будет считаться годом. А мы помним, что любая дата в Excel — это число (число дней с 1 января 1900). Так что 9999 — это 17 мая 1927, и если у вас в данных могут быть такие даты рождения, то лучше второй способ).

Можно просто по числу — если оно больше 2100, то это дата. Тут порог надежнее: 2100 — это 30 сентября 1905, вряд ли у вас будут такие даты.

Итак, задачу решили такой формулой:

=ЕСЛИ(ячейка>2100;РАЗНДАТ(ячейка;СЕГОДНЯ();"y");ГОД(СЕГОДНЯ())-ячейка)

Проверяем значение в ячейке и если оно больше 2100, то с помощью РАЗНДАТ / DATEDIF находим разницу в годах (

"y"

—третий аргумент — полные годы) между датой и сегодняшним числом.
Если меньше — значит, это год. Вычитаем из текущего года — который вычисляем с помощью функции ГОД / YEAR от текущей даты — год в ячейке.

Сравнение списков

Небольшое видео (со звуком) для новичков.

Сравниваем списки двумя способами:
— через условное форматирование (визуально)
— формулами (с помощью функции СЧЁТЕСЛИ / COUNTIF)

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

Заходит как-то в бар (Formula Bar) курьер (а точнее, какой-то другой моноширинный шрифт)...

Небольшое, но приятное обновление в Excel: теперь в строке формул моноширинный шрифт!

Такие шрифты в основном используются в программировании; название намекает, что все символы одной ширины, что упрощает выравнивание, чтение кода. А чем длинные формулы в Excel не код?

Обновление появилось у тех, кто подписан на бета-канал обновлений и потихоньку будет выкатываться на всех пользователей, получающих обновления.

Заодно Лемур хочет вам напомнить, что с помощью Alt+Enter можно вставлять в формуле перенос строки и таким образом делать сложные формулы читабельнее.

Официальная новость тут.

А вот еще одна новинка, но несопоставимого масштаба... код Python будет работать прямо в Excel!

Пока все только начинается — с бета-каналом обновлений группа Python не появилась сама, ее можно добавить как на скриншоте — через параметры ленты инструментов. Но все равно ничего еще не работает.

Работать будет прямо в ячейках — можно будет начинать ввод с =PY в формуле или через ленту инструментов.
А вычисления будут производиться в облаке. То есть будет нужен доступ к сети.

Для ссылок на объекты (ячейки и диапазоны, данные в "умных" таблицах) Excel будет использоваться функция Python xl().

Ссылки:
Официальная новость
Общие сведения о Python в Excel
Начало работы с Python в Excel
Python in Excel (на странице дистрибутива Anaconda)

Формулы Excel для новичков: разбираемся с разными типами ссылок (относительные, абсолютные и имена)

Вашему вниманию небольшой фрагмент из книги "Магия таблиц" для тех, кто только начинает работать с формулами и хочет разобраться наконец с долларами и ссылками на ячейки в Excel (или Google Таблицах).

Объединяем умные таблицы в одну: формулы и Power Query

В видео разбираем такую задачу: собрать данные из нескольких умных таблиц.

Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР / FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки.

Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Функция ISOMITTED / ПРОПУЩЕНО: добавляем к пользовательским функциям необязательные аргументы

Вашему вниманию кусочек видео из будущего нового модуля курса "Магия Excel", посвященного функции LAMBDA.
LAMBDA позволяет создавать собственные функции. Синтаксис у нее такой:

=LAMBDA([переменная]; … ; [переменная]; формула)

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

=LAMBDA(план ; факт ; факт / план - 1)

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

=LAMBDA(план ; факт ; факт / план - 1)(B2;C2)

Но в целом все это затевается ради того, чтобы использовать новую функцию под ее именем уже без всяких лямбд. Для этого нужно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции — например, “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую — можно скопировать создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции).

И в функциях можно даже создавать необязательные аргументы — для этого и нужна ISOMITTED / ПРОПУЩЕНО — она возвращает ИСТИНА / TRUE, когда аргумент пропущен. В видео пример создания пользовательской функции с необязательным аргументом.

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

Для этого нужно либо добавлять столбец в исходных данных, где вычислять нужное произведение, либо построить сводную на основе модели данных (если в вашей версии она, то есть Power Pivot, есть) и использовать меры. В мерах можно использовать функции DAX, в частности, SUMX, которая вычисляет выражение построчно и только потом суммирует — смотрим пример в видео!

Вставили текстовые значения откуда-то и хотите быстро объединить?

Можно использовать команду "Выровнять" (Justify).

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

После этого выделяйте ячейки и выбирайте команду "Выровнять" на вкладке "Главная" в коллекции "Заполнить".
Home — Fill — Justify

И вжух!

Код макроса для удаления пустых листов

Макрос: удаляем пустые листы

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

Код макроса — выше в файле формата .bas — для импорта просто зайдите в редактор VBA (Alt + F11) и нажмите там Ctrl + M для импорта.

Если личной книги макросов еще нет, включите запись макроса, выберите сохранение в личную книгу макросов (в видео это есть) и сразу остановите запись. Тогда будет создана личная книга макросов PERSONAL.XLSB. Макросы, которые лежат в ней, доступны вам в любых книгах Excel.

Друзья, наша с Лемуром книга тем временем стала №1 в жанре "Руководства по пользованию программами" в Лабиринте — в очень достойной компании😺 (особое внимание в этом топе обращаем на "Библию пользователя", хоть она и по 2019 Excel, книгу по визуализации Дика Куслейки и, конечно, книгу про сводные Билла Джелена — очень полезные вещи — про эти и другие книги можете почитать в обзоре)

Если уже прочитали, пожалуйста, поставьте оценку / оставьте отзыв в любом из магазинов, мы будем очень благодарны!

А купить можно тут:
На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая)
Wildberries
И в оффлайне — например, в Библио-Глобусе или в регионах в Читай-городе, Буквоеде и других магазинах

Калькулятор в Excel... Ну, вообще-то строка формул (точнее, сами формулы, в любой ячейке) — вполне себе калькулятор, но если вы знаете толк в извращениях, вам нужны кнопочки M+, M-, CE и вы хотите вызывать калькулятор WIndows из Excel — почему нет 😺
(шутки шутками, но кнопок там намного больше, потому что есть разные режимы — инженерный, графики, вычисления с датами, преобразования)

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

Options — Quick Access Toolbar — All Commands — Calculator — Add.

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом)

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

=СОРТ(таблица; номер столбца, по которому сортируем)

Если нужно по убыванию, то задаем третий аргумент, равный -1.

Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки.
=ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1)

В общем виде ВЫБОРСТРОК имеет такой синтаксис:
=ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...)

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

Мгновенное заполнение — один из самых простых и полезных инструментов Excel

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

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

Курсы по Excel и Google Драйву

Друзья, несколько новостей по моим табличным курсам в МИФе

Во-первых, добавил новые видео и модули в эти курсы:
- В оба курса — модуль про функцию LAMBDA и вспомогательные функции BYROW/ BYCOL, SCAN и REDUCE, MAP и ISOMITTED (последняя в Excel). В случае с Гугл Драйвом — отдельное видео про именованные функции в Google Таблицах.
- Видео про ПРОСМОТРX / XLOOKUP в Google Таблицах — добавлено некоторое время назад, благо функция там появилась вскоре после Excel. В курсе про Excel эта функция и так, разумеется, была.
- Большое видео про Power Pivot (модель данных) в Магии Excel будет скоро.

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

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

В Магии Excel 60+ уроков, в Гугл Драйве 90+.

Урокам можно ставить оценки. За все время у Гугл Драйва 600 оценок (из них 7 четверок и 2 тройки), у Магии Excel 245 оценок (тоже почти все пятерки: только 4 четверки, ни одной тройки).

Коллеги подкинули промокод на скидку, ловите:
LEMURY
35% до 5 октября.

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

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

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

Это сработает и не только для формул, но и для последовательности чисел или дат (допустим, у вас 1 и 2 в первых двух ячейках — двойной щелчок продолжит ряд, как если бы вы использовали инструмент "Прогрессия" или тянули руками за уголок ячейки).

До Excel 2010 магия ломалась на пустых ячейках в соседнем столбце, но потом починили: если даже есть пропуски в столбце слева, формула протянется до конца.

Какие еще варианты?

Можно использовать Таблицы (Tables) — Ctrl+T или Ctrl+L и вперед — формулы в Таблицах автоматически копируются на все строки.

Еще можно использовать сочетание Ctrl + D для заполнения вниз. Но для этого придется сначала выделить все ячейки в столбце. То есть пойти в конец диапазона (Ctrl + End), потом выделить столбец до первой ячейки (Ctrl + Shift + ↑). Кстати, Ctrl + R — это заполнение вправо, тоже может пригодиться.

P.S. Google Таблицы сами предлагают протянуть формулу в таких ситуациях — просто можно нажать галочку или Ctrl + Enter. Двойной щелчок и Ctrl + D / Ctrl + R там тоже работают.

Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные».

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

Если мы хотим идти от результата (допустим, маржинальная прибыль = 750 000) и понять, каким должен быть входящий параметр (один из — допустим, себестоимость при прочих равных) для желаемого результата, нам нужен подбор параметра.

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

1. Чтобы вычислить, сколько символов нужно извлечь, смотрим на число символов в тексте (функция ДЛСТР / LEN) и вычитаем N. Так получим число знаков, которые нужно извлечь.

2. Ну а чтобы их извлечь, используем ПРАВСИМВ / RIGHT — эта функция извлекает из текста (первый аргумент) заданное во втором аргументе число символов. Если нужно было бы вырезать с начала — то ЛЕВСИМВ / LEFT.

Получается:
=ПРАВСИМВ(текст; ДЛСТР(текст) — N)

Альтернатива без формул — мгновенное заполнение. Вводим в первой строке то, что нужно извлечь — Enter — Ctrl+E — вуаля!

Горячие клавиши для быстрого перемещения и выделения в Excel🔥

Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист

Ctrl + Backspace — возвращаемся к активной ячейке

Ctrl + A — выделяем всю текущую область (диапазон)

Shift + пробел — выделяем всю строку (если активна "умная таблица" — то в пределах таблицы, иначе — в пределах всего листа)
Ctrl + пробел — выделяем весь столбец

Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона.

Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными)

P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

Есть клавиши, которые в Excel (да и не только) выполняют одну и ту же задачу в разном контексте. За счет этого их проще запоминать.

Shift позволяет выделять сразу несколько объектов/символов от активного до того, на который щелкнете.

Это работает во многих ситуациях:
— При выделении диапазонов — щелкните на любую ячейку с зажатой Shift и выделится весь диапазон от активной до той, на которую щелкнули.
— Группируем листы в книге Excel — с Ctrl можно выделять по одному, а вот с Shift'ом — сразу от текущего до любого (нужно кликнуть на последний группируемый лист с зажатой клавишей Shift)
— В срезах можно выделять сразу несколько элементов с Shift'ом
— Фрагмент формулы при ее редактировании
— И не только в Excel — в текстовых редакторах и браузере можно выделять текст, в Проводнике — файлы и папки