Функция просмотр в excel примеры: ПРОСМОТР (функция ПРОСМОТР) — Служба поддержки Майкрософт

Содержание

Функция ПРОСМОТРX — наследник ВПР

26190
21.01.2020
Скачать пример


В мае 2019 года руководитель команды разработчиков Microsoft Excel Joe McDaid анонсировал выход новой функции, которая должна прийти на замену легендарной ВПР (VLOOKUP). Новая функция получила сочное английское название XLOOKUP и не очень внятное русское ПРОСМОТРX (причем последняя буква тут именно английская «икс», а не русская «ха» — забавно).


Полгода Microsoft тренировалась на кошках тестировала эту функцию на своих сотрудниках и добровольцах-инсайдерах и, наконец, в январе 2020 года было объявлено, что XLOOKUP готова к использованию и будет в ближайшее время разослана с обновлениями всем подписчикам Office 365.


Давайте разберёмся, в чем её преимущества перед классической ВПР (VLOOKUP), и как она может нам помочь в повседневной работе с данными в Microsoft Excel.

Старый добрый ВПР


Предположим, перед нами стоит задача найти в прайс-листе цену, например, для гречки. При помощи привычно функции ВПР (VLOOKUP) это решалось бы примерно так:



На всякий случай, напомню:

  • Первый аргумент здесь — искомое значение («гречка» из h5).

  • Второй — область поиска, причем обязательно начиная со столбца, где хранятся искомые данные, т.е. с товара, а не с артикула.

  • Третий — порядковый номер столбца в таблице, из которого мы хотим извлечь нужное нам значение (цена в четвертом столбце).

  • Последний аргумент отвечает за режим поиска: 0 — точный поиск, 1 — поиск ближайшего наименьшего значения (для чисел). Причем 0 не подразумевается по умолчанию — нужно вводить его явно.


Привычно, знакомо и делается многими на автомате, не приходя в сознание. ОК.


Теперь посмотрим как то же самое можно вычислить с помощью новой функции ПРОСМОТРX (XLOOKUP).

Синтаксис ПРОСМОТРX (XLOOKUP)


Сначала, для порядка, давайте озвучим официальный синтаксис. У нашей новой функции 6 аргументов:


=ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])


Выглядит немного громоздко, но последние три аргумента [в квадратных скобках] не являются обязательными (мы разберёмся с ними чуть позже). Так что, на самом деле, всё проще:


  • Первый аргумент (искомое_значение) — что мы ищем («гречка» из ячейки h5)


  • Второй аргумент (просматриваемый_массив) — диапазон ячеек, где мы ищем (столбец Товар в прайс-листе).


  • Третий аргумент (возвращаемый_массив) — диапазон, откуда хотим получить результаты (столбец Цена в прайс-листе).


Если сравнивать с ВПР, то стоит отметить, что:

  • По умолчанию используется точный поиск, т. е. не нужно это явно прописывать как в ВПР (последний нолик).

  • Не нужно отсчитывать и задавать номер столбца (третий аргумент ВПР). В больших таблицах это бывает непросто (особенно с учетом наличия скрытых столбцов).

  • Из предыдущего пункта автоматом следует, что вставка/удаление столбцов в прайс не ломают формулу (как было бы с ВПР).

  • Нет проблемы «левого ВПР», когда нужно извлечь значение левее просматриваемого столбца (например, артикул в нашем случае) — просматриваемый и возвращаемый массивы в ПРОСМОТРX могут располагаться как угодно (даже на разных листах, в общем случае!)

  • В общем и целом синтаксис гораздо проще и понятнее, чем у ВПР.


Также приятно, что ПРОСМОТРX отлично работает и в горизонтальном варианте без каких-либо доработок:



Раньше для этого нужно было использовать уже функцию ГПР (HLOOKUP) вместо ВПР (VLOOKUP).

Перехват ошибок #Н/Д


Если искомое значение отсутствует в списке, то функция ПРОСМОТРX, как и ВПР, выдаёт знакомую ошибку #Н/Д (#N/A):



Раньше для перехвата таких ошибок и замены их на что-нибудь более осмысленное применяли вложнную конструкцию из функций ЕСЛИОШИБКА (IFERROR) и ВПР (VLOOKUP). Теперь же можно сделать всё «на лету», используя 4-й аргумент [если_ничего_не_найдено] нашей новой функции :



Удобно.

Приблизительный поиск


Если мы ищем числа, то возможен поиск не только точного совпадения, но и ближайшего наименьшего или наибольшего к заданному числу. Например, для поиска ближайшей скидки, соответствующей определенному количеству товара или тарифа для расчета стоимости доставки на определенное расстояние.


В старой ВПР за это отвечал последний аргумент [интервальный_просмотр] — если задать его равным 1, то ВПР переходила в режим поиска ближайшего наименьшего значения. В ПРОСМОТРХ за этот функционал отвечает 5-й аргумент [режим_сопоставления]:



Он может работать по четырём различным сценариям:

  • 0 — точный поиск (это режим по-умолчанию)

  • -1 — поиск предыдущего, т.е. ближайшего наименьшего значения (для 29 шт. товара это будет скидка 5%)

  • 1 — поиск следующего, т.е. ближайшего наибольшего (для 29 шт. товара это будет уже 10% скидки)

  • 2 — неточный поиск текста с использованием подстановочных символов


Если с первыми тремя вариантами тут всё более-менее понятно, то последний стоит прокомментировать дополнительно. Имеется ввиду ситуация, когда мы ищем значение, где помимо букв и цифр использованы подстановочные символы * (звёздочка = любое количество любых символов) и ? (вопросительный знак = один любой символ).


На практике это может использоваться, например, так:



Заметьте, что, например, капуста в прайс-листе и бланке заказа здесь записана по-разному, но ПРОСМОТРX всё равно её находит, т. к. ищем мы уже не просто капусту, а капусту с приклеенными в начале и конце звёздочками и четвёртый аргумент нашей функции равен 2.


Функция ВПР, кстати говоря, всегда умела такое «из коробки», так что особого преимущества у ПРОСМОТРX здесь нет. Но важен другой нюанс: функция ВПР при включенном приблизительном поиске (последний аргумент =1) строго требовала сортировки искомой таблицы по возрастанию. Новая функция прекрасно ищет ближайшее наибольшее или наименьшее и в неотсортированном списке.

Направление поиска


Если в таблице есть не одно, а несколько совпадений с искомым значением, то функция ВПР всегда выдает первое, т.к. ведёт поиск исключительно сверху-вниз. ПРОСМОТРX может искать и в обратном направлении (снизу-вверх) — за это отвечает последний 6-й её аргумент [режим_поиска]:



Благодаря ему, поиск первого и (главное!) последнего совпадения больше не представляет сложности — различие будет только в значении этого аргумента:



Раньше для поиска последнего совпадения приходилось неслабо шаманить с формулами массива и несколькими вложенными функциями типа ИНДЕКС, НАИБОЛЬШИЙ и т. п.

Резюме


Если вы дочитали до этого места, то выводы, я думаю, уже сделали сами 🙂 На мой взгляд, у Microsoft получилось создать очень достойного наследника легендарной функции ВПР, добавив мощи и красоты и сохранив, при этом, простоту и наглядность использования.


Минус же пока только в том, что эта функция в ближайшее время появится только у подписчиков Office 365. Пользователи standalone-версий Excel 2013, 2016, 2019 эту функцию не получат, пока не обновятся до следующей версии Office (когда она выйдет). Но, рано или поздно, эта замечательная функция появится у большинства пользователей — вот тогда заживём! 🙂

Ссылки по теме

  • Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений

  • Левый ВПР

  • Связка функций ИНДЕКС и ПОИСКПОЗ как аналог ВПР

Функция ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами

В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения. 

В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.

Как создать функцию ВПР в Excel

Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

  1. Выделить ячейку и вписать функцию.

  2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

После этого открывается окно, где можно заполнить ячейки аргументов формулы.

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию. 

В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:h21». Знаки «$» можно поставить вручную, а можно выделить «G2:h21» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат. 

  1. Формула сканирует таблицу по вертикали.

  2. Находит в самом левом столбце совпадение с искомым значением.

  3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

  4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Автозаполнение

В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.

Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной. 

  • В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

  • Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия».  Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

  • Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

  • Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

  • Функцию можно вписать вручную или в специальном окне (Shift + F3).

  • Искомое значение – относительная ссылка, а таблица – абсолютная. 

  • Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

  • Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

  • Порядок работы с функцией подходит для Гугл-таблиц.

Как отображать формулы в Excel: 4 простых метода (2023)

Перейти к содержимому Как отображать формулы в Excel: 4 простых метода (2023)

Вы когда-нибудь работали с электронной таблицей, заполненной формулами?

В таком случае, чтобы понять, как работает каждая из формул и как получаются результаты, вы можете просмотреть формулы в ячейках.

Это руководство научит вас отображать формулы в Excel. 😀

Загрузите наш образец рабочей тетради здесь, чтобы следовать руководству, пока мы углубляемся в детали этой темы.

Содержание

Как отобразить формулы в Excel с ленты

Введите любую формулу в Excel, например = 2+ 2, и нажмите «Ввод».

Excel займет меньше наносекунды, чтобы запустить приведенную выше формулу и отобразить результаты.

Но что, если вы хотите, чтобы формула «= 2 + 2» отображалась только на дисплее? Вот как вы можете это сделать.

1. Выберите любую ячейку рабочего листа.

2. Перейдите на ленту > вкладка «Формулы» > группа «Аудит формул».

3. Нажмите кнопку «Показать формулы».

4. Excel теперь будет отображать формулы для всех ячеек на листе, а не результат.

Отлично! Как мы теперь можем вернуть результаты?

5. Вернитесь к группе «Лента» > «Формулы» > «Аудит формул».

6. Снова нажмите кнопку «Показать формулы», и готово.

Excel снова отображает результат, а не формулу.

Показать формулы с помощью команды быстрого доступа

Вы также можете отображать формулы в Excel с помощью сочетания клавиш. Это быстро и легко.

1. Щелкните любую ячейку рабочего листа Excel.

2. Нажмите клавишу Ctrl + Клавиша акцента ( ` ).

Не можете найти клавишу ударения на клавиатуре? Посмотрите слева от 1 от цифровых клавиш.

Каспер Лангманн , специалист по Microsoft Office

При этом во всех ячейках рабочего листа вместо результатов отображаются формулы.

Обратите внимание, что этот ярлык отображает формулы только для всех ячеек активного рабочего листа. Не всю рабочую тетрадь.

4. Уже хотите вернуться? Нажмите клавишу Ctrl + Клавиша ударения ( ` ) еще раз.

Excel возвращает отображение результатов. Больше никаких формул.

Это для пользователей Windows. Изменяется ли метод для пользователей Mac?

Конечно нет. Даже если вы являетесь пользователем Mac , процесс остается прежним. Нажмите клавишу Control + клавишу серьезного ударения (`), чтобы отобразить формулы.

Всякий раз, когда вы закончите и захотите вернуться к дисплею по умолчанию, снова нажмите те же клавиши вместе.

Как отображать формулы только в определенных ячейках

Мы уже встречались с двумя способами отображения формул в Excel. Однако вы что-то заметили?

Оба вышеуказанных метода будут отображать формулы для всех ячеек активного рабочего листа.

Но что, если вы хотите отобразить формулу только выбранной ячейки? Есть два способа добиться этого.

я. Добавьте пробел перед формулой

Возьмите в качестве примера следующее изображение, где ячейки A1, A2 и A3 содержат формулу.

Вы хотите отобразить формулу только для ячеек A1 и A2, а не для ячейки A3.

1. Выберите ячейки A1 и A2.

2. Перейдите на вкладку «Главная» > группа «Редактирование» > «Найти и выбрать» > «Заменить».

Откроется диалоговое окно «Найти и заменить», как показано ниже.

Вы можете использовать сочетание клавиш Control + H, чтобы запустить функцию «Найти и заменить».

Каспер Лангманн , специалист по Microsoft Office

3. На вкладке «Поиск» введите знак равенства «=».

4. На вкладке «Заменить» введите знак равенства, но после пробела «=».

Таким образом, формулы в выбранных ячейках будут начинаться не со знака равенства, а с пробела. А Excel распознает формулу, только если она начинается со знака равенства.

5. Результаты только в выбранных ячейках будут заменены формулами следующим образом.

В выбранном диапазоне (ячейки A1 и A2) отображаются формулы. В ячейке A3 продолжают отображаться результаты.

ii. Добавьте апостроф перед формулой

Если вы не хотите, чтобы пробел был виден в ячейке, вы можете использовать этот метод.

1. Выберите ячейки A1 и A2.

2. Перейдите на вкладку «Главная» > группа «Редактирование» > «Найти и выбрать» > «Заменить».

3. В диалоговом окне «Найти и заменить» введите знак равенства (=) напротив «Найти».

4. На вкладке «Заменить» введите знак равенства, но после апострофа (‘=).

Таким образом, формулы в выбранных ячейках будут начинаться не со знака равенства, а с апострофа. Всякий раз, когда что-то начинается с апострофа в ячейке, Excel воспринимает это как текстовую строку.

5. Результаты только в выбранных ячейках будут заменены формулами следующим образом.

Однако, в отличие от символа пробела, апостроф в ячейке не виден. Он виден только в строке формул или в режиме редактирования.

Профессиональный совет!

Если вы спешите и хотите только мельком увидеть формулу в ячейке, описанные выше методы могут вам не подойти.

Чтобы проверить формулу в ячейке, вы можете нажать «F2». Это мгновенно покажет формулу, работающую за ячейкой.

Однако, как только вы щелкнете мышью или нажмете клавишу ввода, формула будет заменена результатом.

Как показать формулы с помощью функции FORMULATEXT

Пришло время познакомить вас с современной функцией Excel. Спорим, вы не слышали об этом раньше.

На изображении ниже есть ячейка, содержащая формулу.

Вы хотите, чтобы эта формула отображалась так, как она отображается в строке формул, но в другой ячейке?

1. Активируйте ячейку и напишите функцию FORMULATEXT следующим образом.

= ФОРМУЛАТЕКСТ (A1)

Запишите это как второй аргумент в функции ПОИСКПОЗ, заменив то, что есть в данный момент.

Функция ФОРМУЛАТЕКСТ в Excel имеет один аргумент — аргумент ссылки.

В аргументе ссылки вы создаете ссылку на ячейку, формулу которой вы хотите отобразить. Мы создали ссылку на ячейку A1.

2. Нажмите Enter, чтобы увидеть следующие результаты.

Обратите внимание, как Excel отображает формулу указанной ячейки.

Вы можете вывести формулу любой ячейки. 😉

Профессиональный совет!

Что, если ячейка A1 выше содержит формулу со ссылкой на другую книгу?

Функция FORMULATEXT возвращает ошибку #N/A, если эта книга не открыта в фоновом режиме.

Как выбрать все формулы в выборе

Если вы получили только файл Excel, подготовленный кем-то другим, как вы можете быстро идентифицировать ячейки, за которыми работает формула?

На изображении ниже есть список чисел (некоторые из которых являются результатом формулы). Принимая во внимание, что некоторые другие просто пробиты числовыми значениями.

Чтобы выбрать формулы для первых шести ячеек этого списка (от A1 до A6), выполните следующие действия.

1. Выберите ячейки (от A1 до A6).

2. Перейдите на вкладку «Главная» > «Редактирование» > «Найти и выбрать» > «Перейти к специальному».

Или нажмите клавишу Control + G.

Каспер Лангманн , Microsoft Office Specialist

3. В диалоговом окне «Перейти» нажмите специальный.

4. Проверьте «Формулы» и нажмите «ОК».

5. Excel выбирает все ячейки с формулой от A1 до A6.

A5 и A6 не имеют никаких формул, а только числа. Следовательно, Excel не выбрал их.

Вы можете отформатировать ячейки (выделить их) или проверить количество этих ячеек в строке состояния.

Как печатать формулы

Всякий раз, когда вы печатаете лист Excel, распечатка содержит результаты расчетов. Не формулы.

Нужна распечатка электронной таблицы, чтобы вместо результатов были формулы?

1. Выполните любой из описанных выше способов, чтобы отобразить формулу в ячейках.

2. Когда на листе начнут отображаться формулы, перейдите на вкладку «Файл» > «Печать».

Или просто используйте сочетание клавиш печати. Нажмите клавишу Control + P.

Каспер Лангманн , специалист Microsoft Office

3. Вот как выглядит предварительный просмотр печати.

Окончательная печатная версия будет содержать формулы, а не результаты их вычислений.

Вот и все – Что теперь?

Приведенный выше учебник расскажет вам обо всем, что касается формул. Начиная с отображения формул в Excel и заканчивая их печатью.

Как только вы поймете, как работают формулы в электронной таблице, все начнет обретать смысл.

Но знаете ли вы, как использовать некоторые из наиболее важных формул/функций Excel? Нравятся функции ВПР, СУММЕСЛИ и ЕСЛИ?

Вот мой 30-минутный бесплатный курс электронной почты, который поможет вам быстро освоить эти и многие другие функции Excel. Ссылка для загрузки страницы , и формула исчезнет.

Вот как это должно работать.

Но что, если вы хотите, чтобы в ячейках отображались формулы, а не вычисляемые значения.

В этом руководстве по Excel я рассмотрю следующие темы:

  • Как отображать формулы в Excel вместо значений.
  • Как распечатать формулы в Excel.
  • Как отображать формулы в Excel только в выбранных ячейках.
  • Что делать, если Excel отображает формулы вместо вычисляемых значений.

Показать формулы в Excel вместо значений

Вот шаги для отображения формул в Excel вместо значений:

  • Нажмите на вкладку «Формулы» на ленте.
  • В группе «Аудит формул» щелкните параметр «Показать формулы».

Как только вы нажмете «Показать формулы», формулы на листе станут видимыми. Это кнопка-переключатель, поэтому вы можете нажать на нее еще раз, чтобы формулы были заменены ее вычисленным результатом.

Это кнопка-переключатель, поэтому вы можете щелкнуть по ней еще раз, чтобы формулы были заменены ее расчетным результатом.

Как показано ниже, столбец I содержит формулы. Как только нажата кнопка «Показать формулы», в ячейках отображаются формулы вместо значения.

Вы также можете использовать сочетание клавиш Excel – Control + ` (вы найдете эту клавишу в верхней левой части клавиатуры под клавишей Escape).

Примечание. Это метод на уровне листа. Это означает, что при использовании параметра «Показать формулы» или ярлыка будут отображаться только формулы на активном листе. Все остальные рабочие листы не будут затронуты. Чтобы отобразить формулы на других листах, вам нужно перейти на этот лист и использовать этот ярлык (или кнопку на ленте).

В некоторых случаях у вас может быть много листов, и вы хотите показать формулы на всех листах в книге.

Вот шаги, которые отобразят формулы на всех листах в Excel:

  • Перейдите на вкладку «Файл». Если вы используете Excel 2007, перейдите к кнопке Office.
  • Нажмите «Параметры».
  • На левой панели выберите Дополнительно.
  • Справа прокрутите вниз до раздела «Параметры отображения для этого рабочего листа».
  • : В раскрывающемся списке выберите рабочий лист, на котором вы хотите отображать формулы вместо значений.
  • Установите флажок «Показывать формулы в ячейках вместо результатов их вычислений».
  • Нажмите OK.

Как уже упоминалось, хотя может показаться, что здесь больше шагов по сравнению с ярлыком или кнопкой «Показать формулы» на ленте, это полезно, когда у вас есть несколько листов и вы хотите отобразить формулы на всех этих листах.

 Читайте также: Почему формулы не работают в Excel

Как печатать формулы в Excel

Вот шаги для печати формул в Excel:

  • Перейдите на вкладку «Формула».
  • Нажмите кнопку «Показать формулу».
  • Выберите «Файл» -> «Печать».

Вышеуказанные шаги обеспечат печать формул, а не значений.

Показывать формулы в Excel вместо значения только в выбранных ячейках

Описанные выше методы отображали все формулы на листе.

Однако вы можете захотеть отобразить формулы только в некоторых выбранных ячейках.

Например, как тренер по Excel, я часто создаю шаблоны, в которых я показываю формулу в одной ячейке, а ее результат — в другой (как показано ниже).

Вот шаги для отображения формул в Excel только в выбранных ячейках:

  • Выберите ячейку, в которой вы хотите показать формулу вместо значения.
  • Перейти на главную страницу -> Найти и выбрать -> Заменить (сочетание клавиш — Control + H).
  • В диалоговом окне «Найти и заменить» на вкладке «Заменить» введите = в поле «Найти» и «=» в поле «Заменить на».
  • Нажмите «Заменить все».

Это покажет формулы во всех выбранных ячейках, а остальные ячейки останутся без изменений.

Примечание. Ввод пробела перед формулой делает ее текстовой строкой, и перед знаком равенства отображается символ пробела. С другой стороны, использование апострофа перед знаком равенства делает формулу текстовой строкой, однако апостроф не отображается в ячейке (он отображается только в строке формул и в режиме редактирования).

Как работать с Excel, показывающим формулы вместо вычисляемых значений

Иногда вы можете обнаружить, что в ячейках Excel вместо значения отображается формула.

Есть несколько причин, по которым это может произойти:

  • Включен режим «Показать формулы» или вы случайно нажали сочетание клавиш Control + `. Чтобы отключить его, просто снова используйте ярлык или нажмите «Показать формулу» на вкладке «Формулы».
  • Это может быть связано с наличием пробела или апострофа перед знаком равенства в формуле. Наличие их перед знаком равенства делает формат ячейки текстовым, и формула отображается вместо значения. Чтобы справиться с этим, просто удалите их. Вы можете использовать найти и заменить, чтобы сделать это.
  • Если к ячейке применено форматирование «Текст», и вы вводите формулу и нажимаете клавишу ввода, она будет по-прежнему отображать формулу вместо вычисленного значения. Чтобы решить эту проблему, перейдите на вкладку «Главная» и в группе «Число» измените форматирование на «Общее».