Формулы в xl: 15 формул в Excel, которые помогут в работе

Как работать с формулами в Microsoft Excel: инструкция для новичков

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

Я разберу основы работы с формулами и полезные «фишки», способные упростить процесс взаимодействия с таблицами.

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

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

1. Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.

2. Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».

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

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

   «Справка по этой функции», если хотите открыть страницу о ней в официальной документации Microsoft.

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

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

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

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

Подписаться

Вставка функции в таблицу

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

Выделите любую пустую ячейку и напишите в ней знак «=», объявив тем самым функцию. В качестве значения можете взять любое число, написать номер ячейки (используя буквенные и цифровые значения слева и сверху) либо выделить ее левой кнопкой мыши. На следующем скриншоте вы видите простой пример =B2*C2, то есть результатом функции будет перемножение указанных ячеек друг на друга.

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

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

Растягивание функций и обозначение константы

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

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

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

=B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

• $В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

• B$2 – неизменна строка.

• $B2 – константа касается только столбца.

Построение графиков функций

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

Читайте также: Как построить график функции в Excel

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

Excel 9. Формулы – Эффективная работа в MS Office

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

Список вкладок ленты «Формула» я дала на уроке «Excel 3. Введение в Excel».

По окончании этого урока вы сможете:

1. Перечислить и показать арифметические операторы для создания формулы
2. Вводить вручную формулы
3. Использовать встроенные функции
4. Выбирать нужные функции из контекстного меню

1. Арифметические операторы

Все эти символы вводятся с клавиатуры. Рассмотрим на примере.

Шаг 1. Щелкаем по ячейке А1, куда будет введена формула.

Шаг 2. Вводим формулу «=75+32+44»:

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

Шаг 3. Нажимаем Enter. Активной станет ячейка А2.

Шаг 4. Делаем активной ячейку А1.

Понять и запомнить!

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

Алгоритм ввода формулы при помощи арифметических операторов:

1. Щелкаем на ячейке, в которой должны появиться результаты формулы.
2. Вводим сначала знак равенства (=), а потом − первое значение.
3. Вводим арифметический оператор, а затем − второе значение.
4. После того, как вся формула введена, нажимаем Enter, чтобы завершить ввод.

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

При расчете плотности вязания мне часто приходится вычислять пропорцию:

Размер	Количество петель
10 см образца	18 петель
50 см размер изделия	Сколько нужно набрать петель?

То есть формула будет выглядеть так: «=18*50/10». Это в том случае, если мы решим вводить её вручную.

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

Шаг 2. Вводим сначала знак равенства «=», а затем щелкаем на ячейке B2, которая содержит первое значение 18:

Шаг 3. Вводим арифметический оператор «*», а потом щелкаем на ячейке A3, которая содержит второе значение 50:

Шаг 4. Вводим арифметический оператор «/», а потом щелкаем на ячейке А2, которая содержит третье значение 10:

Шаг 5. Завершаем ввод, нажав клавишу Enter.

Вы можете возразить: да с калькулятором проще! Но у меня давно изготовлен шаблон таблицы Excel, в котором прописаны все формулы для расчета вязаного изделия (а формул много!).

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

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

Шаг 1. Отмечаем ячейку B3 и открываем диалоговое окно «Аргументы функции» (лента Формулы → группа команд Библиотека функций → функция ПРОИЗВЕД из списка выпадающего меню):

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

Знакомимся с диалоговым окном «Аргументы функции»:

1. Поля, в которые вносятся адреса ячеек. По умолчанию Число 1 имеет адрес ячейки, которая находится как раз над ячейкой с будущей формулой. Полей всего два.
2. Значение произведения. В данном случае значение ячейки B2
3. Пояснение к формуле. Советую на первых порах читать внимательно. Слово «Возвращает» всегда немного напрягало. Все-таки по-русски правильно было сказать: результат вычислений. А дальше пояснение, что перемножать можно до 255 чисел. Полей, напомню, всего два.
4. Значение произведения. Зачем дублировать – загадка.

Шаг 2. Не выходя из диалогового окна «Аргументы функции», отмечаем ячейку B2 (на всякий случай) и щелкаем ЛМ в поле «Число 2»

Шаг 3. Не выходя из диалогового окна «Аргументы функции», отмечаем ячейку A2:

Вот и появилось поле «Число 3». Контролируем полученное значение. Уже 900.

Дальше надо решить проблему с делением. Но функции ДЕЛЕНИЕ нет! Кстати, и функции ВЫЧИТАНИЕ тоже нет. На помощь придёт знание арифметики. Деление – это умножение на дробь «1/а».

Шаг 4. Вводим с клавиатуры в поле «Число 3» − ««1/» и, не выходя из диалогового окна «Аргументы функции», отмечаем ячейку A1:

Все время контролируем полученное значение. Уже 90. А заодно внимательно посмотрим на строку формул. Формула записалась с соблюдением синтаксиса Excel.

Нажимаем ОК:

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

Изменяем значение в любой ячейке, которая участвует в формуле, например в ячейке B2:

Формула вычислила новое значение

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

Понять и запомнить!

1. При делении умножаем на дробь «1/адрес ячейки» 2. При вычитании прибавляем отрицательное число «–адрес ячейки»

4. Формула, записанная вручную

Удалите в ячейке В3 формулу. Записываем формулу заново.

Шаг 1. Вводим знак «=» с клавиатуры и набираем «про»:

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

Шаг 2. Двойной щелчок ЛМ по названию функции «ПРОИЗВЕД» и дальше повторяем действия по вводу адресов ячеек, не забывая разделять адреса знаком «точка с запятой ;»:

Если вы дружите с английской раскладкой клавиатуры, то можете набрать формулу вручную с клавиатуры. Если вы наберете «А» или «В» в русской раскладке, то Excel вас просто не поймёт, формула не заработает, и вы увидите:

При щелчке по зеленому треугольнику ЛМ появится значок с восклицательным знаком, а уже при щелчке ЛМ по этому значку появится выпадающее меню:

Разбираться с этим меню вы будете весь световой день, но ошибку не найдёте. Так что лучше не делать ошибок в синтаксисе формул. Самое лучшее в этом случае щелкнуть ЛМ по строке формул:

Правильно введённые адреса акцентированы цветом, а ошибочный адрес (русская буква «А») – чёрным цветом. Если вы исправите русскую букву «А» на английскую, то ошибка исчезнет.

5. Суммирование строк и столбцов

Шаг 1. Создаем ряд чисел в столбце и делаем активной ячейку суммирования.

Шаг 2. Повторяем Шаги 1÷2 из пункта 4:

1. Знак «=» и набираем «сум»
2. Выбираем функцию «СУММА» из предложенного списка

Шаг 3. Щелкаем по первой ячейке ряда чисел, которые следует просуммировать, и протягиваем курсор вниз, то есть выделяем диапазон ячеек.

Шаг 1. Нажимаем Enter

Обратите внимание, что диапазон ячеек обозначен двоеточием «:» (Excel 5).

Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная → группа команд Редактирование → команда Автосумма). Эта кнопка продублирована на ленте Формулы.

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

Щелкаем ЛМ на первой пустой ячейке, находящейся ниже столбца или справа от строки, содержимое в ячейках которых нужно сложить.

Шаг 1. Нажимаем кнопку Автосумма на ленте Главная:

Диапазон ячеек, который суммируется, будет обведен «бегущей» границей, а в строке формул появится формула «=СУММ(С1:С4)».

Шаг 2. Нажимаем Enter

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

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

Шаг 1. Повторяем Шаги 1÷2 из пункта 4:

1. Знак «=» и набираем «сум»
2. Выбираем функцию «СУММА» из предложенного списка

Шаг 7. Выбираем первый диапазон (1), затем второй диапазон (2) простым перетаскиванием ЛМ:

6. Далеко идущие выводы

1. Формула введенная вручную

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

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

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

Теперь вы можете:

1. Перечислить и показать арифметические операторы для создания формулы
2. Вводить вручную формулы
3. Использовать встроенные функции
4. Выбирать нужные функции из контекстного меню

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

Обзор формул в Excel

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

Важно: Результаты расчетов формул и некоторых функций листа Excel могут немного отличаться на ПК с Windows с архитектурой x86 или x86-64 и ПК с Windows RT с архитектурой ARM. Узнайте больше о различиях.

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

Создайте формулу, которая ссылается на значения в других ячейках

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

2. Введите знак равенства =.

   Примечание. Формулы в Excel всегда начинаются со знака равенства.

3. org/ListItem»>

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

4. Введите оператора. Например, – для вычитания.

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

6. Нажмите Enter. Результат расчета появится в ячейке с формулой.

См. формулу

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

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

Введите формулу, содержащую встроенную функцию

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

2. Введите знак равенства =, а затем введите функцию. Например, =СУММ для получения общего объема продаж.

3. Введите открывающую скобку (.

4. org/ListItem»>

   Выберите диапазон ячеек и введите закрывающую скобку).

5. Нажмите Enter, чтобы получить результат.

Загрузите учебник по формулам

Мы подготовили книгу «Начало работы с формулами», которую вы можете скачать. Если вы новичок в Excel или даже имеете некоторый опыт работы с ним, вы можете пройтись по наиболее распространенным формулам Excel в этом туре. С реальными примерами и полезными визуальными эффектами вы сможете суммировать, считать, усреднять и выполнять ВПР как профессионал.

Подробные формулы

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

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

Части формулы

1. Функции : Функция PI() возвращает значение числа пи: 3,142…

2. Ссылки : A2 возвращает значение в ячейке A2.

3. Константы 9Оператор (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.

Константа — это значение, которое не вычисляется; он всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Квартальные доходы» — все это константы. Выражение или значение, полученное из выражения, не является константой. Если вы используете в формуле константы вместо ссылок на ячейки (например, =30+70+110), результат изменится, только если вы измените формулу. Как правило, лучше всего размещать константы в отдельных ячейках, где их можно легко изменить при необходимости, а затем ссылаться на эти ячейки в формулах.

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

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

• Эталонный стиль A1

  По умолчанию Excel использует стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и цифры называются заголовками строк и столбцов. Чтобы сослаться на ячейку, введите букву столбца, а затем номер строки. Например, B2 относится к ячейке на пересечении столбца B и строки 2.

  См.	Использовать
  Ячейка в столбце А и строке 10	А10
  Диапазон ячеек в столбце А и строках с 10 по 20	А10:А20
  Диапазон ячеек в строке 15 и столбцах от B до E	В15:Е15
  Все ячейки в строке 5	5:5
  Все ячейки в строках с 5 по 10	5:10
  Все ячейки в столбце H	Ч:Ч
  Все ячейки в столбцах с H по J	Х:Д
  Диапазон ячеек в столбцах от A до E и строках с 10 по 20	А10:Е20

• org/ListItem»>

  Создание ссылки на ячейку или диапазон ячеек на другом листе в той же книге

  В следующем примере функция СРЗНАЧ вычисляет среднее значение для диапазона B1:B10 на листе с именем Marketing в той же книге.

  1. Относится к рабочему листу под названием «Маркетинг 9».0003

  2. Относится к диапазону ячеек от B1 до B10

  3. Восклицательный знак (!) отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

  Примечание.  Если указанный лист содержит пробелы или числа, необходимо добавить апострофы (‘) до и после имени листа, например =’123′!A1 или =’Доход за январь’!A1.

• Разница между абсолютными, относительными и смешанными ссылками

  org/ItemList»>

  1. Относительные ссылки     Относительная ссылка на ячейку в формуле, например A1, основана на относительном положении ячейки, содержащей формулу, и ячейки, на которую ссылается ссылка. Если положение ячейки, содержащей формулу, изменяется, ссылка изменяется. Если вы скопируете или заполните формулу между строками или столбцами, ссылка будет автоматически скорректирована. По умолчанию в новых формулах используются относительные ссылки. Например, если вы скопируете или заполните относительную ссылку из ячейки B2 в ячейку B3, она автоматически изменится с =A1 на =A2.

     Скопированная формула с относительной ссылкой

  2. Абсолютные ссылки     Абсолютная ссылка на ячейку в формуле, например $A$1, всегда указывает на ячейку в определенном месте. Если положение ячейки, содержащей формулу, изменяется, абсолютная ссылка остается прежней. Если вы скопируете или заполните формулу между строками или столбцами, абсолютная ссылка не изменится. По умолчанию в новых формулах используются относительные ссылки, поэтому вам может потребоваться переключить их на абсолютные ссылки. Например, если вы скопируете или заполните абсолютную ссылку из ячейки B2 в ячейку B3, она останется одинаковой в обеих ячейках: =$A$1.

     Скопированная формула с абсолютной ссылкой

  3. Смешанные ссылки     Смешанная ссылка имеет либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку принимает форму A$1, B$1 и т.  д. Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Если вы копируете или заполняете формулу между строками или столбцами, относительная ссылка корректируется автоматически, а абсолютная ссылка не корректируется. Например, если вы скопируете или заполните смешанную ссылку из ячейки A2 в ячейку B3, она изменится с =A$1 на =B$1.

     Скопированная формула со смешанной ссылкой

• Стиль трехмерных ссылок

  Удобная ссылка на несколько листов     Если вы хотите проанализировать данные в одной и той же ячейке или диапазоне ячеек на нескольких листах в книге, используйте трехмерную ссылку. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, которому предшествует диапазон имен рабочих листов. Excel использует все рабочие листы, хранящиеся между начальным и конечным именами ссылки. Например, =СУММ(Лист2:Лист13!B5) складывает все значения, содержащиеся в ячейке B5, на всех листах между листами 2 и 13 включительно.

  • Вы можете использовать трехмерные ссылки для обращения к ячейкам на других листах, для определения имен и создания формул с помощью следующих функций: СУММ, СРЗНАЧ, СРЗНАЧ, СЧЕТ, СЧЕТ, МАКС, МАКС, МИН, МИН, ПРОИЗВ. STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

  • Трехмерные ссылки нельзя использовать в формулах массива.

  • org/ListItem»>

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

  Что происходит при перемещении, копировании, вставке или удалении листов     В следующих примерах объясняется, что происходит при перемещении, копировании, вставке или удалении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для добавления ячеек с A2 по A5 на листах со 2 по 6.

  • Вставка или копирование     Если вы вставляете или копируете листы между Листами2 и Лист6 (конечными точками в этом примере), Excel включает в расчеты все значения в ячейках с A2 по A5 из добавленных листов.

  • org/ListItem»>

    Удалить      Если вы удалите листы между Листами2 и Лист6, Excel удалит их значения из расчета.

  • Переместить     Если вы переместите листы между Листами2 и Лист6 за пределы указанного диапазона листов, Excel удалит их значения из расчета.

  • Перемещение конечной точки     Если вы переместите Лист2 или Лист6 в другое место в той же книге, Excel изменит расчет, чтобы учесть новый диапазон листов между ними.

  • Удалить конечную точку     Если вы удалите Лист2 или Лист6, Excel изменит расчет, чтобы учесть диапазон листов между ними.

• Справочный стиль R1C1

  Вы также можете использовать стиль ссылок, в котором и строки, и столбцы на листе пронумерованы. Справочный стиль R1C1 полезен для вычисления позиций строк и столбцов в макросах. В стиле R1C1 Excel указывает расположение ячейки буквой «R», за которой следует номер строки, и буквой «C», за которой следует номер столбца.

  Артикул	Значение
  Р[-2]С	Относительная ссылка на ячейку двумя строками выше и в том же столбце
  Р[2]К[2]	Относительная ссылка на ячейку на две строки вниз и на два столбца вправо
  Р2С2	Абсолютная ссылка на ячейку во второй строке и во втором столбце
  Р[-1]	Относительная ссылка на всю строку над активной ячейкой
  Р	Абсолютная ссылка на текущую строку

  При записи макроса Excel записывает некоторые команды, используя стиль ссылок R1C1. Например, если вы записываете команду, такую ​​как нажатие кнопки Кнопка AutoSum , чтобы вставить формулу, которая добавляет диапазон ячеек, Excel записывает формулу, используя стиль R1C1, а не стиль A1, ссылки.

  Стиль ссылок R1C1 можно включить или отключить, установив или сняв флажок Стиль ссылок R1C1 в разделе Работа с формулами в категории Формулы диалогового окна Параметры . Чтобы отобразить это диалоговое окно, щелкните вкладку File .

  Верх страницы

Нужна дополнительная помощь?

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

См. также

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

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

Порядок, в котором Excel выполняет операции в формулах

Использование функций и вложенных функций в формулах Excel

Определение и использование имен в формулах

Рекомендации и примеры формул массива

Удалить или удалить формулу

Как избежать неработающих формул

Найдите и исправьте ошибки в формулах

Сочетания клавиш и функциональные клавиши Excel

Функции Excel (по категориям)

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

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

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

Начните с выбора  OK или нажмите ESC , чтобы закрыть сообщение об ошибке.

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

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

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

Excel выдает различные ошибки фунта стерлингов (#), такие как #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? и #NULL!, чтобы указать что-то в вашей формуле не работает должным образом. Например, #ЗНАЧ! ошибка вызвана неправильным форматированием или неподдерживаемыми типами данных в аргументах. Или вы увидите #REF! ошибка, если формула ссылается на ячейки, которые были удалены или заменены другими данными. Рекомендации по устранению неполадок будут отличаться для каждой ошибки.

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

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

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

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

• org/ListItem»>

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

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

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

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

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

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

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

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

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

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

• org/ListItem»>

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

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

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

• org/ListItem»>

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

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

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

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

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

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

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

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

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

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

При использовании функции в формуле каждой открывающей скобке нужна закрывающая скобка, чтобы функция работала правильно. Убедитесь, что все скобки являются частью совпадающей пары. Например, формула =ЕСЛИ(В5<0),"Недействительно",В5*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 , чтобы перевести ячейку в режим редактирования, а затем нажмите . Введите , чтобы принять формулу.

Дата, которую вы вводите в ячейку с цифрой 9.0005 Тип данных 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, введите полный путь к файлу.

Например, =СТРОКИ(‘C:\Мои документы\[Q2 Operations.xlsx]Продажи’!A1:A8) .

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

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

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

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

=IF(B1,A1/B1,0)

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

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

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

• org/ListItem»>

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

• Если определенное имя отсутствует, а формула, которая ссылается на это имя, возвращает #ИМЯ? ошибки, определите новое имя, которое ссылается на нужный вам диапазон, или измените формулу, чтобы она ссылалась непосредственно на диапазон ячеек (например, 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.

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

org/ItemList»>

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

2. Выберите Формулы > Вычислить формулу .

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

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

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

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

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

   Примечания:

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