Клавиша F9 позволяет вычислить выделенный фрагмент формулы (ссылку на ячейку, диапазон, функцию, выражение из нескольких функций).
Это можно использовать, чтобы посмотреть, какой результат возвращает один из промежуточных этапов формулы, что мы получаем в ней на входе. В таком случае важно не забыть после нажать Esc, чтобы выделенный фрагмент не остался в формуле статичным значением (или ошибкой, как на скриншоте)!
Но иногда превратить диапазон в значения — именно то, что нужно! F9 можно применять и так. Если выделить диапазон и нажать F9, то вместо ссылки в формуле будет массив из значений, то есть тот же самый диапазон, но уже «внутри формулы». И тогда ваша формула будет работать без вспомогательной таблицы. Если вы хотите избавиться от такой таблицы — то этот способ вам подойдет. На скриншоте ниже таблица с интервалами для определения скидки находится внутри формулы, ссылки на диапазон здесь больше нет (теоретически массив можно ввести и вручную, конечно, вообще не имея изначального диапазона, но проще будет его «вычислить», нажав F9 при выделеленной ссылке на диапазон с данными)
Выделенный фрагмент формулы в новом Excel (и Google Таблицах) В Google Таблицах такое было давно. А в Excel можно было только вычислить фрагмент формулы нажатием F9. А в 365 появилась всплывающая подсказка с текущим значением выделенного фрагмента:
А тут просто всплывающее значение. Можно посмотреть, чему равна: — одна из функций в формуле — сочетание функций, выражение внутри формулы — что хранится в той или иной ячейке — какой массив данных хранится в диапазоне или вычисляется функцией/частью формулы.
Пример. Выделяем аргумент функции ДВССЫЛ / INDIRECT — текстовую строку. Видим текст в подсказке:
А если выделить уже всю эту функцию, увидим значения в диапазоне, который находится по этому адресу (ибо ДВССЫЛ это и делает — превращает текстовую строку в настоящую ссылку на диапазон). Понятно, что все данные из столбцов B:C в подсказке не поместятся, но составить впечатление о том, те ли вообще данные там, можно):
Отключается и включается в Excel сочетанием Ctrl + Alt + P, а в Google Таблицах — клавишей F9.
Отображаем формулу как текст 1 вариант. Добавляем апостроф `. Он делает значение текстовым. Если надо временно отключить формулу, просто добавьте апостроф. Еще можно удалить знак «равно» (=) или поменять его на что-то. На скриншоте апостроф только в ячейке A2. На листе его не будет видно, но в строке формул он есть:
2 вариант. Включить режим "Показать формулы" (Вкладка "Формулы"). Formulas — Show Formulas. Этот режим де-факто превращает лист в строку формул: вы видите формулы, а не результаты вычислений; и вы видите значения без форматирования, как они есть.
3 вариант. Функция Ф.ТЕКСТ / FORMULATEXT. Она возвращает текст формулы. Может пригодиться и для визуального выделения формул с определенным содержимым через условное форматирование (об этом по ссылке), и для банального отображения текста формулы в ячейке (моноширинный шрифт в примере я применил сам, автоматически этого не произойдет :))
Прячем формулы Итак, у вас секретнейший алгоритм расчета чего-то там.
Выделяем ячейки, заходим в окно формата (Ctrl + 1) и там включаем соответствующий флажок — "Скрыть формулы".
После чего нужно защитить лист (с паролем, если вы настроены очень серьезно, хотя пароль и не обязателен).
И формулы не будет видно. Но они будут вычисляться, разумеется.
И если мы отключим защиту ячеек, влияющих на формулу (как в видео столбец F «Сумма»), то можно будет менять их значения даже при защите листа и формулы будут пересчитываться. Но мы их все равно не увидим 😸
Как отключить защиту отдельных ячеек? По умолчанию все ячейки защищаемые. Можно снять защиту для отдельных ячеек — там же, где и скрытие формул, в окне формата на вкладке «Защита».
Подсветка всех влияющих или зависимых ячеек Выделяем ячейку с формулой, нажимаем F5. В появившемся окне — «Выделить»:
А далее выбираем "влияющие" или "зависимые" ячейки:
После нажатия ОК они выделятся. Останется применить к ним заливку или другое форматирование по вкусу. Вместо F5 и "Выделить" можно пойти сюда (на вкладке ленты "Главная"):
Превращаем все формулы в значения
Если у вас работает сочетание клавиш Ctrl + Shift + V (новый Excel и Google Таблицы), то все просто: Скопировали Ctrl + C и тут же вставили Ctrl + Shift + V.
Если нет, выделяем все формулы, тащим за границу диапазона с ними правой кнопкой мыши, отводим в сторонку… и быстренько возвращаем на место! После чего кнопку мыши отпускаем и выбираем в появившемся меню «Значения».
Еще один вариант для любых версий: Ctrl + Alt + V (это окно специальной вставки) + З (Значения) + Enter.
F2: переключаем режим Если вы хотите, чтобы при нажатии стрелок на клавиатуре не появлялась/менялась ссылка на ячейку при вводе формулы, а перемещался курсор в рамках самой формулы — нажмите F2. Тогда стрелки будут перемещать курсор. Это работает и в диалоговых окнах Excel — окне условного форматирования, в диспетчере имен.
Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там «Правка» (Edit), то можно смело нажимать на стрелки ;)
Контрольное значение: отслеживаем значения ячеек в любом месте Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе — добавьте эту ячейку в окно контрольного значения.
В примере добавляем в Таблице в строке итогов сумму по всем заказанным товарам в окно контрольного значения, переходим на другой лист и меняем там цены некоторых товаров — и наблюдаем «в режиме онлайн», как меняется сумма заказа из-за изменения цен.
Новый тип ссылки — с решеткой
Так как в Excel 2021 / 2024 / 365 появились динамические массивы (то есть формулу можно ввести в одну ячейку, а результат она выкатит размером более одной ячейки, да еще и размер этого результата может меняться в будущем) и соответствующие функции (например, УНИК / UNIQUE, СОРТ / SORT), то появился и новый тип ссылки — на ячейку с такой формулой.
Ведь если функция УНИК выдает уникальные значения, то откуда мы знаем, какого размера результат будет в будущем? Ведь уникальных значений в источнике может стать меньше или больше.
Поэтому мы не можем сослаться на результат стандартно, например как на A1:A10. Вдруг потом в 11 строке тоже будет результат выдачи?
Поэтому на такие формулы нужно ссылаться с решеткой A1#.
A1# — такая ссылка будет возвращать массив значений, который вычисляется формулой, находящейся в ячейке A1.
Пересечение. Оператор @
Вряд ли вы будете использовать этот оператор в формулах в обычных диапазонах (за пределами «умных» таблиц), но можете с ним столкнуться. Произойти это может, когда вы откроете книгу в «новых» версиях (Excel 2021 / 2024 / 365) с формулами, сделанными в старых версиях.
Если в формуле есть ссылка на диапазон (именованный или обычный), то в старых версиях предполагалось по умолчанию, что берется ячейка из той же строки, в которой находится формула.
В новых версиях ссылка на диапазон — это сразу ссылка на диапазон, а не на пересечение с ним. То есть =A1:A10 — это сразу весь диапазон A1: A10, формула вернет результат размером в 10 строк. А =@A1:A10 — это пересечение с диапазоном A1: A10, то есть одно значение из той же строки, в которой находится формула.
Следующая формула в новых версиях будет возвращать диапазон из 12 значений — разницу между парами значений в столбцах C и B: =C2:C13-B2:B13
В старых версиях такая формула будет возвращать одно значение (для текущей строки).
P. S. Ну, а в «умных"таблицах (чтобы создать такую, нажмите Ctrl + T или Ctrl + L) этот оператор используется в любой версии по умолчанию, но не с диапазоном, а с названием столбца в квадратных скобках. Например, [@План] — ссылка на столбец с заголовком «План» на эту же строку (ту, где формула).
Ссылка с закреплением только одной из двух ячеек диапазона. «Нарастающий итог» Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец.
1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, «закрепить»).
2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен — так что при протягивании/копировании формулы будет меняться. =СУММ ($B$2:B2)
3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.
Для чего еще это может пригодиться? Для нумерации только видимых строк, например. Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.
Ибо она умеет обрабатывать только видимые (не скрытые) ячейки. В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе) или 3. Короткий вариант функции (3 для количества) — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк. Длинный вариант — подсчет только отфильтрованных строк, без скрытых вручную.
А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.
Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ (103;$B$2:B2)
$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.
Ну, а заодно в видео напоминание про 🔥 клавиши: Ctrl + 9 — скрыть строку Ctrl + Shift + 9 — отобразить скрытые строки
Всплывающая подсказка с аргументами функции Да, есть такая. Квадратные скобки тут означают необязательные аргументы, ну, а если будет многоточие — значит, аргументов может быть больше.
Что тут можно посоветовать? Во-первых, ее можно перемещать! Да, это может быть очевидно, но я точно знаю, что не все в курсе и используют. Добиваемся курсора со стрелками в разные стороны и перемещаем эту сущность, ведь иногда она загораживает всякое полезное.
Во-вторых, если кликнуть на аргумент функции, он будет выделен целиком. А дальше можно его скопировать (бывает, что один и тот же аргумент повторяется, например, диапазон условий в СУММЕСЛИМН / SUMIFS) или… см следующий пункт.
Быстрый переход к диапазону из формулы Выделите диапазон внутри формулы — и нажмите F5. Появится окно «Переход» (Go To), в котором уже будет этот диапазон, останется только нажать Enter на клавиатуре или OK в окне.
Возврат к активной ячейке при вводе формулы Ctrl + Backspace — это возвращение к активной ячейке. Удобно при работе с формулами, расширенным фильтром, другими окнами.
Вполне вероятно, вы уже вовсю пользуете сочетание клавиш Ctrl + Shift + стрелки. Оно позволяет выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео.
Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится сочетание — Ctrl + Backspace.
Добавить комментарий к формуле Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль. Последним и можно воспользоваться, если очень хочется добавить в формулу текст без искажения результата.
Например: =E2*15% + Ч ("Вычисляем комиссию менеджера как 15% от суммы сделки")
Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри функции текст есть, а к результату эта часть ничего не добавляет.
Еще один вариант для комментариев в формуле — функция LET. Но ее нет в Excel вплоть до версии 2019 :(
Функционал у нее шире, конечно, чем только комментарии. Про нее у меня есть отдельная статья.
Она нужна в ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз. Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. В конце вычисление с использованием этих переменных.
Если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.
В случае с «комментариями» можно задать переменную (с любым названием) и присвоить ей текстовое значение. =LET (переменная; "комментарий"; [другие переменные для вычислений]; …; вычисление).
Alt + Enter для переноса строки
В формулах можно и нужно использовать перенос строки и пробелы (только не между двумя ссылками на диапазоны, так как в таком случае пробел действует как оператор пересечения, превращая два диапазона, между которыми он введен, в ссылку на их общую часть) для более простого восприятия «многоэтажных» конструкций. На работу формулы пробелы и переносы строки не влияют.
Функция как часть ссылки на диапазон
Несколько функций Excel могут возвращать не значения, а ссылку на последнюю ячейку диапазона, когда они следуют за двоеточием после ссылки на первую ячейку: =$A$2:ФУНКЦИЯ(...)
Это функции ЕСЛИ / IF, ВЫБОР / CHOOSE, ЕСЛИМН / IFS, ДВССЫЛ / INDIRECT, СМЕЩ / OFFSET, ПЕРЕКЛЮЧ / SWITCH и ПРОСМОТРX / XLOOKUP.
Это можно использовать, например, чтобы создать динамический именованный диапазон. Диапазонам и ячейкам в Excel можно присваивать имена (Ctrl + F3 или поле "Имя слева от строки формул или вкладка "Формулы" на ленте).
Что если мы хотим создать имя, за которым будут скрываться все заполненные ячейки в столбце, начиная с A2, например? То есть чтобы одним и тем же именем (например, Компании, как в примере) назывался диапазон динамического размера — в зависимости от числа заполненных ячеек в столбце.
Для нашей задачи можно использовать ИНДЕКС — будем с помощью нее получать адрес последней заполненной ячейки в столбце A. Чтобы узнать, какая строка последняя — посчитаем, сколько заполненных ячеек в столбце A с помощью СЧЁТЗ / COUNTA.
=СЧЁТЗ($A:$A)
Значение из последней заполненной ячейки можно получить так: =ИНДЕКС($A:$A;СЧЁТЗ($A:$A))
Но мы засунем эту конструкцию после ссылки на первую ячейку диапазона, и она не будет возвращать значение из последней заполненной ячейки, а ссылку на нее: =$A$2:ИНДЕКС($A:$A;СЧЁТЗ($A:$A))
Все это остается отправить в поле "Диапазон" вновь созданного имени.
Если у вас Microsoft 365, то вместо имени с формулой можно воспользоваться новым типом ссылок или функцией УРЕЗДИАПАЗОН / TRIMRANGE. Эта функция уменьшает диапазон, отбрасывая пустые строки / столбцы — из начала, конца или с обеих сторон.
Еще появился новый тип ссылок, делающий то же самое.
Столбец A без пустых строк в начале и в конце: =A.:.A
Столбец A без пустых строк в конце: =A:.A
Пошаговое вычисление формулы
Когда в формуле применяется несколько шагов (как правило, несколько разных функций), можно использовать инструмент "Вычислить формулу" (Evaluate Formula), чтобы проследить ход вычисления по порядку:
На втором шаге эта формула будет выглядеть так. Вычислено название ("Номенклатура") искомого товара:
А через несколько шагов итоговый будет выглядеть так (найдены строка и столбец с нужным товаром и заголовком, которые выступают аргументами функции ИНДЕКС):
Заставляем функцию обрабатывать массив, даже если она этого не хочет
Некоторые функции — например, КОНМЕСЯЦА / EOMONTH (возвращающая последнюю дату месяца, отстоящего от даты на заданное число месяцев) — не хотят выдавать результат в виде массива. А возвращают в такой ситуации ошибку — как в столбце B в примере, когда мы дали функции сразу много дат, чтобы и результат получить в виде массива дат. Это всё — про Excel 2021 / 2024 / 365.
Но если добавить к ссылке на диапазон знак «+» (плюс), то магия случится! 🔥
Так не работает: =КОНМЕСЯЦА (A2:A16;1)
А так работает: =КОНМЕСЯЦА (+A2:A16;1)
Почему? Из-за плюса сначала вычисляется выражение с этим самым плюсом — и диапазон A2:A16 становится массивом значений. А с этим уже функция справляется.
Макрофункции через создание имен. Пример: оглавление
Макрофункции работали в старых листах макросов XLM. Листов этих давно в Excel нет, а макрофункции для совместимости остались. Правда, они не работают напрямую в формулах рабочего листа, но можно получить к ним доступ через имена.
Давайте рассмотрим на примере списка листов. Для этой цели воспользуемся одной из макрофункций ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK)
С аргументом «1» она возвращает массив с названиями всех листов. Второй аргумент — имя книги, но его можно пропустить, и тогда формула будет возвращать данные по активной книге.
Так как она не работает в ячейках, придется создать имя — Ctrl + F3 — придумайте имя (у нас будет «Оглавление») — и введите: =ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (1)
Теперь остается перевернуть (транспонировать) список с помощью ТРАНСП / TRANSPOSE: =ТРАНСП (Оглавление) и избавиться от имени книги, идущей в квадратных скобках. С новыми функциями можно так: =ТЕКСТПОСЛЕ (ТРАНСП (Оглавление);"]")
Со старыми формулами (до 2019 включительно) придется пошаманить. Вот так вытащим элементы из массива «Оглавление" — уже по одному, в каждой ячейке своя формула, ибо не умели раньше формулы возвращать сразу все значения, будучи введенными в одну ячейку. ИНДЕКСом мы достаем каждое очередное значение (функцией СТРОКА / ROW получаем номера строк, то есть в первой строке достаем имя первого листа, во второй — второго и т. д.). ЕСЛИОШИБКА — чтобы спрятать ошибки, протянув формулу с запасом для будущих листов, которых пока нет (и, соответственно, для которых будет ошибка, потому что не будет пока таких элементов в массиве):
Останется избавиться от названий книги — старыми формулами это можно сделать как-нибудь так: =ПРАВСИМВ (A1;ДЛСТР (A1)-НАЙТИ ("]";A1))
Считаем, сколько знаков всего в ячейке (ДЛСТР / LEN), находим положение закрывающей скобки (НАЙТИ / FIND), вычитаем из всех положение скобки, и столько символов извлекаем с конца строки (ПРАВСИМВ / RIGHT).