Формулы в excel функции в excel: 13 функций Excel, которые должен знать каждый специалист

Функция АДРЕС

— служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Найдите ссылки на информацию о работе с почтовыми адресами или создании почтовых ярлыков в См. также раздел .

Описание

Вы можете использовать функцию АДРЕС для получения адреса ячейки на рабочем листе с заданными номерами строки и столбца. Например, АДРЕС( 2,3) возвращает $C$2 . В качестве другого примера, ADDRESS( 77,300) возвращает $KN$77 . Вы можете использовать другие функции, такие как функции ROW и COLUMN , для предоставления аргументов номера строки и столбца для АДРЕС функция.

Синтаксис

АДРЕС (номер_строки, номер_столбца, [номер_абс.], [a1], [текст_листа])

Синтаксис функции АДРЕС имеет следующие аргументы:

• row_num     Обязательный. Числовое значение, указывающее номер строки для использования в ссылке на ячейку.

• номер_столбца     Обязательный. Числовое значение, указывающее номер столбца для использования в ссылке на ячейку.

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

• org/ListItem»>

  A1     Дополнительно. Логическое значение, указывающее стиль ссылок A1 или R1C1. В стиле A1 столбцы обозначаются в алфавитном порядке, а строки — в числовом. В стиле ссылок R1C1 и столбцы, и строки имеют числовые метки. Если аргумент A1 равен TRUE или опущен, Функция ADDRESS возвращает ссылку в стиле A1; если FALSE, функция ADDRESS возвращает ссылку в стиле R1C1.

  Примечание. Чтобы изменить стиль ссылок, который использует Excel, перейдите на вкладку Файл , щелкните Параметры , а затем щелкните Формулы . В разделе Работа с формулами установите или снимите флажок Стиль ссылок R1C1 .

• sheet_text     Необязательно. Текстовое значение, указывающее имя рабочего листа, используемого в качестве внешней ссылки. Например, формула = АДРЕС( 1,1,»Лист2″) возвращает Лист2!$A$1 . Если аргумент sheet_text опущен, имя листа не используется, а адрес, возвращаемый функцией, относится к ячейке на текущем листе.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

Как избежать ошибок в формулах

Excel

Формулы и функции

Ошибки

Ошибки

Как избежать ошибок в формулах

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для планшетов Android Excel 2010 Excel 2007 Excel Starter 2010 Больше. ..Меньше

Если Excel не может разрешить формулу, которую вы пытаетесь создать, вы можете получить сообщение об ошибке, подобное этому:

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

Совет:  Существует несколько распространенных функций, с которыми могут возникнуть проблемы. Узнайте больше о СЧЁТЕСЛИ, СУММЕСЛИ, ВПР или ЕСЛИ . Вы также можете увидеть список функций здесь.

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

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

Примечание. Если вы используете Microsoft 365 для Интернета, вы можете не увидеть те же ошибки или решения могут не применяться.

Excel выдает различные ошибки с символами фунта (#), такие как #ЗНАЧ!, #ССЫЛКА!, #ЧИСЛО, #Н/Д, #ДЕЛ/0!, #ИМЯ? и #NULL!, чтобы указать что-то в вашем формула не работает должным образом. Например, #ЗНАЧ! ошибка вызвана неправильным форматированием или неподдерживаемыми типами данных в аргументах. Или вы увидите #REF! ошибка, если формула ссылается на ячейки, которые были удалены или заменены другими данными. Рекомендации по устранению неполадок будут отличаться для каждой ошибки.

Примечание. #### не является ошибкой, связанной с формулой. Это просто означает, что ширина столбца недостаточно широка для отображения содержимого ячейки. Просто перетащите столбец, чтобы расширить его, или перейдите к Home > Format > AutoFit Column Width .

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

• org/ListItem»>

  Исправьте #ЧИСЛО! ошибка

• Исправьте #ЗНАЧ! ошибка

• Исправьте ошибку #Н/Д

• Исправьте #DIV/0! ошибка

• Исправьте #REF! ошибка

• Исправить #ИМЯ? ошибка

• org/ListItem»>

  Исправьте #NULL! ошибка

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

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

Вы всегда можете отключить отображение диалогового окна при запуске. Для этого перейдите в меню «Файл» > «Параметры» > «Дополнительно» > «Общие». и снимите флажок Запрашивать обновление автоматических ссылок .

Важно:  Если вы впервые работаете с неработающими ссылками в формулах, если вам нужно освежить в памяти информацию об устранении неработающих ссылок или если вы не знаете, следует ли обновлять ссылки, см. раздел Управление внешними ссылками. (ссылки) обновлены.

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

• Убедитесь, что Excel настроен на отображение формул в электронной таблице. Для этого выберите Формулы и в группе Аудит формул выберите Показать формулы .

  Совет: Вы также можете использовать сочетание клавиш Ctrl + ` (клавиша над клавишей Tab). Когда вы это сделаете, ваши столбцы автоматически расширятся для отображения ваших формул, но не беспокойтесь, когда вы вернетесь к обычному виду, ваши столбцы изменятся в размере.

• Если описанный выше шаг по-прежнему не решает проблему, возможно, ячейка отформатирована как текст. Вы можете щелкнуть правой кнопкой мыши по ячейке и выбрать Формат ячеек > Общие (или Ctrl + 1 ), затем нажмите F2 > Введите , чтобы изменить формат.

• Если у вас есть столбец с большим диапазоном ячеек, отформатированных как текст, вы можете выбрать диапазон, применить числовой формат по вашему выбору и перейти к Данные > Текст в столбец > Готово . Это применит формат ко всем выбранным ячейкам.

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

1. Выберите вкладку Файл , выберите Параметры , а затем выберите категорию Формулы .

2. org/ListItem»>

   В Параметры расчета , в разделе Расчет рабочей книги убедитесь, что выбран параметр Автоматический .

Дополнительные сведения о вычислениях см. в разделе Изменение пересчета формулы, итерации или точности.

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

Дополнительные сведения о циклических ссылках см. в разделе Удаление или разрешение циклических ссылок.

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

Когда вы вводите что-то вроде СУММ(A1:A10) , Excel отображает текстовую строку СУММ(A1:A10) вместо результата формулы. В качестве альтернативы, если вы введете 11/2 , Excel отобразит дату, например 2 ноября или 02.11.2009, вместо деления 11 на 2.

Чтобы избежать таких неожиданных результатов, всегда запускайте функцию со знаком равенства. Например, введите: = СУММ(A1:A10) и =11/2 .

При использовании функции в формуле каждой открывающей скобке необходима закрывающая скобка, чтобы функция работала правильно. Убедитесь, что все скобки являются частью совпадающей пары. Например, формула =ЕСЛИ(B5<0),"Недействительно",B5*1,05) не будет работать, так как закрывающих скобок две, а открывающей всего одна. Правильная формула будет выглядеть так: =ЕСЛИ(B5<0,"Недействительно",B5*1,05) .

Функции Excel имеют аргументы — значения, которые необходимо указать для работы функции. Только несколько функций (например, PI или TODAY) не принимают аргументов. Проверьте синтаксис формулы, который появляется, когда вы начинаете вводить функцию, чтобы убедиться, что у функции есть необходимые аргументы.

Например, функция ПРОПИСНЫЕ принимает в качестве аргумента только одну строку текста или ссылку на ячейку: =ПРОПИСН(«привет») или =ПРОПИСН(C2)

Примечание:  Вы увидите аргументы функции, перечисленные на плавающей панели инструментов ссылки на функцию под формулой, когда вы ее вводите.

Кроме того, некоторые функции, такие как SUM, требуют только числовых аргументов, в то время как другие функции, такие как REPLACE, требуют текстового значения по крайней мере для одного из своих аргументов. Если вы используете неправильный тип данных, функции могут возвращать неожиданные результаты или отображать ошибку #ЗНАЧ! ошибка.

Если вам нужно быстро просмотреть синтаксис определенной функции, см. список функций Excel (по категориям).

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

Если вы используете форматированные числа в аргументах, вы получите неожиданные результаты вычислений, но вы также можете увидеть ошибку #ЧИСЛО! ошибка. Например, если ввести формулу =ABS(-2,134) , чтобы найти абсолютное значение -2134, Excel отобразит ошибку #ЧИСЛО! ошибка, потому что функция ABS принимает только один аргумент и видит -2 и 134 как отдельные аргументы.

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

Ваша формула может не возвращать ожидаемые результаты, если тип данных ячейки нельзя использовать в вычислениях. Например, если вы введете простую формулу =2+3 в ячейку, отформатированную как текст, Excel не сможет вычислить введенные вами данные. Все, что вы увидите в камере, это =2+3 . Чтобы исправить это, измените тип данных ячейки с Text на General следующим образом:

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

2. Выберите Домашний и щелкните стрелку, чтобы развернуть группу Номер или Формат номера (или нажмите Ctrl + 1 ). Затем выберите General .

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

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

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

Однако, если вы используете ссылки на ячейки, Excel вернет #ИМЯ? ошибка.

Если вы создаете формулу, содержащую текст, заключите текст в кавычки.

Например, формула =»Сегодня» & ТЕКСТ(СЕГОДНЯ(),»дддд, мммм дд») объединяет текст «Сегодня» с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает что-то вроде Сегодня понедельник, 30 мая .

В формуле «Сегодня» есть пробел перед завершающей кавычкой, чтобы обеспечить пробел между словами «Сегодня» и «Понедельник, 30 мая». Без кавычек вокруг текста в формуле может отображаться #ИМЯ? ошибка.

В формуле можно комбинировать (или вкладывать) до 64 уровней функций.

Например, формула =ЕСЛИ(КОРЕНЬ(ПИ())<2,"Меньше двух!","Больше двух!") имеет 3 уровня функций; функция PI вложена в функцию SQRT, которая, в свою очередь, вложена в функцию IF.

Если вы вводите ссылку на значения или ячейки на другом листе, а имя этого листа содержит небуквенный символ (например, пробел), заключите имя в одинарные кавычки (‘).

Например, чтобы вернуть значение из ячейки D3 на листе с названием «Квартальные данные» в вашей книге, введите: = «Квартальные данные»! D3 . Без кавычек вокруг имени листа формула показывает #ИМЯ? ошибка.

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

При вводе ссылки на значения или ячейки в другой книге укажите имя книги, заключенное в квадратные скобки ([]), за которым следует имя рабочего листа, содержащего значения или ячейки.

Например, чтобы сослаться на ячейки с A1 по A8 на листе «Продажи» в рабочей книге «Операции за второй квартал», открытой в Excel, введите: =[Операции за второй квартал.xlsx]Продажи!A1:A8 . Без квадратных скобок формула показывает #ССЫЛКА! ошибка.

Если книга не открыта в Excel, введите полный путь к файлу.

Например, =ROWS(‘C:\My Documents\[Q2 Operations.xlsx]Sales’!A1:A8) .

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

Совет: Самый простой способ получить путь к другой книге — открыть другую книгу, затем в исходной книге ввести = и использовать Alt+Tab для перехода к другой книге. Выберите любую ячейку на нужном листе, а затем закройте исходную книгу. Ваша формула автоматически обновится, чтобы отобразить полный путь к файлу и имя листа вместе с необходимым синтаксисом. Вы даже можете скопировать и вставить путь и использовать там, где вам это нужно.

Деление ячейки на другую ячейку, которая имеет нулевое (0) значение или вообще не имеет значения, приводит к #DIV/0! ошибка.

Чтобы избежать этой ошибки, вы можете обратиться к ней напрямую и проверить наличие знаменателя. Вы можете использовать: 

=IF(B1,A1/B1,0)

Что говорит ЕСЛИ(B1 существует, затем разделите A1 на B1, иначе верните 0).

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

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

• Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и если она возвращает #ССЫЛКА! ошибка, выберите ячейку с #ССЫЛКА! ошибка. В строке формул выберите #ССЫЛКА! и удалите его. Затем снова введите диапазон для формулы.

• Если определенное имя отсутствует, а формула, которая ссылается на это имя, возвращает #ИМЯ? ошибки, определите новое имя, которое ссылается на нужный вам диапазон, или измените формулу, чтобы она ссылалась непосредственно на диапазон ячеек (например, A2:D8).

• Если рабочий лист отсутствует, а формула, ссылающаяся на него, возвращает ошибку #ССЫЛКА! ошибка, исправить это, к сожалению, невозможно — удаленный рабочий лист невозможно восстановить.

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

  Например, если ваша формула =[Book1.xlsx]Sheet1′!A1 и у вас больше нет Book1.xlsx, значения, указанные в этой книге, остаются доступными. Однако если вы отредактируете и сохраните формулу, которая ссылается на эту книгу, Excel отобразит диалоговое окно Update Values ​​ и предложит ввести имя файла. Выберите Cancel , а затем убедитесь, что эти данные не потеряны, заменив формулы, которые ссылаются на отсутствующую книгу, результатами формулы.

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

Например, может потребоваться скопировать результирующее значение формулы в ячейку на другом листе. Или вы можете захотеть удалить значения, которые вы использовали в формуле, после того, как вы скопировали полученное значение в другую ячейку на листе. Оба этих действия приводят к появлению в целевой ячейке ошибки недопустимой ссылки на ячейку (#ССЫЛКА!), поскольку на ячейки, содержащие значения, которые вы использовали в формуле, больше нельзя ссылаться.

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

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

2. На вкладке Главная в группе Буфер обмена выберите Копировать .

   Сочетание клавиш: нажмите CTRL+C.

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

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

4. На вкладке Главная в группе Буфер обмена выберите Вставить , а затем выберите Вставить значения или нажмите Alt > E > S > V > введите для Windows или Option > Command > V > V > введите на Mac.

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

1. Выберите формулу, которую вы хотите вычислить.

2. Выберите  Формулы > Оценить формулу .

3. org/ListItem»>

   Выберите  Оцените , чтобы проверить значение подчеркнутой ссылки. Результат оценки выделен курсивом.

4. Если подчеркнутая часть формулы является ссылкой на другую формулу, выберите Step In , чтобы показать другую формулу в поле Evaluation . Выберите Step Out , чтобы вернуться к предыдущей ячейке и формуле.

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

5. Продолжайте, пока не будет оценена каждая часть формулы.

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

   Примечания:

   • Некоторые части функций ЕСЛИ и ВЫБОР не будут оцениваться, и в поле Оценка может появиться ошибка #N/A .

   • Пустые ссылки отображаются как нулевые значения (0) в поле Оценка .

   • Некоторые функции пересчитываются при каждом изменении листа. Эти функции, включая функции СЛЧИС, ПЛОЩАДИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, СТРОКИ, КОЛОННЫ, СЕЙЧАС, СЕГОДНЯ и СЛУЧМЕЖДУ, могут привести к тому, что в диалоговом окне Оценить формулу будут отображаться результаты, которые отличаются от фактических результатов в ячейка на рабочем листе.