Магия Excel

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой: =ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) ) Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем. Ее аргументы мы получаем текстовыми функциями: Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки) Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID. День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Если вы хотите, чтобы в ячейках в диапазоне можно было вводить все слова только с заглавной буквы — например, если там хранятся ФИО — можно воспользоваться проверкой данных. Формула будет выглядеть так: =СОВПАД(первая ячейка диапазона; ПРОПНАЧ(первая ячейка диапазона)) Функция СОВПАД / EXACT проверяет совпадение с учетом регистра. А ПРОПНАЧ / PROPER меняет регистр текста — делает первые буквы каждого слова заглавными. Таким образом, мы проверяем, совпадает ли текущее значение ячейки с ним же, но в "правильном" регистре. И если не совпадает, то функция СОВПАД возвращает ЛОЖЬ / FALSE — и проверка данных ругается.

Тем временем книга получила статус "Хит" на Озоне и "Бестселлер", "Хит продаж" на Литресе! А вот и первые отзывы (пока на Озоне): отличная подача материала. намного дешевле курса! Большое количество полезных фишек для ускорения работы Отлична книга, великолепная подача, все «разжевано». Можно читать как подряд, так и местами, которые нужны. Пока это самое понятное пособие по Excel, которое я читала Собрали для вас с Лемуром ссылки на основные магазины, где можно купить: На сайте издательства Book24 Лабиринт Озон Литрес (электрическая) Wildberries Если уже прочитали, напишите отзыв тоже, будем признательны 😺

Как заполнить всю ячейку каким-нибудь символом между двух текстовых фрагментов (например, названием главы и номером страницы, как в примере)? Общая логика формулы такая: Берем первый кусочек, приклеиваем (&) к нему нужный символ, который повторяем много раз с помощью функции ПОВТОР / REPT, и потом справа приклеиваем (&) второй кусочек. Сколько раз повторять символ (в нашем случае дефис)? Берем какое-нибудь число, которое будет больше, чем наши текстовые фрагменты, и из него вычитаем число символов (ДЛСТР / LEN) в наших склеиваемых текстовых фрагментах. Таким образом получится, что число символов-заполнителей везде будет разное, но общее число символов одинаковое. P.S. Магия сработает только с моноширинным шрифтом — например, с Courier New.

Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond Представьте себе, целая книга, посвященная новым динамическим массивам Excel и соответствующим функциям. Подробное введение в то, как работают новые формулы массива в Excel (например, про новый тип ссылок с решеткой) и для чего нужны новые функции, как их применять на практике. Много примеров их комбинирования для решения интересных задач. Речь про функции SORT, SORTBY, FILTER, UNIQUE, SEQUENCE и про функции для работы с массивами — RANDARRAY, TOCOL, TOROW, WRAPCOLS, WRAPROWS, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, EXPAND, TAKE и DROP, а также про текстовые функции TEXTSPLIT, TEXTBEFORE, TEXTAFTER. Тут максимум деталей и нюансов (для новичков) и много примеров применения новых функций, в том числе их комбинаций (это будет интересно и новичкам, и продолжающим) — насколько же проще стало решать многие задачи! Есть файлы с примерами. Ну а полный обзор табличных книг вместе с этой новинкой — по постоянному адресу: https://teletype.in/@renat_shagabutdinov/excellent_books

Оператор неявного пересечения @ Вряд ли вы будете использовать этот оператор в формулах в обычных диапазонах (за пределами "умных" таблиц), но можете с ним столкнуться. Произойти это может, когда вы откроете книгу в новых версиях (Excel 2021 / 365) с формулами, сделанными в старых версиях. Если в формуле есть ссылка на диапазон, то в старых версиях предполагалось по умолчанию, что берется ячейка из той же строки, в которой находится формула. В новых версиях ссылка на диапазон — это сразу ссылка на диапазон, а не на пересечение с ним. То есть =A1:A10 — это сразу весь диапазон A1:A10, формула вернет результатом размером в 10 строк. А =@A1:A10 — это пересечение с диапазоном A1:A10, то есть одно значение из той же строки, в которой находится формула. Следующая формула в новых версиях будет возвращать диапазон из 12 значений — разницу между парами значений в столбцах C и B: =C2:C13-B2:B13 В старых версиях такая формула будет возвращать одно значение (для текущей строки). P.S. Ну а в "умных"таблицах (чтобы создать такую, нажмите Ctrl + T) этот оператор используется в любой версии по умолчанию, но не с диапазоном, а с названием столбца в квадратных скобках. Например, [@План] — ссылка на столбец с заголовком "План" на эту же строку (ту, где формула).

Еще один вопрос с недавнего вебинара: Был столбец с пунктами типа 10.1, 10.2, 10.3...10.10, 10.11, формат текстовый... Поменял формат на числовой, после чего часть цифр превратилась в пятизначные типа 45430, 44438, часть в даты. Как отметили наши коллеги из МИФа, для оптимиста стакан наполовину полон, а для Excel — первое февраля. И вот тут как раз такой случай, нам нужна нумерация такого вида, которую Excel воспринимает как даты. Чтобы ввести это как текст, ставьте апостроф в начале, который превратит значение в текстовое (в ячейке он отображаться не будет — на скриншоте это третья строка): '10.1 А если нужно уже имеющиеся данные превратить в порядковые пункты, можно использовать такую формулу: =ДЕНЬ(ячейка) & "." & МЕСЯЦ(ячейка) За счет того, что мы склеиваем несколько значений в одну текстовую строку (с помощью амперсанда), результат будет текстом.

Хотите добавить в ячейку ссылку на файл (не обязательно книгу Excel, можно и на Word, и на другие файлы)? Нажимайте Ctrl + K (стандартное сочетание для вставки гиперссылки), выбирайте папку (Искать в) и далее нужный файл. В поле "Текст" можно ввести то, что вы хотите отображать в ячейке со ссылкой, иначе там просто будет название файла с расширением. Нажимаем ОК и готово! В ячейке появится ссылка, при щелчке на которую будет открываться этот файл.

Друзья, наша с Лемуром книга тем временем доехала до всех основных книжных магазинов! Внутри — все от основ и интерфейса до новых функций, появившихся в 2021-2023 годах (в том числе LAMBDA и ее вспомогательных функций) и визуализации данных. Все функции и команды на двух языках, есть информация о том, какие вещи доступны в Google Таблицах. Если вам нужны полкило скриншотов из Excel и Google Таблиц (и только одна фотография кота) и 45 миллионов байт с примерами, заказывайте: На сайте издательства (там же электрическая книга) Book24 Лабиринт Озон Литрес (электрическая)

Проводили недавно вебинар с Лемуром, и один из вопросов слушателей был такой: как фильтровать данные в сводной (или просто в диапазоне / таблице) по последним N дням месяца/квартала/года? Например, по последним трем? Временная шкала в сводной таблице такого не умеет. Здесь можно добавить к данным расчетный столбец, где формула будет выдавать одно из двух - последние три дня или нет. И потом фильтровать на основе этого столбца. Справа здесь скриншот с примером, а вот один из вариантов такой формулы: =ЕСЛИ(И(A1>=КОНМЕСЯЦА(A1;0)-2;A1<=КОНМЕСЯЦА(A1;0));"Посл три дня месяца";"Другие дни") Функция КОНМЕСЯЦА / EOMONTH выдает последнюю дату месяца. Первый аргумент — дата, а второй — отступ от нее по месяцам. То есть КОНМЕСЯЦА(A1; -1) вернет последнюю дату предыдущего месяца относительно даты в A1. А с нулем это будет последняя дата этого месяца, того, к которому относится дата в первом аргументе — без сдвига. И в формуле мы проверяем с помощью функции И / AND (проверяет одновременное выполнение всех условий), попадает ли наша дата в промежуток между датой за три дня до конца месяца и собственно концом месяца.

Как найти все формулы в диапазоне? Вариант второй, динамический — через условное форматирование и функцию ЕФОРМУЛА / ISFORMULA, которая выдает ИСТИНА / TRUE, если в ячейке есть формула. Условное форматирование — Создать правило — Использовать формулу... Conditional Formatting — New Rule — Use a formula... И вводим формулу: =ЕФОРМУЛА(первая ячейка форматируемого диапазона) И далее выбираем форматирование: как мы хотим оформлять ячейки, содержащие формулы.

Как найти все формулы в диапазоне? Вариант первый, "на один раз": "Найти и выделить" —> Формулы Find and Select —> Formulas Все ячейки с формулами в активном диапазона будут выделены. А если активна одна ячейка — то все формулы на листе.