Формула это в excel: Обзор формул — Служба поддержки Майкрософт

Глава 10 Работа с формулами и функциями. Компьютер для бухгалтера

Глава 10 Работа с формулами и функциями

Программа Excel предоставляет широкие возможности для использования формул. Как уже упоминалось в прошлой главе, формулы – это выражения, состоящие из числовых величин, адресов ячеек и функций, соединенных знаками арифметических операций. О них и пойдет речь в данной главе.

• Ввод формул в ячейку

• Функции

• Относительная и абсолютная адресация

• Защита ячеек, листов и книг

• Работа со списками

• Сводные таблицы

Ввод формул в ячейку

Формула в программе начинается со знака = или +. В формуле могут использоваться операторные скобки. Например, формула может выглядеть следующим образом: =А4/(В1+12).

В той ячейке, где находится формула, виден только результат вычислений над данными, содержащимися в ячейках А4 и В1. Если меняются значения в ячейке А4 или В1, то автоматически меняется и результат в формуле. Саму формулу можно увидеть в строке формул, если сделать активной содержащую ее ячейку.

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

Рассмотрим, как работать с формулами и функциями в программе.

Чтобы научиться вводить формулы в ячейки, создадим накладную, содержащую четыре графы: наименование товара, цена, количество и сумма. Сумма – это вычисляемая величина, поэтому нам необходимо в ячейки с суммой ввести формулы.

Начнем с оформления документа:

1. Заполните ячейки значениями, приведенными в табл. 10.1.

Таблица 10.1. Шапка накладной

2. В ячейку D4 введем формулу =В4*С4 (рис. 10.1). Можно ввести формулу, набрав адреса ячеек с клавиатуры, но гораздо удобнее вводить формулы с помощью мыши.

Рис. 10.1. Ввод формулы в ячейку

3. В ячейку D4 введите знак равенства (=).

4. Щелкните в ячейке В4 (обратите внимание на то, что ее адрес при этом попал в редактируемую ячейку).

5. Наберите математический знак умножения (*).

6. Щелкните во второй ячейке, которая участвует в формуле, – С4.

7. Нажмите клавишу Enter.

После заполнения граф Цена и Количество в графе Сумма автоматически появится результат.

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

9. Если изменить значения граф Количество и Цена, в графе Сумма результат будет автоматически пересчитан.

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

Возможные сообщения об ошибках могут быть следующими:

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

в формуле может указываться диапазон ячеек: он показывается двоеточием. Например, диапазон ячеек от А1 до А10 будет указан так: А1:А10.

В формулах могут также использоваться операторные скобки и логические функции. Например, при вычислении подоходного налога формула будет выглядеть так: =(СЗ-400)*0,13, где в ячейке с адресом СЗ указан оклад сотрудника.

Функции

Под функцией понимают зависимость некоторой переменной (у) от одной (х) или нескольких переменных (х1, х2, …, хп), причем каждому набору значений переменных х1, х2, …, хп должно соответствовать единственное значение зависимой переменной у. Одним из преимуществ программы Excel является встроенный набор математических, финансовых, статистических, текстовых и логических функций. При вводе некоторого набора чисел Excel может обработать их более чем 300 различными способами: от подсчета среднего значения до вычисления сложных тригонометрических зависимостей.

Функции Excel – это специальные формулы, хранящиеся в памяти программы. Каждая функция включает в себя две части: имя функции и ее аргументы.

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

Чтобы познакомиться с набором функций Excel, щелкните на кнопке Вставка функции панели инструментов Стандартная. Откроется окно мастера функций.

Первые две категории функций – это группы Последние использовавшиеся (в списке 10 функций, которые использовались последними) и Полный алфавитный перечень, включающий все функции, перечисленные в алфавитном порядке.

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

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

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

Категория Математические позволяет вычислять математические функции, такие как сумма, произведение, частное, квадратный корень, логарифм и т. д. Желающие могут поупражняться в переводе арабских цифр в римские. Есть функция, возвращающая значение числа л. В этом разделе также представлено несколько разновидностей округления и суммирования. Здесь можно также упомянуть операции округления до ближайшего четного или нечетного, до меньшего целого и т. д. Функция, выполняющая суммирование (СУММ), дополнена функциями СУММ KB (сумма квадратов), СУММПРОИЗВЕД (сумма произведений), СУММКВРАЗН (сумма квадратов разностей), СУММРАЗНКВ (сумма разностей квадратов).

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

Категория Текстовые позволяет выполнять операции с текстами: подсчитать количество символов в ячейке, узнать код символа, поместить в данную ячейку некоторое количество символов из другой, проверить идентичность двух текстов, найти некоторый текст и заменить его другим.

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

Мастером функций удобно пользоваться, чтобы не запутаться в многочисленных функциях. Для вставки функции вызовите мастер функций, выберите категорию и необходимую функцию и щелкните на кнопке ОК, а затем следуйте указаниям мастера. Для перехода на следующий шаг щелкайте на кнопке Далее, а в конце – на кнопке Готово.

Название функции может быть написано по-русски или по-английски в зависимости от версии программы Excel. В скобках указываются аргументы.

Для вставки функции не обязательно пользоваться мастером функций – можно набирать функции и на клавиатуре.

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

Функция СУММ

Наиболее простой способ суммирования – сделать активной ячейку, в которую надо поместить сумму, и щелкнуть на кнопке Автосумма на панели инструментов Стандартная. Таким образом удобно суммировать данные в столбцах и строках (рис. 10.2).

Рис. 10.2. Суммирование данных

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

1. Откройте окно мастера функций (рис. 10.3), выберите категорию Математические, затем выберите функцию СУММ и щелкните на кнопке ОК.

Рис. 10.3. Выбор функции с помощью мастера функций

2. В открывшемся диалоговом окне наберите адрес ячейки или выберите ее щелчком мыши на рабочем листе.

3. Для того чтобы лист стал доступным, щелкните на кнопке справа в поле ввода.

4. Выделите ячейки листа и вернитесь в окно мастера функций, щелкнув на кнопке справа в свернутой строке ввода.

5. Щелкните на кнопке ОК.

Функция ЕСЛИ

Данная функция относится к разряду логических и применяется, когда заранее неизвестно, какой результат будет в ячейке. Функция имеет следующий формат:

Данный текст является ознакомительным фрагментом.

Глава 8 Работа с функциями XSLT и XPath

Глава 8
Работа с функциями XSLT и XPath
В этой главе мы изучим доступные вам в XSLT функции — как встроенные в XSLT, так и функции XPath. Вы уже встречались с этими функциями выборочно (например, с id, generate-id, position, count и т.д). В этой главе мы рассмотрим их все.Некоторые функции входят в XSLT,

Различия между функциями wait и waitpid

Различия между функциями wait и waitpid
Теперь мы проиллюстрируем разницу между функциями wait и waitpid, используемыми для сброса завершенных дочерних процессов. Для этого мы изменим код нашего клиента TCP так, как показано в листинге 5.7. Клиент устанавливает пять соединений с

Глава 4 Работа с формулами в редакторе Word

Глава 4 Работа с формулами в редакторе Word
• Математические формулы• Формулы, созданные в предыдущих версиях Word• Химический редактор ChemPen3D1Сложно представить себе дипломную или курсовую работу без формул. В работах по точным наукам (математике, физике, химии) без них

87. Делайте предикаты чистыми функциями

87. Делайте предикаты чистыми функциями
РезюмеПредикат представляет собой функциональный объект, который возвращает ответ да/нет, обычно в виде значения типа bool. Функция является «чистой» в математическом смысле, если ее результат зависит только от ее аргументов

Работа с формулами

Работа с формулами
В ячейках таблицы Excel могут содержаться как данные, так и формулы. Формулы – не что иное, как набор математических и логических операторов и данных. В качестве данных в формулах могут выступать как постоянные значения, так и адреса ячеек (или диапазонов).

9. Как правильно пользоваться функциями

9. Как правильно пользоваться функциями
ФУНКЦИИСТРОИТЕЛЬНЫЕ БЛОКИ ПРОГРАММЫСВЯЗЬ МЕЖДУ ФУНКЦИЯМИ: АРГУМЕНТЫ, УКАЗАТЕЛИ, ВОЗВРАТ ЗНАЧЕНИЯТИПЫ

Использование указателей для связи между функциями

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

Б.4. Взаимосвязь с библиотечными функциями ввода-вывода

Б.4. Взаимосвязь с библиотечными функциями ввода-вывода
Выше уже говорилось о том. что функции ввода-вывода стандартной библиотеки языка С реализованы на основе низкоуровневых функций. Иногда удобнее работать с одними, иногда — с другими.Если файл был открыт с помощью

22.

1. Сложные функции и сложности с функциями

22.1. Сложные функции и сложности с функциями
Функции могут принимать входные аргументы и возвращать код завершения.function_name $arg1 $arg2Доступ к входным аргументам, в функциях, производится посредством позиционных параметров, т.е. $1, $2 и так

Видеоплаты с функциями видеозахвата

Видеоплаты с функциями видеозахвата
Некоторые фирмы, производящие видеоплаты (например, фирма Matrox), создали свои модули захвата кадров, работающие только с данным видеоадаптером. Несколько лет назад такое решение было довольно распространенным, однако сейчас подобные

Глава 3 Работа с математическими формулами

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

У18.

1 Эмуляция перечислимых типов однократными функциями

У18.1 Эмуляция перечислимых типов однократными функциями
Покажите, что при отсутствии unique-типов перечислимый тип языка Pascaltype ERROR = (Normal, Open_error, Read_error)может быть представлен классом с однократной функцией для каждого значения

6.2.7. Работаем с функциями

6.2.7. Работаем с функциями
Для выполнения задания нам понадобится материал разд. 5.2.7.Задание? Создать таблицу, как показано на рис. 6.34.? В ячейки диапазона F6: F13 записать формулы для подсчета суммы набранных баллов.? В ячейки G6:G13 внести формулы для отображения отметок о

Практическая работа 44. Создание простой таблицы с формулами

Практическая работа 44. Создание простой таблицы с формулами
Задание. Составить таблицу расчета заработной платы для работников отдела, имея следующие исходные данные: фамилии сотрудников, их часовые ставки и количество отработанных часов. В таблице требуется вычислить:

Основные формулы Excel и как их использовать

Excel — это мощное и популярное программное обеспечение для работы с электронными таблицами, которое многие предприятия и частные лица используют для организации и анализа данных. Одной из самых полезных функций Excel являются формулы и функции. С помощью формул можно быстро выполнять сложные задачи за короткий промежуток времени.

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

Что такое формулы и функции Excel?

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

Например, допустим, ячейка A1 содержит значение 2 и B1 содержит значение 3. Чтобы получить сумму A1 и A2 в ячейке C1, введите формулу =A1+B1 в C1. Теперь значение в C1 должно быть 5. Если вы измените значение в A1 на 4, значение в ячейке C1 изменится на 7. Эта функциональность означает, что вам не нужно вручную вычислять значения каждый раз, когда вам нужно изменить элемент.

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

Совет профессионала: Многие функции excel также могут быть использованы в других программах электронных таблиц, таких как Google Sheets.

Основные формулы в Excel

Excel предлагает более 475 предопределенных формул. Однако некоторые функции, например, вычисление базовых математических задач или подсчет ячеек, используются чаще других. Вот некоторые из наиболее распространенных функций Excel:

COUNT

С помощью функции COUNT можно определить, в скольких ячейках содержится числовое значение. Например, вы можете использовать функцию COUNT для сканирования первых 100 записей в столбце A и возврата количества ячеек, в которых есть число.

Формула для этой функции =COUNT(Range).

COUNTA

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

Формула для этой функции =COUNTA(Range).

SUM

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

Формула для этой функции =SUM(Range).

СРЕДНЕЕ

Формула AVERAGE, как и SUM, возвращает среднее значение из указанных ячеек. Она берет общую сумму ячеек, затем делит ее на количество ячеек. По сути, это функция SUM, разделенная на функцию COUNT.

Формула для этой функции =AVERAGE(Range).

Утверждения IF

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

Формула для операторов ЕСЛИ =IF(условие, TRUE, FALSE).

Например, допустим, вы составляете маркетинговый список для предстоящей рассылки по электронной почте. Вы хотите отправить письмо клиентам, которые потратили больше денег в этом году (столбец A), чем в прошлом (столбец B). В этом случае вы бы использовали формулу =IF(A1>B1, Отправить, Не отправлять ). Тогда, если значение в A1 больше, чем в B1, формула вернет текст Отправить. В противном случае она вернет значение Не отправлять.

CONCATENATE

С помощью функции CONCATENATE можно объединить значения двух или более ячеек. Например, если есть список имен в столбце A и фамилий в столбце B, можно использовать функцию CONCATENATE, чтобы вернуть полное имя в столбце C.

Формула для этой функции =CONCATENATE(Value1, Separator, Value2). В приведенном выше примере формула в C1 будет =CONCATENATE(A1, , B1).

МАКС и МИН

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

Формула для функции MAX =MAX(Value1, Value2). Например, =MAX(B1, B10) вернет наибольшее значение, найденное между B1 и B10.

Формула для функции MIN имеет вид =MIN(Value1, Value2). Например, =MIN(B1, B10) вернет наименьшее значение между B1 и B10.

AND

Функция AND проверяет, является ли что-то истинным или ложным.

Например, формула =AND(A6>10, B4= Печать ) вернет TRUE, если A6 больше 10 и B4 содержит текст выведите. Если ни одно из этих условий не выполняется, формула возвращает FALSE. В функции И можно добавить столько условий, сколько вам нужно.

XLOOKUP

XLOOKUP очень полезна для поиска информации в электронной таблице. С помощью этой функции вы предоставляете значение в заданном столбце или строке и запрашиваете соответствующее ему значение. Например, имеется таблица с именами в столбце A, от A1 до A10, и соответствующими днями рождения в столбце B, от B1 до B10. Использование функции XLOOKUP позволит вам узнать день рождения человека. В этом примере формула может выглядеть примерно так =XLOOKUP( Patty ,A1:A10,B1:B10).

XLOOKUP — новая формула для пользователей Excel. Если у вас более старая версия Excel, вам может понадобиться использовать VLOOKUP или HLOOKUP, которые выполняют по сути ту же задачу.

Советы по использованию формул excel

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

Как ввести формулу

1. Начните с нажатия на ячейку, где должен появиться результат.

2. Над электронной таблицей, рядом с кнопкой Fx, находится длинная белая полоса, в которую можно ввести формулу. Это известно как формульная линейка. Все формулы начинаются с оператора =, с последующей формулой. Например, чтобы найти сумму A1 и A2, введите =A1+A2.

Как использовать функцию

Чтобы войти в функцию, выполните следующие действия:

1. Щелкните по ячейке, в которой должен появиться результат.

2. Найдите кнопку Fx, расположенную над электронной таблицей и рядом со строкой формул. Должно появиться новое окно, в котором отображаются доступные функции.

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

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

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

Как изменить формулу

Чтобы изменить формулу в Excel, выполните следующие действия:

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

2. Щелкните внутри этой формулы и отредактируйте ее при необходимости. Например, вы можете изменить формулу на =A1+A2 на =A1+A3 или =SUM(A1:A2) на =SUM(B1:B2).

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

Как удалить формулу и сохранить значение

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

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

2. Нажмите Ctrl + C , чтобы скопировать ячейки.

3. Щелкните правой кнопкой мыши по выделенным ячейкам.

4. Выберите Вставить значения > значения , чтобы вставить рассчитанные значения обратно в выделенные ячейки.

Избегайте использования форматированных чисел

Числа, используемые в таблице excel, должны вводиться без какого-либо форматирования, например, запятых, знаков доллара или апострофов. Хотя привычно ставить знак доллара ($) перед цифрой, когда речь идет о деньгах, это может запутать excel и привести к нежелательным ошибкам.

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

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

Если вы не хотите, чтобы они что-то меняли, у вас есть возможность либо защитить весь рабочий лист/книгу, либо защитить определенные ячейки с важными данными (которые вы не хотите, чтобы пользователь испортил).

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

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

В этом уроке я покажу вам, как скрыть формулы в Excel на защищенном листе (чтобы он не был виден пользователю).

Итак, приступим!

В этом руководстве рассматриваются:

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

Когда у вас есть формула в ячейке, пользователь может увидеть ее двумя способами:

1. Двойным щелчком по ячейке и переходом в режим редактирования
2. Выбрав ячейку и увидев формулу в строке формул

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

Предположим, у вас есть набор данных, как показано ниже, где у вас есть формула в столбце D.

Ниже приведены шаги, чтобы скрыть все формулы в столбце D:

1. Выберите ячейки в столбце D, содержащие формулу что вы хотите скрыть
2. Перейдите на вкладку «Главная»
3. В группе «Число» нажмите кнопку запуска диалогового окна (это маленькая иконка с наклонной стрелкой в ​​правом нижнем углу группы)
4. В открывшемся диалоговом окне «Формат ячеек» щелкните вкладку «Защита»
5. Проверьте параметр «Скрытый»
6. Нажмите OK
7. Перейдите на вкладку «Обзор» на ленте.
8. В группе «Защита» выберите параметр «Защитить лист».
9. В диалоговом окне «Защита» введите пароль, который потребуется, если вы хотите разблокировать рабочий лист (если вы не хотите применять пароль, вы можете оставить это поле пустым)
10. Нажмите OK

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

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

Хотя этот метод работает нормально, вы должны знать, что листы/ячейки, защищенные в Excel, могут быть легко разблокированы пользователем.

Любой технически подкованный пользователь может легко проникнуть в ваши защищенные рабочие книги (простой поиск в Google даст им несколько способов взломать защищенный рабочий лист). Это не прямолинейно, но и не слишком сложно.

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

 Также прочитайте: Как заблокировать формулы в Excel 

Как скрыть только формулы в Excel (и оставить остальные ячейки редактируемыми)

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

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

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

Это тоже легко сделать.

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

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

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

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

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

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

Посмотрим, как это сделать.

Шаг 1. Отключите свойство блокировки для всех ячеек

Итак, сначала нам нужно отключить свойство блокировки для всех ячеек (чтобы их нельзя было защитить)

Ниже приведены шаги для этого:

1. Выберите все ячейки рабочего листа (это можно сделать, нажав на серый треугольник в верхней левой части листа).
2. Перейдите на вкладку «Главная»
3. В группе «Число» нажмите кнопку запуска диалогового окна
4. В диалоговом окне «Формат ячеек» перейдите на вкладку «Защита».
5. Снимите флажок с параметра «Заблокировано»
6. Нажмите «ОК»

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

Теперь, даже если я пойду и защитю лист с помощью параметра на ленте (Просмотр >> Защитить лист), ячейки не будут полностью заблокированы, и вы все равно сможете редактировать ячейки.

Шаг 2. Включите заблокированное и скрытое свойство только для ячеек с формулами

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

И, блокируя эти ячейки, я бы убедился, что формула также скрыта из строки формул.

Ниже приведены шаги, чтобы скрыть формулы:

1. Выберите все ячейки на листе (вы можете сделать это, нажав на серый треугольник в верхней левой части листа).
2. Перейдите на вкладку «Главная»
3. В группе редактирования нажмите кнопку «Найти и выбрать».
4. Нажмите на опцию «Перейти к специальному».
5. В диалоговом окне «Перейти к специальному» выберите параметр «Формулы». Это выберет все ячейки, в которых есть формула

.

6. Выделив ячейки с формулами, удерживайте клавишу Control, а затем нажмите клавишу 1 (или клавишу Command и клавишу 1, если вы используете Mac). Откроется диалоговое окно «Числовой формат»

.

7. Перейдите на вкладку «Защита»
8. Убедитесь, что установлены флажки «Заблокировано» и «Скрытый»
9. Нажмите «ОК»

 Читайте также: Выделение ячеек с формулами в Excel 

Шаг 3. Защита рабочего листа

На данный момент свойство Locked отключено для всех ячеек, кроме тех, в которых есть формула.

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

Вот шаги для этого:

1. Перейдите на вкладку Обзор
2. В группе «Защита» выберите параметр «Защитить лист».
3. В диалоговом окне «Защитить лист» введите пароль (необязательно)
4. Нажмите «ОК»

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

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

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

Если вам интересно, можно ли скрыть формулы в Excel без защиты листа, к сожалению, нельзя .

Хотя это можно сделать с помощью сложного кода VBA, это будет ненадежно и может привести к другим проблемам. Вот статья, в которой есть такой код (используйте его, если вы действительно не можете без него)

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

Надеюсь, этот урок был вам полезен.

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

• Как заблокировать ячейки в Excel
• Как скрыть рабочий лист в Excel (который нельзя отобразить)
• Как отобразить столбцы в Excel
• Как отобразить листы в Excel
• Как скрыть или показать панель формул в Excel?

Лучшие сообщения в блогах 2022 года о новых формулах Excel • UpSlide

Последняя версия Excel содержит ряд интересных новых функций и улучшений.

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

Формула Excel № 1: ТЕКСПЛИТ

Формула Excel № 2: TEXTBEFORE и TEXTAFTER

Формула Excel № 3: ВОЗЬМИТЕ и ОТПАДИТЕ

Формула Excel № 4: CHOOSECOLS и CHOOSEROWS

Формула Excel № 5: VSTACK и HSTACK

Формула Excel № 6: XLOOKUP

БОНУС: Лучшие видеоролики о формулах Excel 2022 года

Формула Excel №1: TEXTSPLIT

TEXTJOIN уже некоторое время используется как формула для объединения текста… но встречали ли вы новый аналог TEXTJOIN: TEXTSPLIT?

TEXTSPLIT позволяет вам разделить текст на несколько столбцов/строк. Как и TEXTJOIN, эта новая функция Excel использует заданный разделитель (то есть пробел или запятую) для разделения текста в нужных ячейках.

Ознакомьтесь с TEXTSPLIT с помощью этого полезного контента:

• В этом блоге Ablebits Александр Фролов подробно рассказывает о функции РАЗДЕЛИТЬ ТЕКСТОВ в Excel.
• В этом блоге Excel Campus сравниваются преимущества TEXTSPLIT с TEXTBEFORE и TEXTAFTER: лучший способ разделения текста в Excel.

Формула Excel #2: ТЕКСТПЕРЕД и ТЕКСТПОСЛЕ

TEXTBEFORE и TEXTAFTER — отличные инструменты, если вы хотите извлечь текст из начала или конца ячейки. TEXTBEFORE извлекает весь текст, который стоит перед заданным разделителем, а TEXTAFTER делает то же самое с текстом после этого разделителя.

Узнайте, как использовать эти новых формул Excel , из этих коротких блогов ниже:

• В этом блоге Excel-Exercise показано, как использовать TEXTBEFORE и TEXTAFTER всего за две минуты: TEXTBEFORE и TEXTAFTER.
• Еще одна полезная статья Александра Фролова о том, как использовать функцию ТЕКСТДО: Excel ПЕРЕД.

Формула Excel №3: ВОЗЬМИТЕ И ОТПАДИТЕ

Эти новые функции Excel позволяют вам сохранить (ВЗЯТЬ) или удалить (УДАЛИТЬ) заданное количество столбцов/строк из начала или конца массива.

• Эти советы от службы поддержки Майкрософт помогут вам освоить их за считанные минуты: функция ВЗЯТЬ и функция БРОСИТЬ.

Формула Excel № 4: ВЫБОР РЯДОВ и ВЫБОР

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

Перейдите по этим ссылкам, чтобы разобраться с CHOOSECOLS и CHOOSEROWS:

• В этом блоге Get Digital Help дается краткий обзор CHOOSECOLS: как использовать функцию CHOOSECOLS.
• Эти страницы службы поддержки Майкрософт также хороши для изучения основ: CHOOSECOLS и CHOOSEROWS.

Формула Excel № 5: VSTACK и HSTACK

VSTACK и HSTACK позволяют комбинировать два или более набора массивов по вертикали (VSTACK) или по горизонтали (HSTACK). Более того, эти новые функции Excel работают даже с динамическими массивами и диапазонами с большими исходными размерами!

• В качестве краткого руководства эти руководства от ExtendOffice подробно описывают каждую новую функцию: функцию Excel VSTACK и функцию Excel HSTACK.

Формула Excel № 6: XLOOKUP

Если вы являетесь поклонником Excel, вы должны знать формулу XLOOKUP. Она доступна в Office365 и Excel для Интернета с 2020 года, но нам все еще не хватает этой функции в 2022 году! XLOOKUP — это мощная функция поиска , которая может выходить далеко за рамки ограничений функций VLOOKUP, INDEX и MATCH.