Как включить подсказки формул в экселе
Обновлено: 01.07.2024
Привет. Бывали ли у вас такие ситуации, когда формулу ввели, а результат не соответствует ожиданиям? Можно внимательно прочитать формулу, еще раз попробовать ее написать. Но если это что-то длинное, с кучей ЕСЛИ, ссылками на другие ячейки или даже листы - так просто ее не отладишь!
Расскажу о нескольких инструментах Эксель для отладки формул!
1. Мастер функций
Мастер функций вызывается нажатиме на значек fx слева от строки ввода формулы.
Его помощь заключается в следующем:
- подсказка по текущей функции (что делает, какие аргументы)
- разбивка ввода по аргументам
- отображение значения для каждого аргумента
- отображение результата формулы
- возможность вызова справки по этой формуле.
2. Где формулы, а где значения?
Чтобы отобразить формулы в ячейках, нажмите сочетание клавиш CTRL+` (маленький значок — это значок тупого ударения). В таком виде лист можно распечатать, и при печати будут видны формулы. Чтобы вернуться к отображению результатов в ячейках, снова нажмите CTRL+`.
Команду можно также вызвать из ленты Формулы -> Отобразить формулы (Formulas -> Show Formulas)
3. Влияющие и зависимые ячейки
Влияющие и зависимые ячейки. Листайте вправо для скриншота русского варианта --> Влияющие и зависимые ячейки. Листайте вправо для скриншота русского варианта -->При однократном нажатии на кнопку Формулы -> Влияющие ячейки (Formulas -> Trace Precedents) стрелкой будут обозначены ячейки, от которых зависит формула. При повторном - аналогично будут обозначены ячейки от которых зависят те, от которых зависит наша формула. И так далее: вот дом, который построил Джек!
Аналогично работает и функция Зависимые ячейки (Trace Dependends).
4. Пошаговое вычисление формулы
Один из самых эффективных способов - пошаговое вычисление формулы. Этот способ позволяет пошагово "разобрать" длинную формулу на кусочки, и понять где происходит ошибка. Вызвать эту функцию можно в Формулы -> Вычислить формулу (Formulas -> Evaluate Formula)
Возможно вы замечали всплывающее поле в момент редактирования формулы:
Рис. 1. Всплывающая подсказка
Аргумент функции, которую вы редактируете в данный момент, будет выделен на экране жирным шрифтом. В нашем примере – это искомое_значение.
Вы можете нажать на любой из аргументов в подсказке, чтобы выбрать текст в формуле, соответствующий этому аргументу. Например, в подсказке кликните мышью на аргумент таблица:
Рис. 2. В подсказке выбран аргумент таблица
В формуле выделяется фрагмент $B$3:$D$6. Такая техника позволяет легко изменять ссылку.[1] Кликнув на аргумент таблица, можно выбрать ячейки на листе, чтобы изменить эту ссылку ($B$5:$D$8). Это намного экономичнее, чем пытаться выделить текст с помощью курсора мыши, особенно с длинными формулами, которые ссылаются на другие листы.
Подсказка также может использоваться для быстрого изменения ссылок на абсолютные, относительные или смешанные. В подсказке кликните на аргумент, который нужно изменить, а затем нажмите клавишу F4 на клавиатуре столько раз, сколько требуется, чтобы изменить ссылку (подробнее о ссылках см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel).
Если в подсказке нажать на имя функции запустится меню справки для этой функции:
Рис. 3. Нажатие в подсказке на имени функции запускает справку
И, наконец, если подсказка вам мешает, вы можете переместить ее. Наведите указатель мыши на левую часть подсказки, пока курсор не изменится на перекрестные стрелки. Щелкните левой кнопкой мыши, удерживайте и перетащите подсказку в новое место:
Рис. 4. Перемещение подсказки на новое место
[1] Это адаптированный перевод фрагмента книги Jon Acampora 10 Excel Pro Tips.
Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " https://support.content.office.net/ru-ru/media/bafc281e-a8a0-4a19-8d5c-57ae713d20db.jpg" alt="Автозавершение формул" loading="lazy">
1. Введите знак «=» (знак равенства) и первые буквы или триггер отображения для запуска автозавершения формул.
2. По мере ввода прокручиваемый список допустимых элементов отображает наиболее близкие значения.
3. Значки представляют тип записи, например функцию или ссылку на таблицу.
4. Подробные всплывающие подсказки помогают сделать оптимальный выбор.
Управление раскрывающимся списком с помощью триггеров отображенияВ таблице ниже показано, как динамически управлять отображением элементов в раскрывающемся списке автозавершения формул.
Элементы, которые нужно отобразить
Вводимые символы
Имена функций Excel и функций, определенных пользователем
Одна или несколько начальных букв в месте ввода какой-либо функции.
Например: СУ
(Триггер отображения не отображается).
Введите аргумент, например число или ссылку на ячейку, или используйте триггер отображения, например начало букв или [ (открываемая скобка).
Например: СУММ(5;A2;[
Для каждого последующего аргумента введите сначала точку с запятой, а затем сам аргумент или другой триггер отображения.
Примечание: У следующих функций есть аргументы с перечисляемой константой, которые автоматически отображаются в списке: ЯЧЕЙКА, ОКП, ГВ, ПОИСКПОЗ, PMT, ПС, РАНГ, SUBTOTAL и ВLOOKUP.
Определенные имена и имена таблиц
Одна или несколько начальных букв в месте ввода имени.
Пример: Анна
Один или несколько перечисленных ниже элементов.
«[» (открывающая квадратная скобка) сразу после имени таблицы.
Например: ГодовойОтчет[
«;» (точка с запятой) сразу после специального элемента.
«:» (двоеточие) сразу после имени столбца.
Например: ГодовойОтчет[Продажи:
Примечание: Если известно имя ячейки из таблицы, то название таблицы является необязательным параметром. Например, следующие формулы будут иметь один и тот же смысл:
Имена подключений в функциях куба
«"» (открывающие кавычки) сразу после открывающей круглой скобки в имени функции куба.
Например: КУБЭЛЕМЕНТ("
Примечание: Отобразятся только подключения OLAP, сохраненные в текущей книге.
Текстовые строки с многомерными выражениями (MDX) в функциях куба
Один или несколько перечисленных ниже элементов.
«"» (открывающие кавычки) сразу за точкой с запятой после аргумента.
Например: КУБЭЛЕМЕНТ("ДанныеКубаПродаж";"
. (точка) сразу после закрывающей квадратной скобки.
Например: КУБЭЛЕМЕНТ("ДанныеКубаПродаж";"[Заказчики].
Например: КУБЭЛЕМЕНТ("ДанныеКубаПродаж";"[Заказчики].[Мексика].
( (открывая скобки) сразу после открываемой кавычка для текстовой строки с многоязыковой многоязыковой текстовой строкой, указывляемой на начало tuple.
Например: КУБЗНАЧЕНИЕ("ДанныеКубаПродаж";"(
«;» (точка с запятой) сразу после закрывающей квадратной скобки в текстовой строке с многомерными выражениями, указывающая на вторую часть кортежа.
Например: КУБЗНАЧЕНИЕ("ДанныеКубаПродаж";"([Заказчики].[Мексика];
Например: КУБЗНАЧЕНИЕ("ДанныеКубаПродаж";"
Чтобы ввести текстовую строку с многомерными выражениями (MDX) с использованием автозавершения формулы, требуется подключение к источнику данных OLAP.
Если название определено, оно отображается во всплывающей подсказке, что облегчает окончательный выбор.
Если текстовая строка с многомерными выражениями неоднозначна, уникальное имя компонента будет все равно введено, но его правильность необходимо проверить. Например, в следующей текстовой строке с многомерными выражениями содержатся два значения:
КУБМЕБЕР("SalesCubeData";"[Клиенты].[ Мексика]. [Hidalgo]. [Ора С. Сапоги]
Должно быть введено одно из следующих значений:
Если введенное значение не подходит, удалите его, а затем выберите другое значение.
Имена функций Microsoft SQL Server Analysis Services, такие как Children, Parent или Crossjoin, не отображаются в раскрывающемся списке, но их можно ввести вручную.
В любой момент при использовании автозавершения формулы можно ввести необходимые символы или значения для окончания формулы.
Автозавершения формул можно использовать в середине имеющейся вложенной функции или формулы. Текст непосредственно перед курсором используется для отображения значений раскрывающегося списка, а весь остальной текст после курсора остается неизменным.
Определенные имена, созданные для перечисляемых констант, например, используемых в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, и соединения функций куба не отображаются в раскрывающемся списке автозавершения, но их можно ввести вручную.
Навигация по раскрывающемуся списку автозавершения формул с помощью клавишВ таблице ниже указаны клавиши, с помощью которых можно перемещаться по раскрывающемуся списку автозавершения формул.
Перемещение позиции курсора на один знак влево
Перемещение позиции курсора на один знак вправо
Перемещение выделения на один элемент вверх
Перемещение выделения на один элемент вниз
Выбор последнего элемента
Выбор первого элемента
Перемещение на следующую страницу и выбор нового элемента
Перемещение на предыдущую страницу и выбор нового элемента
Закрытие раскрывающегося списка
ESCAPE (или щелкните другую ячейку)
Включение и выключение функции автозавершения
Ввод элемента из раскрывающегося списка с помощью триггера вставкиВажно: При вводе формулы даже после использования триггера вставки не забывайте вводить круглую закрывающую скобку для функции, прямоугольную закрывающую скобку для ссылки на таблицу или закрывающие кавычки для текстовой строки с многомерными выражениями.
Чтобы вставить выбранный элемент в формулу и установить курсор сразу после него, нажмите клавишу TAB или дважды щелкните данный элемент.
Примечание: Этот раздел не относится к Excel в Интернете.
Щелкните Файл > Параметры > Формулы.
В Excel 2007: нажмите кнопку Microsoft Office нажмите кнопку Параметры Excel ивыберите категорию Формулы.
В группе Работа с формулами установите или снимите флажок Автозавершение формул.
Совет: Вы также можете нажать клавиши ALT+СТРЕЛКА ВНИЗ.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Чаще всего под формулами в Excel подразумевают именно встроенные функции, предназначенные для выполнения расчетов, и куда реже математические формулы, имеющие уже устоявшийся вид.
В этой статье я рассмотрю обе темы, чтобы каждый пользователь нашел ответ на интересующий его вопрос.
Окно вставки функции
Некоторые юзеры боятся работать в Экселе только потому, что не понимают, как именно устроены функции и каким образом их нужно составлять, ведь для каждой есть свои аргументы и особые нюансы написания. Упрощает задачу наличие окна вставки функции, в котором все выполнено в понятном виде.
Для его вызова нажмите по кнопке с изображением функции на панели ввода данных в ячейку.
В нем используйте поиск функции, отобразите только конкретные категории или выберите подходящую из списка. При выделении функции левой кнопкой мыши на экране отображается текст о ее предназначении, что позволит не запутаться.
После выбора наступает время заняться аргументами. Для каждой функции они свои, поскольку выполняются совершенно разные задачи. На следующем скриншоте вы видите аргументы суммы, которыми являются два числа для суммирования.
После вставки функции в ячейку она отобразится в стандартном виде и все еще будет доступна для редактирования.
Используем вкладку с формулами
В Excel есть отдельная вкладка, где расположена вся библиотека формул. Вы можете использовать ее для быстрого поиска и вставки необходимой функции, а для редактирования откроется то же самое окно, о котором шла речь выше. Просто перейдите на вкладку с соответствующим названием и откройте одну из категорий для выбора функции.
Как видно, их названия тематические, что позволит не запутаться и сразу отобразить тот тип формул, который необходим. Из списка выберите подходящую и дважды кликните по ней левой кнопкой мыши, чтобы добавить в таблицу.
Приступите к стандартному редактированию через окно аргументов функции. Кстати, здесь тоже есть описания, способные помочь быстрее разобраться с принципом работы конкретного инструмента. К тому же ниже указываются доступные значения, которые можно использовать для работы с выбранной формулой.
Ручная вставка формулы в Excel
Опытные пользователи, работающие в Excel каждый день, предпочитают вручную набирать формулы, поскольку так это делать быстрее всего. Они запоминают синтаксис и каждый аргумент, что не только ускоряет процесс, но и делает его более гибким, ведь при использовании одного окна с аргументами довольно сложно расписать большую цепочку сравнений, суммирований и других математических операций.
Для начала записи выделите ячейку и обязательно поставьте знак =, после чего начните вписывать название формулы и выберите ее из списка.
Далее начните записывать аргументы, в чем помогут всплывающие подсказки. По большей части они нужны для того, чтобы не запутаться в последовательности и разделителях.
По завершении нажмите клавишу Enter, завершив тем самым создание формулы. Если все записано правильно, и программе удается рассчитать результат, он отобразится в выбранной ячейке. При возникновении ошибки вы сможете ознакомиться с ее текстом, чтобы найти решение.
Вставка математических формул
В завершение поговорим о математических формулах в Excel, так как тематика статьи подразумевает и вставку таких объектов в таблицу тоже. Доступные уравнения относятся к символам, поэтому для их поиска понадобится перейти на вкладку со вставкой и выбрать там соответствующий раздел.
Из появившегося списка найдите подходящее для вас уравнение или приступите к его ручному написанию, выбрав последний вариант.
На экране появится редактор и блок формулы. Его используйте для перемещения, а сам редактор – для того, чтобы заносить в формулу числа и редактировать ее под себя. Учитывайте, что в этом случае не работают никакие проверки, поэтому правильность написания проверять придется собственноручно.
Это были самые простые способы вставить функции и формулы в Excel. Первые три помогут создать операции, а последний пригодится математикам и тем, кто выполняет сложные расчеты при помощи таблицы и нуждается во вставке математических формул.
Работаете ли вы с электронной таблицей в Microsoft Excel 2010, где вы меняете ячейку, включенную в формулу, но результат формулы не корректируется с учетом ваших изменений? Это происходит из-за того, что параметры формул в рабочей книге были настроены для расчета вручную. Это может быть предпочтительным поведением, если вы работаете с очень большой электронной таблицей с большим количеством формул, так как в Excel могут возникнуть проблемы с производительностью, когда нужно вычислять слишком много формул одновременно.
Но для большинства небольших таблиц и многих пользователей Excel предпочтительно, чтобы формулы обновлялись автоматически при внесении изменений в соответствующие значения ячеек. К счастью, это простая настройка для вашего рабочего листа, и наше руководство ниже покажет вам, как это сделать.
Автоматическое вычисление формул в Excel 2010
Шаги в этой статье предполагают, что ваша электронная таблица Excel в настоящее время настроена на ручной расчет. Если для электронной таблицы задано ручное вычисление, формулы не будут обновляться автоматически при изменении ячейки, на которую ссылается формула. Когда включен режим ручного вычисления, вам нужно будет нажать F9 на клавиатуре, чтобы принудительно пересчитать формулы.
Обратите внимание, что есть другой способ изменить параметры расчета в Excel 2010, и этот параметр можно найти в окне параметров Excel. Если вы предпочитаете использовать этот метод, вы можете перейти к следующему разделу.
Шаг 1. Откройте файл в Microsoft Excel 2010.
Шаг 2: Нажмите «Формулы» вкладка в верхней части окна.
Шаг 3: Нажмите «Варианты расчета» кнопку справа от навигационной ленты, затем нажмите кнопку «автоматическая» вариант.
После выбора «автоматическая» В этом случае формулы в вашей электронной таблице будут автоматически обновляться на основе любых изменений, внесенных в ячейки, включенные в формулы.
Изменить параметры расчета формулы Excel 2010 в меню параметров Excel
Шаг 1. Откройте Excel 2010.
Шаг 2: Нажмите «файл» вкладка в верхнем левом углу окна.
Шаг 3: Нажмите «Параметры» в столбце с левой стороны окна. Это собирается открыть новый «Параметры Excel» окно.
Шаг 4: Нажмите «Формулы» Вкладка в левой части «Параметры Excel» окно.
Шаг 5: Нажмите на круг слева от «автоматическая» под «Расчет рабочей книги»,
Шаг 6: Нажмите «Хорошо» Кнопка внизу окна.
Ваша электронная таблица отображает фактические формулы вместо результатов этих формул? Если вы хотите изменить это поведение, чтобы просмотреть результаты формулы, эта статья покажет вам настройку, которую нужно изменить.
Читайте также: