Что такое формула электронной таблицы

Обновлено: 20.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Вы можете создать простую формулу для сложения, вычитания, умножения или деления значений на листе. Простые формулы всегда начинаются со знака равенства (=), за которым следуют константы, представляющие собой числовые значения, и операторы вычисления, такие как знаки плюс (+), минус (-), звездочка (*) или косая черта (/).

Давайте рассмотрим пример простой формулы.

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

Введите = (знак равенства), а затем константы и операторы (до 8192 символов), которые вы хотите использовать в расчетах.

Для нашего примера введите =1+1.

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

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

Нажмите Enter (Windows) или Return (Mac).

Возьмем еще один вариант простой формулы. Введите =5+2*3 в другую ячейку и нажмите Enter или Return. Excel умножает два последних числа и добавляет к результату первое число.

Использовать автосумму

Вы можете использовать автосумму для быстрого суммирования столбца, строки или чисел. Выберите ячейку рядом с числами, которые вы хотите суммировать, нажмите «Автосумма» на вкладке «Главная», нажмите «Ввод» (Windows) или «Return» (Mac). Готово!

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

Примечание. Вы также можете ввести ALT+= (Windows) или ALT++= (Mac) в ячейку, и Excel автоматически вставит функцию СУММ.

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

Нажмите Enter, чтобы отобразить результат (95,94) в ячейке B7. Вы также можете увидеть формулу в строке формул в верхней части окна Excel.

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

После создания формулы вы можете скопировать ее в другие ячейки вместо того, чтобы вводить ее снова и снова. Например, если вы скопируете формулу из ячейки B7 в ячейку C7, формула в ячейке C7 автоматически подстроится под новое местоположение и вычислит числа в ячейках C3:C6.

Вы также можете использовать автосумму для нескольких ячеек одновременно. Например, вы можете выделить ячейки B7 и C7, нажать "Автосумма" и одновременно подвести итоги по обоим столбцам.

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

Примечание. Чтобы формулы отображали результаты, выделите их, нажмите F2, а затем нажмите Enter (Windows) или Return (Mac).

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

Символы

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

  • = : сообщает электронной таблице, что вы пишете формулу. Это первое, что должно быть в ячейке с формулой. (ПРИМЕЧАНИЕ. В электронной таблице предполагается, что все, начинающееся с символа «=», является формулой… поэтому будьте осторожны при ее использовании!)
  • + : добавить
  • - : вычесть
  • * : умножить (на калькуляторе это будет "x")
  • / : разделить (на калькуляторе это будет «÷»)

Совет. Расположите символы в правильном порядке

Стоит помнить, что применяются основные математические правила порядка функций. Например, формула =3+5*2 даст вам 13, а НЕ 16. Если вы не знаете почему или не можете вспомнить правила, ознакомьтесь с этим вводным введением. Если вы хотите изменить порядок функций, вам понадобятся круглые скобки: формулы внутри круглых скобок будут оцениваться перед любой другой формулой. Если вы хотите, чтобы приведенная выше формула дала 16, вам нужно ввести: =(3+5)*2

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

Что делать, если вы хотите добавить больше номеров? Вы всегда можете добавить их вручную, используя +, или вы можете использовать формулу SUM для суммирования всех значений в заданном диапазоне. Давайте попробуем подсчитать, сколько яблок, слив и всего фруктов мы продали за неделю: перейдите в ячейку B7 и введите =СУММ(A2:A6), это добавит количество яблок.

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

Подсчитаем общее количество проданных фруктов.

  1. Получите пример данных и создайте копию.
  2. Для начала перейдите к первой строке.
  3. Каждая формула в электронной таблице начинается с =
  4. Введите = и выберите первую ячейку, которую хотите добавить. Заметили, как в формуле появляется ссылка на ячейку?
  5. теперь введите + и выберите вторую ячейку, которую хотите добавить
  6. Нажмите Enter или Tab .
  7. Формула исчезает и заменяется значением.
  8. Попробуйте изменить число в одной из исходных ячеек (яблоки или сливы). Вы увидите, что общее значение обновится автоматически.
  9. Вы можете вводить каждую формулу по отдельности, но также можно вырезать и вставлять или перетаскивать формулы в диапазоне ячеек.
  10. Скопируйте только что написанную формулу (используя Ctrl + C) и вставьте ее в ячейку ниже (используя Ctrl + V). Вы получите сумму двух чисел в строке ниже.
  11. Вы также можете нажать правый нижний угол ячейки (синий квадрат) и перетащить формулу в нижнюю часть столбца. Следите за обновлением столбца «всего». Похоже на волшебство!

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

    Взгляните на приведенный выше пример — он точно такой же, за исключением того, что вы используете - вместо + . Легкий! Найдите разницу между двумя столбцами в приведенном выше примере.

    Пошаговое руководство: умножение и деление

    Подведение итогов по математике. Если у вас есть процент и связанное с ним значение, вы можете вычислить значение процента. экв. скажем, 25% людей в городе с населением 1000 человек моложе 15 лет. Вы можете рассчитать количество жителей, используя следующую формулу:

    В электронной таблице приведенный выше ответ выражается просто: = 25 * 1000/100 (более подробное объяснение процентов см. на BBC Skillswise )

    А теперь более сложный пример:

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

    1. В наших исходных данных есть три столбца, относящиеся к расходам на здравоохранение; «расходы на здравоохранение (частные)», «расходы на здравоохранение (государственные)» и «расходы на здравоохранение (всего)». Поэтому нам нужно добавить три новых столбца справа от электронной таблицы, чтобы выполнить ваши расчеты. Дайте каждому заголовок; возможно, «расходы на здравоохранение (частные) в долларах США» и т. д.
    2. В исходных данных государственные, частные и общие расходы на здравоохранение выражены в процентах от ВВП. ВВП уже указан в долларах. Вычислить расходы в долларах США из этих двух фактов — это всего лишь один шаг. Мы хотим рассчитать, сколько денег (в долларах США) тратится на здравоохранение в каждой стране и в год.
    3. Давайте начнем с самой первой полной строки (NB: найдите пробел! у нас нет данных по ВВП Афганистана в 2000 году. Просто имейте это в виду (более подробно мы поговорим о пробелах в данные позже). Первая полная строка — Афганистан в 2001 г.

    В 2001 году ВВП Афганистана составлял 2 461 666 315 долларов США. Их частные расходы на здравоохранение составили 6,009337744% от этой суммы. Итак, расчет, который вам нужно сделать, это 2461666315 * 6,009337744 / 100

    Пошаговое руководство: копирование формул сбоку

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

    По-прежнему используя данные Всемирного банка, попробуйте просто перетащить формулу ячейки. Вы видите проблему? Электронная таблица автоматически перемещает все ячейки на один столбец вправо. Таким образом, если раньше у нас было: =E3*H3/100, то теперь мы имеем =F3*I3/100 … но ВВП по-прежнему находится в столбце E, поэтому эта формула не та, которая нам нужна.

    Чтобы «исправить» столбец или строку, все, что вам нужно сделать, это добавить «$» перед разделом, который вы хотите исправить. Итак, если вы адаптируете исходную формулу к… =$E3*H3/100… вы сможете без проблем перетащить ее вправо.

    Совет. Поначалу может быть немного сложно привыкнуть к команде $. Если вы впервые сталкиваетесь с этим, мы предлагаем вам потратить некоторое время на то, чтобы поиграть и посмотреть, на что он способен. Вернитесь к своей «игровой» таблице, придумайте числа и поэкспериментируйте! Попробуйте, например, =$B2*C2 vs =B$2*C2 , перетащите его и посмотрите, какая разница. Лучший способ освоиться с формулами — это использовать их!

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

    Пошаговое руководство: минимальные и максимальные значения

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

    1. Откройте новый лист. Для этого нажмите «+» в левом нижнем углу
    2. Оставьте первый столбец в первой строке пустым, во втором столбце введите =, чтобы сообщить электронной таблице, что вы будете использовать формулу.
    3. Вернитесь на лист с набором данных Всемирного банка.
    4. Выберите первый столбец с числовыми данными на листе, где хранятся ваши данные.
    5. Нажмите Enter, и вы увидите имя на первом листе: magic. Почему мы делаем это именно так, а не просто копируем и вставляем? Это автоматически изменит заголовки, если вы измените свои заголовки (например, вы переместите столбцы или переименуете вещи).
    6. Теперь первый столбец будет тем, что вы вычисляете: введите Минимум в первом столбце второй строки (A2) для минимального значения.
    7. В ячейке рядом с ней введите =MIN((MIN – это формула минимума)
    8. Вернитесь к другому листу, чтобы выбрать первый столбец с числовыми данными. Чтобы выбрать весь столбец, нажмите на серую область с буквой столбца.
    9. Закройте скобки, введя ).
    10. Теперь вы должны увидеть минимальное значение в этом поле.
    11. Теперь сделайте то же самое для Максимума в третьей строке. Когда вы закончите, просто отметьте три значения во второй строке (формула для максимума =max() )
    12. Видите синий квадрат в правом нижнем углу? Возьмите его и потяните его правильно. Отпустите его, и если у вас все еще нет всех столбцов, продолжайте, пока не получите все значения.
    13. Таким образом вы создали таблицу с минимальными и максимальными значениями каждого из столбцов.

      14. Пошаговое руководство: работа с пустыми ячейками

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

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

      1. Чтобы справиться с пустыми ячейками, мы должны исправить части наших расчетных формул в таблице данных Всемирного банка.
      2. Для начала создайте макет электронной таблицы, чтобы поиграть с данными. Скопируйте в него первые несколько строк набора данных Всемирного банка, чтобы вы могли начать. Чтобы проверить наши формулы, попробуйте удалить значения в некоторых строках.
      3. У нас есть недостающая проблема прямо в первом значении: ВВП Афганистана отсутствует за 2000 год.
      4. Подумайте о нашей цели. Чего мы хотим добиться: если какое-либо из значений, которые мы умножаем (в данном случае ВВП или расходы на здравоохранение), не является числом (вероятно, потому, что значение отсутствует), мы не хотим отображать итог.
      5. Иными словами, расчет в электронной таблице должен выполняться только при наличии значения как ВВП, так и расходов на здравоохранение; в противном случае ячейка должна оставаться пустой.
      6. Формула для выражения этого условия: «ЕСЛИ». (Вы можете найти обзор подобных формул в справке Google Doc.)
      7. Формула предлагает нам ввести три элемента: (1) условие, (2) значение, если условие верно, (3) значение, если условие ложно. =ЕСЛИ(Условие, Значение, если условие истинно, Значение, если условие ложно)
      8. В нашем случае мы знаем части (2) и (3). (2) это формула, которую мы использовали выше, это расчет, который мы хотим выполнить, если оба значения присутствуют в электронной таблице. =ЕСЛИ(Условие, $E3*H3/100, Значение, если условие ложно)
      9. (3) является пустым — если чисел нет, мы не хотим ничего отображать, поэтому мы вообще ничего не заполняем это значение. =ЕСЛИ(Условие, $E3*H3/100,)
      10. Теперь нам просто нужно решить (1), условие. =ЕСЛИ(Условие, $E3*G3/100,)
      11. Помните, что мы хотим, чтобы ОБА значения ВВП и расходов на здравоохранение были числовыми. Формула для определения того, является ли ячейка числом: ISNUMBER .
      12. Это еще одна из тех маленьких формул, с которыми стоит попробовать поиграть! Если вы наберете =ISNUMBER(F2) и F2 будет пустым полем, будет указано FALSE . Если есть число, оно скажет TRUE. Удобно не правда ли?
      13. Нам нужна формула, которая будет рассчитываться только в том случае, если и ВВП, и и расходы на здравоохранение являются фактическими цифрами.
      14. Нам нужно объединить результаты IЧИСЛО(ВВП) и IЧИСЛО(расходы на здравоохранение). Для этого используется формула AND. Это просто скажет ИСТИНА, если оба они ИСТИНА (т.е.оба числа) или FALSE, если один или оба из них имеют значение FALSE.
      15. Это именно то, что нам нужно. Таким образом, наше условие будет таким: И(ЧИСЛО(ВВП),ЧИСЛО(расходы на здравоохранение))

      или, чтобы использовать наши ячейки из предыдущего AND(ISNUMBER($E3),ISNUMBER(H3))

      Если вы посмотрите на данные, то быстро обнаружите, что страны с большей численностью населения тратят на здравоохранение больше, чем страны с меньшей численностью населения. Интуитивно не так. Так как же сравнивать страны более непосредственно? Разбейте это на расходы на здравоохранение на человека!. Этот шаг называется нормализацией и часто используется при сравнении различных объектов, например стран и т. д.

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

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

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

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

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

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

      Также можно нажать кнопку Формула на панели формул. В приведенном ниже примере показано использование формулы в ячейке C15 для вычисления суммы ячеек D11 и D13.

      При нажатии клавиши Enter электронная таблица вычисляет значение ячейки C15 и отображает результат:

      Практический пример

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

      Чтобы получить цену с учетом налога для статьи 34FR453, прибавьте сумму налога к стоимости до уплаты налогов, то есть стоимость до уплаты налогов, умноженную на НДС (в данном случае 19,6%):

      В приведенном выше примере

      • сумма TI находится в E6,
      • количество BT находится в D6,
      • сумма НДС указана в D11,

      что дает следующую формулу:

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

      Поэтому электронная таблица находится в режиме ввода формул и:

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

      Во втором примере нажатие на ячейку D6 автоматически добавит ее имя в строку формул. Еще один щелчок по этой ячейке отобразит D6+D6, поскольку сложение является операцией по умолчанию в электронной таблице. Нажмите кнопку *, чтобы выбрать умножение, а затем выберите ячейку D11.

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

      Как копировать формулы

      Представьте, что мы хотим скопировать эту формулу и использовать ее для других статей. Выберите формулу в (ячейка E6), скопируйте ее и выберите блок ячеек от E7 до E9 (написано E7: E9) и вставьте ранее скопированную формулу. Вот результат:

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

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

      Это связано с тем, что ранее введенные ссылки были относительными ссылками.
      Поэтому электронная таблица выполняет автоматическое смещение ссылок на ячейки при копировании формулы. Таким образом, чтобы электронная таблица не смещала ячейку со значением НДС, используйте абсолютную ссылку ($D$11). Повторим формулу:

      Теперь электронная таблица дает согласованный результат:

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

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

      Давайте повторим приведенный выше пример. Чтобы автоматически рассчитать общую сумму заказов до уплаты налогов и общую сумму заказов TI, используйте функцию Sum(). Для этого выберите ячейку, в которой нужно отобразить сумму, и введите формулу (начиная со знака =) с помощью функции Sum(). Есть много способов ввести функцию Sum():

      • введя его вручную в строку формул
      • используя кнопку function, которая позволяет выбрать функцию из списка:

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

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

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

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

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

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

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

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

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

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

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

      Посмотреть формулу

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

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

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

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

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

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

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

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

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

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

      Подробнее о формулах

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

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

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

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

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

      <р>3. Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

      <р>4. Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.

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

      Стили ссылок A1

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

      Для ссылки

      Ячейка в столбце A и строке 10

      Диапазон ячеек в столбце А и строках с 10 по 20

      Диапазон ячеек в строке 15 и столбцах с B по E

      Все ячейки в строке 5

      Все ячейки в строках с 5 по 10

      Все ячейки в столбце H

      Все ячейки в столбцах с H по J

      Диапазон ячеек в столбцах от A до E и строках с 10 по 20

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

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

      <р>1. Относится к рабочему листу под названием "Маркетинг"

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

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

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

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

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

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

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

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

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

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

      Трехмерный эталонный стиль

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

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

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

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

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

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

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

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

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

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

      Стиль ссылок R1C1

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

      Относительная ссылка на ячейку двумя строками выше и в том же столбце

      Относительная ссылка на ячейку на две строки вниз и на два столбца вправо

      Абсолютная ссылка на ячейку во второй строке и во втором столбце

      Относительная ссылка на всю строку над активной ячейкой

      Абсолютная ссылка на текущую строку

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

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

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

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

      Читайте также: