Свои функции книги

Что это

Своя функция — это формула, которой вы дали короткое имя. Её заводят один раз в панели «Именованные диапазоны» (или просят агента), а потом пишут в любой ячейке книги, как SUM: =МАРЖА(D2;E2).

Зачем. В прайсе сто строк, и в каждой вы считаете маржу: =(D2-E2)/D2, =(D3-E3)/D3 и так сто раз. Со своей функцией:

в каждой ячейке короткое =МАРЖА(D2;E2) — читается по-русски, понятно без расшифровки;
правило записано в одном месте. Решили считать маржу иначе — поправили функцию, и пересчитались все ячейки, где она стоит;
в сотой строке не ошибиться со скобками: скобки есть только в самой функции.

Как это работает — на пальцах

У функции есть параметры — это «окошки». У МАРЖИ два окошка: «цена» и «себестоимость».
В формуле функции вы пишете, что сделать с окошками: (цена-себестоимость)/цена. Адресов ячеек тут нет — только названия окошек.
Когда вы пишете в ячейке =МАРЖА(D2;E2), в первое окошко («цена») попадает то, что стоит первым в скобках (D2), во второе («себестоимость») — второе (E2).
Порядок важен. Значения в скобках идут в том же порядке, что и параметры. Цена 100, себестоимость 70: =МАРЖА(100;70) даёт 0,3 (30 %), а =МАРЖА(70;100) — минус 0,43, потому что цена и себестоимость поменялись местами.
В скобках можно писать адрес ячейки, число или другую формулу: =МАРЖА(D2;E2), =МАРЖА(100;70), =МАРЖА(D2*1,2;E2). Пустая ячейка считается нулём.
Результат живой: поменяли цену в D2 — маржа пересчиталась сама. Этим своя функция отличается от расчёта кодом у агента: там в ячейки ложатся готовые числа, и сами они не пересчитываются.

Как пользоваться

Панель «Именованные диапазоны»

Откройте таблицу. На панели инструментов нажмите «Именованные диапазоны» — значок закладки. Если окно браузера узкое и значка не видно, он спрятан в меню «Ещё инструменты». Ещё один путь — стрелка «Список имён» у поля адреса слева от строки формул, последний пункт «Именованные диапазоны…».
Справа от листа откроется панель «Именованные диапазоны», а кнопка останется нажатой. Лист не закрывается: пока панель открыта, в нём можно выделять ячейки и набирать формулы.
В панели сверху — список «Имена» (в новой книге там написано «Пока нет имён») и кнопка «+ Добавить», под ним — форма, внизу — «Отмена» и «Сохранить». Курсор сразу стоит в поле «Имя».
Форма сразу готова завести имя для выделенной на листе области: «Диапазон1», «Диапазон2»… Если у выделенной области уже есть имя, форма откроет его.
Ширину панели можно менять, потянув за её левый край.
Закрыть панель — крестик «Закрыть» в её шапке, Escape или ещё одно нажатие на «Именованные диапазоны».
Справа открыта одна панель книги: откроете «Комментарии», «Историю версий», «Уведомления», «Агента», редактор диаграммы, «Проверку данных», «Условное форматирование» или защиту — панель имён закроется, и наоборот.

Способ 1. Завести функцию самому

Откройте панель «Именованные диапазоны». Если в форме уже открыто другое имя, нажмите «+ Добавить».
Под списком — переключатель из двух вариантов: «Диапазон» (значок закладки) и «Функция» (значок f(x)). Выберите «Функция» — появятся поля «Имя», «Параметры» и «Формула». В пустом поле бледно написан образец того, что в него вписать: «цена; себестоимость», «=(цена - себестоимость) / цена».
«Имя»: поле пустое, в нём виден образец «МАРЖА» — напишите МАРЖА.
«Параметры»: напишите цена; себестоимость. Разделять можно «;» или «,». Строка «В ячейке» под полями сразу покажет, как функцию писать в ячейке: =МАРЖА(A2; B2) — по ячейке на каждый параметр, в том же порядке.
«Формула»: напишите (цена-себестоимость)/цена. Знак «=» в начале писать не обязательно — при сохранении он допишется сам.
Проверьте функцию до сохранения. Под строкой «В ячейке» — «Проверка»: по полю на каждый параметр, подписанному его именем. Впишите в «цена» 100, в «себестоимость» — 70, и в «Результате» появится 0,3. Пока заполнены не все поля или формула не дописана, там стоит тире. Меняете формулу или значения — результат меняется сразу.
Нажмите «Сохранить». Панель останется открытой, а в списке «Имена» появится выбранная строка со значком f(x): «МАРЖА (цена; себестоимость)». Внизу панели появится корзина «Удалить имя».
Встаньте в ячейку F2 — панель закрывать не нужно, — наберите =МАРЖА(D2;E2) и нажмите Enter.
Чтобы посчитать все строки, потяните квадратик в правом нижнем углу F2 вниз: в F3 окажется =МАРЖА(D3;E3), в F4 — =МАРЖА(D4;E4) и дальше.
Маржа получается долей: 0,3 — это 30 %. Чтобы видеть проценты, выделите колонку F и выберите «Формат чисел» (кнопка «123») → «Процент».

Второй пример: число внутри функции (НДС). Нажмите «+ Добавить» и повторите шаги 2–7 с другими значениями:

«Имя» — НДС, «Параметры» — сумма, «Формула» — сумма*0,2.
«В ячейке» покажет =НДС(A2); в «Проверке» впишите 100 в «сумма» — «Результат» 20.
В ячейке: =НДС(D2). При сумме 100 получится 20.
Дробные числа в формуле пишутся через запятую (0,2), значения в скобках при вызове разделяются «;». Всё как в обычных формулах в русском интерфейсе.
Изменилась ставка — щёлкните НДС в списке, поменяйте 0,2 на новое число и нажмите «Сохранить». Все ячейки с =НДС(…) пересчитаются.

Дать имя диапазону

Выделите на листе область, например A2:A100 с ценами, и откройте панель. В переключателе выбран «Диапазон», в поле «Диапазон» — адрес выделения.
«Имя»: вместо «Диапазон1» напишите Цены. Строка «В ячейке» покажет =SUM(Цены).
Нужна другая область — щёлкните поле «Диапазон» и обведите ячейки на листе мышью: адрес впишется сам. Пока поле ждёт выделение, вокруг него цветная рамка, и обводить можно сколько угодно раз. Поле перестаёт слушать лист, когда вы переходите в другое поле панели или нажимаете в нём Escape или Enter. Enter заодно сохраняет имя; Escape в этом поле только перестаёт слушать лист, второй Escape закрывает панель. Адрес можно и вписать: A2:A100.
Нажмите «Сохранить» — в списке появится строка со значком закладки: «Цены · A2:A100».
Теперь, когда выделена ровно эта область, в поле адреса слева от строки формул стоит «Цены». Набрать в поле цены и нажать Enter или выбрать «Цены» в «Списке имён» у поля — область выделится. Свои функции в этом списке не показываются.

Способ 2. Попросить агента

Нажмите «Агент» в шапке таблицы.
Напишите обычными словами, например: «заведи функцию МАРЖА с параметрами цена и себестоимость: (цена − себестоимость) / цена, и посчитай маржу в колонке F».
В ответе агент напишет «определил функцию МАРЖА(цена; себестоимость)» и заполнит колонку F. Если функция с таким именем уже была, он заменит её формулу и напишет «переопределил функцию …».
Функция появится в списке панели «Именованные диапазоны» и в списке «f(x)», дальше с ней можно работать руками.
Убрать функцию тоже можно словами: «убери функцию МАРЖА».
Не понравилась правка — откройте «Историю версий», нажмите на отметку «До правки агента», затем «Перейти к версии» и «Перейти».

Способ 3. Вставить готовую функцию из списка

Встаньте в ячейку.
Нажмите «f(x)» слева от строки формул.
Наверху — группа «Свои функции». Наведите указатель на МАРЖА и подождите полсекунды: появится её формула.
Щёлкните МАРЖА — в ячейке появится =МАРЖА(. Допишите D2;E2) и нажмите Enter.
Можно искать: наберите «мар» в поле «Поиск функции» — своя функция найдётся вместе со встроенными.
Ещё быстрее — начните набирать имя в ячейке, например =МАРЖ: в подсказках будет «МАРЖА(цена; себестоимость)», Tab или Enter вставит её со скобкой.

Изменить, переименовать, удалить

Изменить: щёлкните имя в списке «Имена» — оно откроется в форме, а область диапазона выделится на листе. Курсор остаётся в списке, так что можно сразу перейти к следующему имени. У функции поправьте «Параметры» или «Формулу» — «В ячейке» и «Проверка» обновятся сразу, поля «Проверки» при открытии пустые. Нажмите «Сохранить». Пересчитаются все ячейки с этой функцией.
«Отмена» сбрасывает несохранённые правки: форма возвращается к имени выделенной на листе области, а если у области имени нет — к новому имени для неё. Панель остаётся открытой.
Открытая снова функция показана в поле «Формула» так же, как формулы в ячейках: дробь через запятую (0,2), встроенные функции — английскими именами (ОКРУГЛ станет ROUND).
Переименовать: поменяйте «Имя» и нажмите «Сохранить». Формулы в ячейках сами перепишутся на новое имя.
Удалить: выберите имя и нажмите корзину «Удалить имя». Форма начнётся с нового имени, панель останется открытой. В ячейках, где стояла функция, появится #NAME?.
Строка списка «Имена»: значок (закладка — диапазон, f(x) — функция), имя, рядом бледнее — адрес диапазона («· A1:B10») или параметры функции («(цена; себестоимость)»).
Имена с других листов видны в списке с названием листа после «·», но выбрать их нельзя: откройте лист, где имя создали.
Перешли на другой лист при открытой панели — несохранённые правки в форме пропадают, она начинается заново с выделения на этом листе.

Полезно знать

Функция работает на всех листах книги.
Большие и маленькие буквы не важны: =маржа(D2;E2) — то же, что =МАРЖА(D2;E2).
Функция без параметров тоже бывает: «Имя» — КУРС, «Параметры» — пусто, «Формула» — 90. Вызывается со скобками: =КУРС().
В формуле функции можно сослаться на ячейку, например =сумма*$B$1, если ставка лежит в B1. Такая ссылка всегда ведёт на лист, где функцию создали, даже если функцию вызвали с другого листа.
Функция может вызывать другую свою функцию или саму себя.

Ограничения

Имя: буквы (русские тоже), цифры, «» и «.», без пробелов; начинается с буквы или «»; до 255 знаков.
Нельзя назвать функцию как адрес ячейки (A1, D2), как ИСТИНА, ЛОЖЬ, TRUE, FALSE и как встроенную функцию (SUM, СУММ, ОКРУГЛ).
Имя одно на всю книгу: если так уже назван диапазон или функция на любом листе, второе такое имя не завести.
На листе — до 500 имён, диапазоны и функции вместе. У функции — до 253 параметров, формула — до 8 192 знаков.
Функции, вызывающие друг друга или себя, — до 128 уровней вложенности.
Пределы вычислений те же, что у обычных формул (раздел «Ограничения» в документе «Формулы и функции»).
«Проверка» — пробный расчёт: в таблицу ничего не пишется. Числа в её полях пишутся, как в формулах вашего языка (в русском интерфейсе 0,2); не число — считается текстом.
При скачивании в XLSX свои функции сохраняются и работают в Excel 365. При загрузке XLSX с такими функциями они появляются в книге.
Заводить и менять функции может тот, кто правит таблицу. В таблице только для просмотра нет ни кнопки «Именованные диапазоны», ни списка «f(x)», но формулы со своими функциями считаются.
На сводном листе и в просмотре версии нет ни кнопки, ни панели «Именованные диапазоны»: открытая панель закрывается и сама не возвращается.
На телефоне панель встаёт под листом во всю ширину и занимает до 45 % высоты экрана, список «Имена» в ней — до 20 %; список и поля прокручиваются вместе, «Сохранить» всегда виден. Обводить ячейки для поля «Диапазон» на телефоне неудобно — впишите адрес с клавиатуры, например A2:A100.

Если что-то пошло не так

Что видно на экране	Почему	Что сделать
«Так называется встроенная функция»	Имя занято встроенной функцией (SUM, СУММ)	Выбрать другое имя, например МОЯ_СУММА
«Такое имя уже есть»	Так уже назван диапазон или функция в этой книге, на любом листе	Выбрать другое имя или открыть и поправить существующее
«Проверьте имя»	В имени пробел или лишний знак, оно похоже на адрес ячейки (A1) или это ИСТИНА/ЛОЖЬ	Писать слитно: ЦЕНА_С_НДС
«Проверьте формулу»	Поле «Формула» пустое или в нём не закрыта скобка или кавычка	Дописать формулу, проверить скобки и кавычки
«Проверьте имя и параметры»	Параметры повторяются (`цена; Цена`) или не годятся как имена (с пробелом, вида A1)	Дать параметрам разные слитные имена
«Проверьте имя и диапазон»	На листе уже 500 имён	Удалить ненужные имена
«Проверьте диапазон»	Адрес в поле «Диапазон» не разобрался	Обвести ячейки на листе или вписать адрес вида A1:B10
Адрес в поле «Диапазон» не меняется, когда выделяете ячейки	Поле перестало слушать лист: вы перешли в другое поле панели или нажали в нём Escape или Enter	Щёлкнуть поле «Диапазон» ещё раз и обвести ячейки
Панель имён закрылась сама	Открылась другая панель книги («Комментарии», «История версий», «Уведомления», «Агент», редактор диаграммы, «Проверка данных», «Условное форматирование», защита), вы перешли на сводный лист или открыли версию	Нажать «Именованные диапазоны» ещё раз; со сводного листа — сначала вернуться на обычный
`#VALUE!` в ячейке	В скобках больше или меньше значений, чем параметров, или текст вместо числа	Проверить, сколько значений и в каком порядке
`#NAME?` в ячейке	Опечатка в имени, функцию удалили, или функция без параметров вызвана без скобок (`=КУРС`)	Проверить имя в списке «f(x)»; писать со скобками: `=КУРС()`
`#NAME?` при `=МАРЖА(D2,E2)`	В русском интерфейсе значения в скобках разделяются «;», а не запятой	Написать `=МАРЖА(D2;E2)`
`#DIV/0!` в МАРЖЕ	Цена равна нулю или ячейка цены пустая	Заполнить цену
Маржа с минусом	Перепутан порядок значений в скобках	Первым ставить цену, вторым — себестоимость
Тире в «Результате»	Заполнены не все поля «Проверки», пусто «Имя» или «Формула», или формула не разбирается	Заполнить все поля, проверить скобки и кавычки
`#DIV/0!`, `#NAME?`, `#VALUE!` в «Результате»	Формула на этих значениях даёт ошибку: деление на ноль, неизвестное имя, текст вместо числа	Проверить формулу и пробные значения