Ознакомительная версия. Доступно 12 страниц из 57
все корректно, проверяйте, точно ли вы указали последний аргумент как ЛОЖЬ (ноль, 0)? Если нет, будет по умолчанию 1, ИСТИНА (интервальный, а не точный поиск). И в случае с поиском текстовых значений возникнут ошибки.
Сравнение данных, поиск данных (те же ВПР и прочие): если у вас ошибка Н/Д, может быть проблема в разных форматах данных. Например, в исходнике, который вы импортируете, артикулы текстового формата, а у вас в вашей таблице — числового. Проверить можно с помощью функций ЕТЕКСТ / ISTEXT, ЕЧИСЛО / ISNUMBER.
Кроме того, всегда есть риск банальных ошибок ввода данных — лишние пробелы, перепутанные кириллица/латиница, сокращения. Для предотвращения таких ошибок используйте проверку данных. А для отлавливания — функцию УНИК / UNIQUE (выводите список уникальных значений и смотрите, есть ли там разные варианты написания одного и того же значения).
Если вдруг ВПР или другая функция в упор не находит значение, а визуально они кажутся одинаковыми, не забывайте, что всегда можно сравнить две ячейки формулой (=A1=A2) или посмотреть, одинаковой ли они длины по количеству символов (с помощью функции ДЛСТР / LEN).
Убрать лишние пробелы (до и после текстовой строки и все, что свыше одного пробела между слов) помогает функция СЖПРОБЕЛЫ / TRIM.
Забыли протянуть формулу / изменить диапазон: по возможности используйте таблицы в Excel и ссылки на них в формулах.
В Google Таблицах можно использовать формулу массива с открытым диапазоном. Так вы настроите ее раз и навсегда, и не нужно будет беспокоиться о появлении новых столбцов. Допустим, вы пишете формулу, которая будет отправлять адресату по его имейлу из текущей строки письмо:
=HYPERLINK("mailto: "&E2&"?subject=Тема письма";"Письмо")
Вместо такой формулы сделайте ее по открытому диапазону E2:E, и она будет работать бесконечно для любого количества строк. Чтобы в пустых строках ничего не отображалось, добавьте проверку на пустоту ячейки с имейлом (с помощью ЕСЛИ / IF).
Чтобы обрабатывать весь массив, нужно всю формулу поместить внутрь функции ArrayFormula (также можно нажать Ctrl + Shift + Enter, как для ввода формулы массива в Excel, и эта функция появится автоматически):
=ArrayFormula(IF(F2:F="";""; HYPERLINK("mailto: "&E2:E&"?subject=Подключение тарифа";"Письмо")))
Формула в одной ячейке, а дальше во всем столбце ссылки появляются автоматом — только если есть имейл.
ЧТО ПОМОЖЕТ НАПИСАТЬ/РАЗОБРАТЬ/ПОЧИНИТЬ СЛОЖНУЮ ФОРМУЛУ
Принцип луковицы
Так этот подход назвал эксперт по Google Таблицам и автор книги Spice Up Your Sheet Life Бен Коллинз. Идея в следующем: вместо того чтобы с ходу прописывать сложную формулу, сначала прописываем часть, смотрим, как она работает и правильно ли вычисляет промежуточное значение, затем ссылаемся на эту формулу из другой формулы, в которой она будет выступать одним из аргументов, и, если все заработает, удаляем столбец с промежуточной формулой, а ее переносим в итоговую формулу (на то место, где мы на нее ссылались).
Иначе говоря, формируем отдельные части формулы в отдельных ячейках, ссылаясь из следующей на предыдущий этап, а потом уже соединяем в одну.
В приведенном выше примере с гиперссылкой можно сначала сформировать текстовую формулу, которая будет соединять mailto и адрес электронной почты из ячейки, и сослаться на ячейку с этой формулой из ГИПЕРССЫЛКИ. И если все заработает, уже потом скопировать первую формулу и вставить вместо ссылки на ячейку с ней.
Переход на следующую строку в формуле
В строке формул можно переходить на следующую строку с помощью Alt + Enter. Это позволяет визуально разделить отдельные фрагменты/функции, тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику).
Это может помочь, если у вас уже «многоэтажная» формула, а в ней возникает синтаксическая ошибка. Обратите внимание, что высоту строки формул можно менять: достаточно потянуть за нижнюю границу, удерживая нажатой левую кнопку мыши.
Также можно пробелами ставить отступы, если это поможет вам с восприятием формулы.
Клавиша F9
В Excel можно вычислить фрагмент формулы — ссылку на ячейку, диапазон, отдельную функцию, выражение. Это помогает понять, какое значение вычисляется на промежуточном шаге, почему возникает ошибка, и разобраться в конечном счете со сложными формулами.
F9 можно использовать, чтобы посмотреть, какой результат возвращает один из промежуточных этапов формулы, что мы получаем в ней на входе.
В таком случае важно не забыть после нажать Esc, чтобы выделенный фрагмент не остался в формуле статичным значением.
Можно вычислить значение отдельной ячейки.
Можно вычислять и значение отдельной функции.
Также можно вычислить и целое выражение (любой фрагмент формулы, который может вернуть какое-то значение): например, результат сложения нескольких значений или нескольких функций или две функции, вложенные одна в другую.
В Google Таблицах это реализовано в виде всплывающей подсказки, которую можно отключить той же клавишей F9 (или нажатием на крестик справа сверху).
В Excel всплывающие подсказки, показывающие, чему равен фрагмент формулы, появились только в 2023 году (то есть их нет в версиях вплоть до Excel 2021, только в Microsoft 365).
После нажатия F9 вычисленный фрагмент становится значением, и нужно выйти через Esc, чтобы он так и не остался фиксированным значением, а подсказка просто отображает значение выделенного фрагмента, как в Google Таблицах.
Можно посмотреть, чему равны:
• одна из функций в формуле;
• сочетание функций, выражение внутри формулы;
• что хранится в той или иной ячейке;
• какой массив данных хранится в диапазоне или вычисляется функцией/частью формулы.
Отключаются и включаются подсказки сочетанием Ctrl + Alt + P.
Но иногда превратить диапазон в значения — именно то, что нужно! F9 можно применять и так. Если выделить диапазон и нажать F9, то вместо ссылки в формуле будет массив из значений, то есть тот же самый диапазон, но уже «внутри формулы». И тогда ваша формула будет работать без вспомогательной таблицы. Если вы хотите избавиться от такой таблицы, то этот способ вам подойдет.
Файл с примером: F9 и ВПР.xlsx
Так окажется выглядеть формула после нажатия F9 — таблица в строках 11–14 уже будет не нужна.
Пошаговое вычисление
Когда в формуле применяется несколько шагов (как правило, несколько разных функций), можно использовать инструмент «Вычислить формулу» (Evaluate Formula), чтобы проследить
Ознакомительная версия. Доступно 12 страниц из 57
