Магия Excel

Меняем регистр в Word: Shift + F3 Лемур уверен: многие пользователи Ворда знают это сочетание клавиш, но вряд ли все. Так что стоит об этом напомнить! Итак, Shift + F3 меняет регистр слова (на котором курсор) или выделенного фрагмента. Верхний-Нижний-Каждое С Заглавной

Меняем регистр в Excel Здесь можно использовать функции: СТРОЧН / LOWER - нижний ПРОПИСН / UPPER - ВЕРХНИЙ ПРОПНАЧ / PROPER - Каждое С Заглавной Жаль, нет функции для того, чтобы только первая буква всего текста была заглавной. Это можно исправить формулой из нескольких функций. Нужно соединить первую букву, сделав ее заглавной, и все остальные, сделав их строчными: =ПРОПИСН(ЛЕВСИМВ(текст))&СТРОЧН(ПРАВСИМВ(текст;ДЛСТР(текст)-1)) =UPPER(LEFT(текст))&LOWER(RIGHT(текст;LEN(текст)-1)) Извлекаем первую букву (ЛЕВСИМВ), делаем ее заглавной (ПРОПИСН), прикрепляем (&) к этому все буквы справа (ПРАВСИМВ), кроме первой (длина всего текста - ДЛСТР - за вычетом единицы) и делаем строчными (СТРОЧН).

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

Сыграть на Alt'е - доступ к командам на ленте с помощью клавиатуры Хотя коты любят мышек, но колдовать в Excel быстрее получается при использовании клавиатуры - а освободившееся время можно посвятить охоте. Лемур напоминает: нажатие Alt позволяет перемещаться по вкладкам и командам на ленте с помощью клавиш (указанных на вкладках и на командах) и стрелок (стрелками право-влево по вкладкам, вниз - чтобы зайти на вкладку, и Enter для выбора команды). Alt - идем на нужную вкладку - выбираем нужную команду. А еще у команд на панели быстрого доступа тоже есть обозначения. А значит, это простой способ создать свое сочетание клавиш для абсолютно любой команды Excel, в том числе той, которой нет на ленте. Подробнее о том, как их добавлять на панель быстрого доступа, мы писали тут: https://t.me/google_sheets/1021 А когда команда на ней, остается нажать Alt + нужная цифра. P.S. Магия Alt'а не работает на Маках 😿

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. В Excel при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, но не обращайте на это внимания — она работает во всех версиях. И в Google Таблицах тоже! =РАЗНДАТ(дата_начала; дата_окончания; единица измерения) Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY. Единица измерения задается в кавычках. Есть следующие возможные варианты: "d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание); "m" — число полных месяцев в периоде; "y" — число полных лет в периоде; "md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней); "ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев); "yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Давайте поговорим про даты в Excel (и в Google Таблицах основные принципы такие же). Сегодня вот 44868 день - по летоисчислению Excel. Потому что даты в Excel - это просто числа. Что обеспечивает возможность расчетов с ними. Одна единица - это один день. А значит, чтобы получить дату через неделю, достаточно прибавить 7. А если прибавить 0,5 ? Тогда мы получим дату со временем. Логично! Если единица - это день, то дробная часть - это время, часть дня. 0,5 - это полдень, 12:00:00. На практике это значит: - Если вы видите вместо дат числа (в районе 40-с-чем-то-тысяч, как правило) - просто поменяйте формат на "Дату". Со значениями все в порядке, вопрос форматирования (внешнего вида). - Можно вычесть из даты число или прибавить - чтобы получить дату на соответствующее количество дней раньше или позже. - Можно вычесть из одной даты другую - тогда мы получим число (количество дней между днями). - Любое число (ну, кроме отрицательного) может стать датой, если поменять у него формат. Тут снова поможет изменение формата на "Числовой". В Excel работают даты с 1 января 1900 года (на Mac с 1 января 1904 - но даты конвертируются при этом при открытии книг в разных системах). Если захотите составить табличку с историей чемпионатов мира по футболу - все получится. Но если вы занимаетесь историей в целом (или генеалогией) - видимо, будет маловато. Вы можете вводить более ранние даты, но с ними не будет работать магия (функции, которые предназначены для работы с датами, не будут выдавать правильный результат; проводить вычисления тоже не получится). Вводить дату можно в разных форматах. Даже как число, а потом форматировать (если вдруг вы достигли просветления в Excel и помните, что 43132 - это 2 февраля 2018 года). Но на практике речь о разных "нормальных" форматах: ДД.ММ.ГГГГ (например, 01.06.2022) ДД/ММ/ГГГГ (например, 01/06/2022) ГГГГ-ММ-ДД (например, 2022-06-01) ГГГГ/ММ/ДД (например, 2022/06/01) ДД название месяца ГГ(ГГ) (например, 1 июнь 22) ДД короткое обозначение месяца ГГ(ГГ) (например, 1 июн 2022) Используете в формуле дату? Если она в ячейке - просто ссылайтесь на ячейку. Если хотите указать дату как константу прямо в формуле - возьмите ее в кавычки.

Сочетания клавиш: выделяем таблицу "до упора" и возвращаемся к активной ячейке. Думаю, многие из вас знают одно из любимых Лемуром сочетаний клавиш Ctrl + Shift + стрелки (⌘ + ⇧ + стрелки). Оно позволяет (если ловкости лап хватит все это нажать одновременно) выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео. Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится чуть менее часто используемое (ну, как нам кажется) сочетание — Ctrl + Backspace (⌃ + Delete).

Так, а что делать без подписки Microsoft 365, если у вас одна из коробочных версий? Шаманить со старыми текстовыми функциями - это первый вариант. Можно найти положение некой "зацепки", например, пробела - с помощью функции НАЙТИ / FIND. После этого определить, сколько символов нужно извлечь справа (это функция ПРАВСИМВ / RIGHT) из исходного текста - столько, сколько в нем есть (это вычисляется функцией ДЛСТР / LEN), минус положение пробела. =ПРАВСИМВ(текст;ДЛСТР(текст)-НАЙТИ("символ-зацепка";текст)) Второй вариант - использовать мгновенное заполнение, о котором было чуть выше. Ввести одно-два названия в соседнем столбце (без ненужного слова "Корм" или других ненужных частей) и нажать Ctrl+E.

ТЕКСТПОСЛЕ / TEXTAFTER - извлекать текст после какого-то знака/слова стало проще Как же хорошо, когда появляются новые функции в Excel. Жаль только, что это счастье доступно подписчикам Microsoft 365, а в старых версиях такой функции нет. Итак, берем ячейку с текстом (первый аргумент), указываем, после чего нужно извлечь текст (например, здесь после общего слова "корм" и пробела после него). Регистр учитывается. Функция ТЕКСТДО / TEXTBEFORE тоже появилась.

Мгновенное заполнение - один из самых простых и полезных инструментов Excel. Поможет: - извлечь из текста фрагмент (например, имя из ФИО) - переделать текст в другой вид - например, вместо ФИО - инициалы плюс полная фамилия - получить текст из нескольких столбцов (если у вас есть столбцы с датой и фамилией, например, а вам надо склеить это в одну фразу "День рождения такого-то человека такого-то числа.такого-то месяца" - Поменять регистр текста Задаем Excel 1-2 образца того, что надо получить - в пустом столбце. И либо ждем, что Excel сам предложит заполнить (как в начале видео) и нажимаем Enter (или щелкаем мышкой на серые значения), либо вводим одно значение и нажимаем Ctrl+E. Важно: Мгновенное заполнение анализирует всю строку, всесмежные столбцы (то есть нужно вводить данные в любом соседнем столбце с данными, без перерыва в виде пустого столбца) И магия работает в Excel 2013 и более новых версиях.