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

Содержание

—


MS Office 2007: Microsoft Excel



Использование функций


Excel содержит большое количество встроенных функций, которые вы можете использовать в своих формулах. К ним относятся как достаточно распространенные функции, например СУММ, СРЗНАЧ и КОРЕНЬ, так и функции, специально предназначенные для определенных целей, например статистические или инженерные. Функции существенно повышают эффективность формул. Они могут упростить формулы и сделать их понятнее. Более того, в большинстве случаев функции позволяют производить вычисления, которые без них было бы невозможно выполнить. Покажем на примере. Что если вам нужно найти наибольшее значение диапазона ячеек? Это еще один пример того, как без использования функции вы не сможете получить ответ. Например, формула =МАКС (A1: D100) позволяет отыскать наибольшее значение в диапазоне A1: D100.

Значения, передаваемые функции, называются аргументами функции. При записи функции сначала пишется ее имя, а потом в круглых скобках приводится аргумент (или аргументы) функции. Например, формула, вычисляющая квадратный корень из числа 4 будет выглядеть следующим образом: КОРЕНЬ (4).

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

  • функции без аргументов;

  • функции с одним аргументом;

  • функции с фиксированным числом аргументов;

  • функции с неопределенным числом аргументов;

  • функции с необязательными аргументами.

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

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

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

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



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

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

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

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

4. Напечатайте вторую букву Р. Список теперь будет отображать функции и диапазоны, имена которых начинаются на ПР (рис.36).



5. Теперь в списке видно название функции промежуточные . итоги. С помощью клавиш управления курсором перейдите в списке к названию функции ПРОМЕЖУТОЧНЫЕ. итоги и нажмите клавишу Tab. Excel введет выбранное название функции в ячейку и даже напечатает открывающую скобку после имени функции. Кроме того, список теперь будет отображать возможные значения первого аргумента данной функции, как показано ниже на рис.37.



6. С помощью клавиш управления курсором перейдите в списке к названию аргумента СРЗНАЧ, и нажмите клавишу Tab. Excel в качестве первого аргумента вставит число 101, код для вычисления среднего значения.

7. Введите точку с запятой для отделения первого аргумента от следующего.

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



9. В этом списке выделите Выборка, и нажмите клавишу Tab.

10. Введите закрывающую скобку, и завершите ввод клавишей Enter – формула создана

Другой способ вставки функции в формулу заключается в выборе этой функции из группы команд Библиотека функций, расположенной на ленточной вкладке Формулы. Этот способ удобен, если известно к какой группе относится ваша функция. Выберите из интересующей вас категории функций (например, Финансовые, Логические, Текстовые и т.д.), нужную функцию. После этого в открывшемся окне Аргументы функции задайте значения аргументов выбранной функции.

Еще один метод вставки функции в формулу заключается в использовании Мастера функций. Для этого

  • щелкните на кнопке Вставить функцию fx, которая находится в левой части строки формул;

  • или выберите команду Формулы-Библиотек-Вставить функцию fx;

  • или нажмите комбинацию клавиш Shift+F3.

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

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



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

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





Как добавить числа в Excel, используя формулу

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

Инструкции в этой статье применяются к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и Excel для Mac.

Lifewire / Хьюго Лин

Синтаксис формулы Excel

Несколько важных моментов, которые следует помнить о формулах Excel:

  • Формулы в Excel начинаются со знака равенства.
  • Знак равенства вводится в  ячейку,  где появится ответ.
  • Знак добавления в Excel является символом плюса.
  • Формулы заполняются нажатием клавиши Enter.

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

В приведенных ниже примерах данных строки 2–4 используют формулу, расположенную в столбце C, для добавления данных в столбцы A и B. В строке 2 результат вычисляется с использованием формулы сложения = 5 + 5.

Строки 3 и 4 показывают, как лучше сначала ввести данные в ячейки таблицы, а затем использовать адреса или ссылки этих ячеек в формуле. Например, = A3 + B3.

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

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

Введите ссылки на ячейки с помощью точки и нажмите

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

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

Используйте формулу сложения в Excel

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

Вот как создать формулу сложения:

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

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

  3. Введите знак плюс в формулу после A3 .

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

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

  6. Ответ 20 появляется в ячейке C3.

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

Изменить формулу

Чтобы изменить или исправить формулу, выберите один из двух вариантов:

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

Создать более сложные формулы

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

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

Создать последовательность Фибоначчи

Последовательность Фибоначчи, созданная итальянским математиком 12-го века Леонардо Пизано, образует непрерывную серию возрастающих чисел. Эти серии используются для математического объяснения различных закономерностей, встречающихся в природе, таких как:

  • Спиральная форма ракушек.
  • Расположение листьев на ветке дерева.
  • Картина размножения пчел.

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

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

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

Чтобы создать ряд Фибоначчи, показанный в примере:

  1. В ячейке A1 введите 0 (ноль) и нажмите Enter .

  2. В ячейке A2 введите 1 и нажмите Enter .

  3. В ячейке A3 введите формулу = A1 + A2 и нажмите Enter .

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

  5. Наведите указатель мыши на маркер заливки (это точка в правом нижнем углу ячейки A3). Указатель меняется на черный знак плюс, когда он находится над маркером заливки.

  6. Перетащите ручку заполнения вниз в ячейку A19.

  7. Ячейка A19 содержит номер 2584.

Excel Функция ДВССЫЛ Примеры и видео

Функция Microsoft Excel ДВССЫЛ возвращает ссылку на диапазон, и я использую ее для создания зависимых раскрывающихся списков в моей книге Excel, чтобы показать элементы на основе значения другой ячейки. Вы также можете использовать НЕПРЯМОЙ
для создания ссылки, которая не изменится, если строки или столбцы
вставляется в рабочий лист, где вы используете функцию СУММ.

Автор: Дебра Далглиш

Видео: ДВССЫЛ Функция

Как работает функция ДВССЫЛ 

Зависимые раскрывающиеся списки 

— Видео: Зависимые раскрывающиеся списки

Примеры из видео

— Блокировка ссылки на ячейку 

— См. другой лист

— Обратитесь к именованному диапазону 

Примеры аргумента Ref_Text

Получить файл примера

Видео: примеры функций ДВССЫЛ

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

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

Хронология видео

  • 00:00 Введение
  • 00:14 Заблокировать ссылку на ячейку
  • 01:27 См. именованный диапазон
  • 02:50 См. специальный лист
  • 04:22 Заблокировать массив чисел

Спасибо Дэйву Петерсону за его вклад в эту страницу.

Как работает функция ДВССЫЛ

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

На этом снимке экрана в ячейке B2 есть раскрывающийся список, в котором можно выбрать «Факт» или «Бюджет». После того, как вы сделаете выбор, итог для этого типа появится в ячейке B3.

INDIRECT Аргументы синтаксиса функции

Синтаксис функции ДВССЫЛ имеет два аргумента: ДВССЫЛ(ref_text,a1)

  1. ref_text : Ссылка на ячейку или текстовая строка (или и то, и другое),
    создать ссылку диапазона на ячейку, диапазон ячеек или именованный диапазон.
  • Подробные примеры для этого аргумента см. в разделе Примеры аргументов Ref_Text ниже на этой странице.
  • a1 : (необязательно) Логическое значение — использует ли ссылка ссылку A1
    стиль?
      • ИСТИНА или опущено — ref_text должен быть A1
        ссылка на стиль
      • ЛОЖЬ — ref_text должен быть типом R1C1 стиля ссылки

      		 Предупреждение : Если НЕПРЯМАЯ формула ссылается на другую книгу, эта книга должна быть открыта , иначе формула вернет #ССЫЛКА! ошибка. См. примеры на этой странице для ссылки на другой лист или другую книгу.

      Зависимые выпадающие списки

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

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

      Например,

      1. Выберите категорию овощей в
        столбец Тип продукта
      2. Зависимый раскрывающийся список в столбце Элемент показывает список
        Овощные варианты.

      Правило проверки данных

      Функция ДВССЫЛ используется в диалоговом окне настроек проверки данных, как пример формулы, показанный ниже

      • =ДВССЫЛ(B3)

      В видео ниже показано, как это настроить.

      Видео: Зависимые выпадающие списки

      В этом коротком видео вы увидите, как настроить основной выпадающий список с зависимым выпадающим списком в следующем столбце, который использует функцию ДВССЫЛ.

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

      Введение в примеры видео НЕПРЯМОЙ

      Ниже приведены письменные шаги для примеров НЕПРЯМОГО видео, показанных выше.

      1) Заблокировать ссылку на ячейку

      2) Обратиться к другому листу

      3) Обратиться к другой рабочей книге

      4) Обратиться к именованному диапазону

      Заблокировать ссылку на ячейку

      При создании формулы СУММ и ячеек вставлены позже, вверху списка, формула СУММ может не включать новые суммы. Вот как вы можете использовать функцию ДВССЫЛ, чтобы избежать этой проблемы.

      Функция ДВССЫЛ может «заблокировать» определенную ячейку в формуле. Затем, если строки или столбцы вставляются или удаляются выше или слева от этой ячейки, ссылка не изменяется. Без НЕПРЯМОГО задание будет автоматически корректироваться.

      Чтобы увидеть, как это работает, выполните следующие действия, чтобы создать две формулы — одну
      с обычными ссылками на ячейки и один с НЕПРЯМОЙ
      функция в сочетании с функцией СУММ.

      1. В ячейках C2:C7 введите список чисел
      2. Скопировать список в E2:E7
      3. В ячейке C8 введите формулу СУММ: =СУММ(C2:C7)
      4. В ячейке E8 введите следующую формулу:  =СУММ(ДВССЫЛ(«E2»):E7)
      Вставка строки

      Чтобы увидеть разницу между формулами, вставьте пустую строку над строкой 2 и введите 100 для января в ячейки C2 и E2.

      • Общая сумма изменяется в столбце E, так как начальная ячейка заблокирована на E2.
      • Общая сумма НЕ изменяется в столбце C. Начальная ячейка смещается вниз к ячейке C3.

      Ссылка на другой лист

      ДВССЫЛЬНАЯ формула может также ссылаться на ячейки на других листах.
      В этом примере вы создадите формулу с функцией ДВССЫЛ,
      используя ссылки на имя листа и имя ячейки.

      1. На листе с именем Data Sheet введите числа в ячейки A1:A10
      2. На другом листе в ячейке A2 введите имя листа: Данные
        Лист
      3. В ячейке B2 введите имя ячейки из диапазона чисел, например:
        А2
      4. В ячейке C2 введите следующую формулу:
        = ДВССЫЛ («‘» & A2 & «‘!» & B2)
      5. Первая часть строки представляет собой одинарную кавычку в наборе
        двойные кавычки:
             » ‘ »   (пробелы
        были добавлены для ясности)
      6. После ссылки на А2, которая содержит имя листа, стоит один
        кавычка и восклицательный знак, заключенные в двойные кавычки:
             » ‘ !»   (пробелы
        были добавлены для ясности)
      7. Строка заканчивается ссылкой на ячейку B2, содержащую
        сотовый адрес.
      8. Одинарные кавычки включены в строку, чтобы предотвратить ошибки, если
        имя листа содержит символы пробела.
      9. Нажмите клавишу Enter, и формула вернет число в ячейке
        A2 на листе технических данных.
      10. Измените число в ячейке C2, и результат в D2 изменится.

      Примечание : если ячейка A2 или B2 пуста, формула будет
      вернуть ошибку. Чтобы предотвратить это, вы можете добавить функцию ЕСЛИ:

           =ЕСЛИ(ИЛИ(A2=»»,B2=»»),»»,ДВССЫЛ(«‘»
      & A2 & «‘!» & B2))    

      Ссылка на другую книгу

      ДВССЫЛЬНАЯ формула может ссылаться на ячейки в других книгах, но будет
      верните #ССЫЛКА! ошибка, если эта книга закрыта       . В этом примере
      вы создадите формулу с функцией ДВССЫЛ, используя ссылки
      к имени файла, имени листа и имени ячейки.

      Настройка рабочей книги данных
      1. Создайте новый файл Excel с именем TestFile. xlsx
      2. Измените имя первого листа на Тестовые данные
      3. В листе данных испытаний введите числа в ячейки A1:A10
      4. Сохранить книгу
      Настройка связанной книги

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

      1. Создайте еще один новый файл Excel с именем LinkedFile.xlsx
      2. На первом листе в ячейке A2 введите имя файла данных с расширением или без него:  Тестовый файл
      3. В ячейке A3 введите имя листа: Test Data
      4. В ячейке A4 введите A7 — мы вытащим число из этой ячейки в книге данных
      Получить образец формулы

      Чтобы увидеть синтаксис, который вам потребуется использовать в формуле ДВССЫЛ, выполните следующие действия:

      1. В связанном файле выберите
        ячейку A7 и введите знак равенства:   =

        2. .

      2. Переключитесь на книгу Test File, щелкните ячейку A7 и нажмите Enter
      3. В строке формул вы увидите созданную ссылку:
                = ‘[Test File.xlx s]Test Data’!$A$7
      4. Чтобы сохранить эту формулу видимой, введите апостроф перед знаком равенства
      Создать формулу ДВССЫЛ

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

      1. В ячейке A6 введите следующую формулу:
        = ДВССЫЛ («‘[» & A2 & «]» & A3 & «‘!» & A4)
      Как работает формула
      1. Первая часть строки представляет собой одинарную кавычку и квадратную скобку.
        в наборе двойных кавычек:
             » ‘ [ »   (я добавил пробелы
        здесь, так удобнее читать). Одинарные кавычки включены в строку, чтобы предотвратить ошибки, если
        имя листа или имя файла содержат символы пробела, как в этом примере
      2. Символы и (амперсанд) соединяют вместе все части текстовой строки.
      3. A2 — это ячейка с именем файла данных

        4. .

      4. Далее идут символы для завершения имени книги данных — квадратная скобка в двойных кавычках:   » ] »   (пробелы
        добавлено для ясности)
      5. A3 — ячейка с именем листа
      6. Далее идут символы для окончания имени листа данных — одинарная кавычка и восклицательный знак в двойных кавычках:    » ‘ ! »   (пробелы
        добавлено для ясности)
      7. A4 — ячейка с
        адрес ячейки данных.
      Проверка формулы
      1. Измените адрес ячейки в ячейке A4, и результат в ячейке A6 изменится.
      2. После того, как формула ДВССЫЛ сработает, вы можете удалить образец ссылки в ячейке A5
      Примечания к формуле

      Примечание1 : Если A2, A3 или A4 пусты, формула вернется
      ошибка. Чтобы предотвратить это, вы можете добавить функцию ЕСЛИ:

            =ЕСЛИ(ИЛИ(A2=»»,A3=»»,A4=»»)»,»»,ДВССЫЛ(«‘[»
      & A2 & «]» & A3 & «‘!» & A4))

        Note2 : если рабочая книга Test File. xlsx закрыта,
      Формула ДВССЫЛ вернет ошибку #ССЫЛКА. Я не использовал следующее
      файлы, но они могут помочь вам, если вам нужно извлечь данные из закрытого
      рабочая тетрадь:

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

        • https://www.4shared.com/file/L_eA8s4G/pull.html
      • Laurent Longre имеет надстройку (morefunc.xll) по адресу:
        • https://www.ozgrid.com/Services/ExternalFree.htm#MoreFunc
      • Он включает =indirect.ext(), который может вам помочь.

      Ссылка на именованный диапазон

      В дополнение к ссылкам на ячейки вы можете ссылаться на именованные диапазоны в
      НЕПРЯМАЯ формула. В этом примере используется функция ДВССЫЛ.
      для суммирования выбранного именованного диапазона.

      1. В ячейках A1:B5 введите заголовки и номера, как показано справа.
      2. Назовите ячейки A2:A5 Востоком, а ячейки B2:B5 — Западом. Есть нейминг
        инструкции здесь:

        Имена — Диапазоны имен

      3. В ячейке D2 введите имя одного из диапазонов, например Восток
      4. В ячейке E2 введите формулу: =СУММ(ДВССЫЛ(D2))
      5. Нажмите клавишу Enter, и формула вернет сумму чисел
        в Восточном диапазоне.
      6. Измените ячейку D2 на Запад, и формула вернет сумму чисел
        в Западном ареале.

      Ref_Text Примеры аргументов

      		Этот технический раздел читать не обязательно — в нем приведены примеры для аргумента ref_text в функции ДВССЫЛ. Продолжайте читать, если хотите узнать больше об этих опциях

       

      В большинстве случаев ссылка на ячейку или комбинация текстовой строки и ссылки на ячейку лучше всего работают с функцией ДВССЫЛ.

      Текстовая строка — адрес вводится в формулу (негибкая)
      • Стиль A1: =ДВССЫЛ(«A4») или =ДВССЫЛ(«Демо!B3») или =ДВССЫЛ(«Бюджет»)
      • Стиль R1C1: = ДВССЫЛ («R4C1», ЛОЖЬ)
      • Примечание . Если вы используете простую текстовую строку, диапазон «жестко закодирован» в формуле, что имеет ограниченную полезность, за исключением блокировки ссылки на ячейку.
      Ссылка на ячейку — относится к ячейке, содержащей текстовую строку
      • Ссылка на ячейку стиля A1: = ДВССЫЛ (C6)
      • Стиль ссылки R1C1: =ДВССЫЛ(C8, ЛОЖЬ)
      Комбинированный — Текстовая строка и ссылка на ячейку
      • Стиль A1: =СУММ(ДВССЫЛ(«A4:A» & C12))

      Получить образец файла

      • Примеры ДВССЫЛ (для видео): Получить образец рабочей книги ДВССЫЛ, в которой есть примеры, используемые в видео Примеры ДВССЫЛ.
        Заархивированный файл имеет формат xlsx и не содержит макросов.

      Символы

      		Наконечник Excel

      		 Технический совет

      		 Предупреждение

      Связанные страницы

      Список функций

      Косвенные — сравните листы

      Vlookup

      Выберите

      Countif

      Индекс / Match

      0000 Основы формул и функций в Microsoft Excel 2016

      Главная > Microsoft Excel > Основы формул и функций в Microsoft Excel 2016

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

      Нравится то, что вы видите? Получите наши полные учебные курсы по Microsoft Excel 2016 для начинающих, средних и продвинутых учащихся. Кликните сюда, чтобы узнать больше. >>

      Стенограмма видео

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

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

      Что я собираюсь сделать, так это поместить сумму в эту ячейку здесь, F9. И прежде всего я собираюсь сделать все это очень плохо, или, по крайней мере, вы увидите через несколько минут, что это было сделано очень плохо. И это я просто собираюсь поместить здесь простую формулу, чтобы сложить цифры в столбце F. Вообще говоря, если в ячейке, а не число, например, количество валюты или какой-то текст, например, слово «Путешествие» или дата вы хотите поместить формулу, которая включает в себя вычисление, вы ставите знак равенства, и именно так вы всегда начинаете содержимое ячейки, где нужно выполнить вычисление, поэтому равняется. Теперь вы фактически вводите формулу. Теперь в этом случае я хочу сделать это. Мне нужно содержимое F4, так что это F4, и я просто набираю F4, затем говорю «плюс», затем мне нужно F5, затем я хочу и так далее. Итак, позвольте мне просто добавить другие, и это Формула.

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

      Хотите больше? Начните с бесплатного курса Excel 2016! Нажмите здесь

      Теперь, прежде чем мы пойдем дальше, здесь очень важно понять, что ячейка F9 теперь отличается от других ячеек на этом листе, потому что она имеет значение, ее значение составляет 305,20 доллара США, и у нее есть формула. А его формула — это формула в баре. Поэтому, когда вы щелкаете в строке формул, вы видите в строке формул формулу, которую вы также видите в ячейке в этой ситуации. Но если вы выберете ячейку, вы увидите значение.
      Итак, в ячейке F9 есть формула и значение. И с этим тесно связан тот факт, что без изменения формулы значение может измениться. Итак, давайте предположим, что я заметил ошибку в своих расходах. Давайте предположим, что ошибка, которую я обнаружил, заключается в том, что годовая подписка в размере 64,50 доллара США на самом деле должна стоить 84,50 доллара США. Так что тут была ошибка в 20 долларов. Позвольте мне изменить его на 84,5 доллара, отметьте. Теперь автоматически значение в F9 увеличивается на 20 долларов. И когда вы вносите изменения в ячейку в Excel, любые последующие изменения выполняются автоматически. Итак, в этом случае, когда я меняю значение в F7, F7 является частью формулы для F9.поэтому значение F9 также пересчитывается. Таким образом, мы фактически не изменили формулу в F9, но значение изменилось из-за изменения значения в ячейке F7.

      Формула, которую вы видите здесь, в F9, довольно проста. Это формула для сложения содержимого пяти ячеек. И, конечно же, в Excel формулы могут быть чрезвычайно сложными. И если вы посмотрите, скажем, на длину панели формул прямо на моем экране, вы можете увидеть эту панель формул полной и более чем полной с точки зрения сложности формул, которые возможны в Excel. Но помимо написания такой суммы, =F4+F5+F6 и т. д., есть и другие способы выполнения действий, такие как суммирование столбца цифр, которые на самом деле гораздо более эффективны. И я собираюсь продемонстрировать вам одну из них. Но прежде чем я это сделаю, я хочу указать еще на одну вещь, и это относится к тому, что я сказал ранее.

      Если бы я добавил сюда дополнительные расходы. Итак, давайте предположим, что я понимаю, что были еще расходы на 10 марта, 10 марта, но я забыл их включить. Позвольте мне выбрать строку 7 и вставить дополнительную строку. Давайте вернемся и посмотрим, какая формула в том, что теперь называется F10. Посмотрите на формулу. Это F4+F5+F6+F8+F9. Excel может компенсировать такие вещи, как вставка и удаление строк и столбцов. Таким образом, вам не только не нужно беспокоиться об этом, потому что Excel позаботится об этом, но, очевидно, вы можете использовать это в своих интересах.

      Теперь позвольте мне просто отменить это, и я на самом деле собираюсь удалить F9. Поэтому не забывайте, что когда я выберу эту ячейку, я могу либо просто нажать клавишу Delete, либо сказать «Очистить все», потому что при очистке всего будут очищены формулы, содержимое и форматы. Это прояснит абсолютно все. Так что все убрано. А теперь я собираюсь применить другой подход к суммированию этого столбца цифр.

      Я собираюсь использовать следующий подход. Я собираюсь выбрать F4, держать клавишу мыши нажатой, перетащить вниз и просто включить F9., затем я отпущу клавишу мыши. Я уже упоминал эту маленькую иконку здесь внизу. На данный момент не обращайте внимания на значок быстрого анализа. Пока не беспокойтесь об этом. Что мы собираемся сделать, так это перейти к группе редактирования на ленте и рядом с кнопкой «Автосумма» мы собираемся выбрать «Сумма». И неудивительно, что общая сумма, которую мы получаем, возвращается к 325,20 долларам, как и раньше. И у нас есть формула в F9, но она сильно отличается от той, что была у нас раньше, потому что теперь она использует функцию суммы Excel.

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

      Теперь одно из больших преимуществ использования функции Sum заключается в том, что вам не нужно вводить список имен ячеек. Но у него есть и другие преимущества, и позвольте мне сделать то, что я только что сделал, и вставить строку, соответствующую отсутствующему расходу. Итак, позвольте мне снова выбрать строку 7, выполнить вставку и давайте теперь посмотрим на эту формулу. И формула по-прежнему просто = Сумма, но в этом случае она не только охватывает правильный диапазон от F4 до настоящего F9, но и если я поставлю здесь значение, скажем, я положу туда значение, скажем, 17,30 доллара, оно будет включено в расчетах, потому что при использовании этой функции мне не нужно было специально упоминать новую F7. Все, что мне нужно было сказать, это диапазон, и F7 автоматически включается, потому что он находится в этом диапазоне. Так что это большое преимущество использования функции здесь.

      Теперь позвольте мне отменить последнюю пару операций. И еще одна вещь, которая особенно полезна в этом подходе, давайте предположим, что вместо суммирования этих сумм расходов я по какой-то причине хотел найти, какая из них самая большая. Если я щелкну в строке формул и удалю слово «Сумма» и вместо этого изменю его на слово «Макс», я увижу, что в этом столбце есть цифра «Макс». Таким образом, максимальная сумма составляет 127,50 долларов США. Или как насчет средней цифры? Теперь, когда я начинаю вводить это имя функции, появляется целый список функций, начинающихся с буквы A. И в этот момент, если бы я хотел просто прокрутить вниз, найти тот, который я хочу, средний, это тот, который я хочу. Возьмем это, отметим это. Средняя сумма расходов составляет $65,04. В Excel 2016 доступно очень и очень большое количество функций.

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