Какие из следующих выражений удовлетворяют правилам построения формул Excel
Обновлено: 21.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).
Проверка того, являются ли условия истинными или ложными, и выполнение логических сравнений между выражениями характерны для многих задач. Вы можете использовать функции И, ИЛИ, НЕ и ЕСЛИ для создания условных формул.
Например, функция ЕСЛИ использует следующие аргументы.
Формула, использующая функцию ЕСЛИ
logical_test: условие, которое вы хотите проверить.
value_if_true: возвращаемое значение, если условие истинно.
value_if_false: возвращаемое значение, если условие имеет значение False.
Дополнительные сведения о создании формул см. в разделе Создание или удаление формулы.
Что вы хотите сделать?
Создайте условную формулу, результатом которой будет логическое значение (ИСТИНА или ЛОЖЬ)
Для выполнения этой задачи используйте функции и операторы И, ИЛИ и НЕ, как показано в следующем примере.
Пример
Пример будет легче понять, если вы скопируете его на пустой лист.
Как скопировать пример?
Выберите пример из этой статьи.
Выбор примера из справки
В Excel создайте пустую книгу или лист.
На листе выберите ячейку A1 и нажмите CTRL+V.
Важно! Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (ударение) или на вкладке "Формулы" в группе "Аудит формул" нажмите кнопку "Показать формулы".
После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.
Описание (результат)
Определяет, превышает ли значение в ячейке A2 значение в ячейке A3, а также меньше ли значение в ячейке A2 значения в ячейке A4. (ЛОЖЬ)
Определяет, превышает ли значение в ячейке A2 значение в ячейке A3 или значение в ячейке A2 меньше значения в ячейке A4. (ИСТИНА)
Определяет, не равна ли сумма значений в ячейках A2 и A3 24. (ЛОЖЬ)
Определяет, не равно ли значение в ячейке A5 значению "Звездочки". (ЛОЖЬ)
Определяет, не равно ли значение в ячейке A5 значению "Звездочки" или значение в ячейке A6 равно "Виджеты". (ИСТИНА)
Дополнительную информацию об использовании этих функций см. в разделах Функция И, Функция ИЛИ и Функция НЕ.
Создайте условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ
Для выполнения этой задачи используйте функции и операторы ЕСЛИ, И и ИЛИ, как показано в следующем примере.
Пример
Пример будет легче понять, если вы скопируете его на пустой лист.
Как скопировать пример?
Выберите пример из этой статьи.
Важно! Не выделяйте заголовки строк или столбцов.
Выбор примера из справки
В Excel создайте пустую книгу или лист.
На листе выберите ячейку A1 и нажмите CTRL+V.
Важно! Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (ударение) или на вкладке "Формулы" в группе "Аудит формул" нажмите кнопку "Показать формулы".
После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.
Описание (результат)
=ЕСЛИ(A2=15, "ОК", "НЕ ОК")
Если значение в ячейке A2 равно 15, верните "ОК". В противном случае верните «Не в порядке». (ОК)
=ЕСЛИ(A2<>15, "ОК", "НЕ ОК")
Если значение в ячейке A2 не равно 15, вернуть "ОК". В противном случае верните «Не в порядке». (Не в порядке)
=ЕСЛИ(НЕ(A2 "ЗВЕЗДОЧКИ", "В норме", "Не в норме")
Если значение в ячейке A5 не равно "ЗВЕЗДОЧКИ", вернуть "ОК". В противном случае верните «Не в порядке». (Не в порядке)
Если значение в ячейке A2 больше значения в ячейке A3, а значение в ячейке A2 также меньше значения в ячейке A4, верните "ОК". В противном случае верните «Не в порядке». (Не в порядке)
=ЕСЛИ(И(A2<>A3, A2<>A4), "ОК", "НЕ ОК")
Если значение в ячейке A2 не равно A3, а значение в A2 также не равно значению в A4, верните "ОК". В противном случае верните «Не в порядке». (ОК)
Если значение в ячейке A2 больше значения в ячейке A3 или значение в ячейке A2 меньше значения в ячейке A4, верните "ОК". В противном случае верните «Не в порядке». (ОК)
=ЕСЛИ(ИЛИ(A5<>"Звездочки", A6<>"Виджеты"), "ОК", "Не ОК")
Если значение в ячейке A5 не равно "Звездочки" или значение в ячейке A6 не равно "Виджеты", верните "ОК". В противном случае верните «Не в порядке». (Не в порядке)
=ЕСЛИ(ИЛИ(A2<>A3, A2<>A4), "ОК", "НЕ ОК")
Если значение в ячейке A2 не равно значению в ячейке A3 или значение в ячейке A2 не равно значению в ячейке A4, верните "ОК". В противном случае верните «Не в порядке». (ОК)
Дополнительную информацию об использовании этих функций см. в разделе Функция ЕСЛИ, функция И и функция ИЛИ.
Критерии – ключевая концепция Excel, но создание полезных критериев для текста, чисел, дат, времени и т. д. – сложная задача, поскольку для этого требуется хорошее понимание того, как Excel обрабатывает данные. Это руководство поможет вам создать формулы, которые работают с первого раза, на более чем 50 примерах.
Одним из наиболее важных навыков создания полезных формул является создание критериев — части формулы, которая решает, что включить в расчет, а что исключить. Однако создание эффективных критериев может оказаться на удивление сложным, поскольку для этого требуется хорошее понимание того, как Excel обрабатывает данные. Если вы когда-нибудь проводили полдня, устраняя неполадки с формулой, которая, казалось бы, должна «просто работать», вы понимаете, о чем я :)
Это руководство призвано помочь вам создавать формулы, которые работают с первого раза.
Примечание: знатоки языков заметят, что "критерий" стоит в единственном числе, а "критерий" - во множественном, но я буду использовать "критерий" в обоих случаях для простоты.
Что делают критерии?
Критерии среди прочего:
- Прямой логический поток с логикой IF/THEN
- Ограничить обработку только совпадающими значениями
- Создание условных сумм и подсчетов
- Отфильтровать данные, чтобы исключить ненужную информацию.
- Активировать правила условного форматирования
Чтобы подготовить почву, давайте рассмотрим три примера критериев в действии.
На приведенном ниже экране F3 содержит следующую формулу:
Перевод: если значение в E3 больше 30, вернуть "Да", в противном случае вернуть "Нет".
Здесь E3>30 — это критерий, используемый внутри ЕСЛИ, чтобы определить, должна ли формула возвращать «Да» или «Нет» для каждого счета.
В следующем примере D3 содержит следующую формулу:
Перевод: если B3 "красный" или "зеленый", увеличьте цену на 10%. В противном случае верните исходную цену.
В этом примере функция СУММЕСЛИМН используется для суммирования суммы только в том случае, если цвет красный:
Перевод: сумма значений в E3:E7, когда значение в B3:B7 "красное".
Основные критерии
В этом разделе рассматриваются основные блоки критериев формулы и некоторые простые способы проверки того, что критерии работают должным образом.
Что такое критерии?
Критерии — это логические выражения, которые возвращают ИСТИНА или ЛОЖЬ или их числовые эквиваленты, 1 или 0.
Хитрость заключается в том, чтобы построить критерии таким образом, чтобы они возвращали значение TRUE только в том случае, если тест точно соответствует вашим критериям. Во всех остальных случаях критерии должны возвращать FALSE или ноль. Если вы сможете освоить одну эту идею, у вас будет основа для создания и понимания многих сложных формул.
Логические операторы
Критерии часто используют логические операторы, перечисленные в таблице ниже.
Критерии для чисел
Чтобы проверить, равен ли A1 5, вы можете использовать такие критерии:
Вот еще несколько примеров критериев для проверки числовых значений:
Номера уведомлений НЕ заключаются в двойные кавычки. Если вы заключаете число в кавычки, вы указываете Excel рассматривать число как текст, что сделает критерии бесполезными. Кроме того, помните, что форматирование чисел в Excel влияет только на отображение и никак не меняет числовые данные. Не включайте знаки доллара ($), знаки процента (%) или другую информацию о форматировании при построении критериев для проверки чисел.
Критерии дат
Даты в Excel — это просто числа, а это значит, что вы можете использовать обычные математические операции с датами, если хотите. С датами заказа в столбце A и датами доставки в столбце B эта формула в столбце C будет помечать сроки доставки более 3 дней как "просроченные":
Excel также предоставляет большое количество специальных функций для работы с датами. Например, чтобы проверить, является ли дата «будущей», вы можете использовать функцию СЕГОДНЯ следующим образом:
Чтобы проверить, встречается ли дата в течение следующих 30 дней, формулу можно расширить следующим образом:
Перевод: ЕСЛИ A2 больше сегодняшнего дня И меньше или равно сегодняшнему дню + 30 дней, вернуть TRUE.
Вот несколько других примеров критериев для дат, предполагая, что A1 содержит допустимую дату:
Самый безопасный способ вставить допустимую дату в критерии — использовать функцию ДАТА, которая принимает год, месяц и день в качестве отдельных аргументов. Вот пара примеров:
Критерии времени
Время – это дробное число в Excel, поэтому в некоторых случаях для вычисления времени можно использовать простую математику. Например, чтобы проверить, является ли время в A1 после 12:00 (более 12 часов), вы можете использовать:
Это работает, потому что 1 день = 24 часа, поэтому полдня = 12 часов.
Для более детальной работы в Excel есть специальные функции для извлечения времени по компонентам. Например, для времени 8:45 в ячейке A1:
Самый безопасный способ вставить время в критерий — использовать функцию ВРЕМЯ. Вот несколько примеров:
Критерии для СУММЕСЛИМН, СЧЁТЕСЛИМН и т. д.
Критерии для функций СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧСЛИМН и аналогичных функций на основе диапазона следуют немного другим правилам. Это связано с тем, что критерии разделены на две части (диапазон критериев и критерии), и это влияет на синтаксис, когда критерии включают операторы.
Простые критерии, основанные на равенстве, не требуют специальной обработки. Оператор равенства (=) подразумевается, поэтому нет необходимости включать его в критерии:
Однако все меняется, когда мы добавляем операторы:
Заметили кавычки ("") вокруг критериев? Они требуются, когда критерии включают оператор в эти функции.
Критерии для типов данных
Excel поддерживает три основных типа данных: текстовые, числовые и логические. Даты, время, проценты и дроби — это просто числа с форматированием чисел, применяемым для изменения способа их отображения. По умолчанию числа выравниваются по правому краю, текст выравнивается по левому краю, а логические значения выравниваются по центру. Но пользователь может переопределить выравнивание вручную, так что это не очень хорошая проверка типа.
Excel предоставляет три функции, которые можно использовать для проверки типов данных: ISTEXT, ISNUMBER и ISLOGICAL. Эти функции возвращают TRUE или FALSE. На приведенном ниже экране ячейки D3, F3 и H3 содержат следующие формулы, скопированные вниз:
Чтобы использовать эти функции в качестве критериев, просто поместите их в правильное место формулы.Например, чтобы проверить, содержит ли A1 число, вы можете использовать ISNUMBER в качестве логической проверки внутри ЕСЛИ следующим образом:
Примечание. Формулы не являются типом данных, но их можно проверить с помощью функции ISFORMULA:
Причудливости
В приведенных выше примерах показаны основы использования критериев в формулах. Существует множество способов усложнить критерии. В этом разделе рассматриваются несколько методов.
Изменение критериев
Часто полезно сделать критерии переменными, ссылаясь на ячейку на листе. Например, на приведенном ниже листе проходной балл находится в ячейке E3, а формула для определения "пройдено" или "не пройдено" выглядит следующим образом:
Размещение проходного балла в ячейке E3 позволяет легко изменить его в любое время без редактирования формул. Обратите внимание, что ссылка на $E$3 является абсолютной, чтобы предотвратить изменения при копировании формулы.
Создание переменных критериев в СЧЁТЕСЛИМН, СУММЕСЛИМН и т. д.
Как и раньше, если критерии проверяются на равенство, никакой специальной обработки не требуется:
Однако, если критерии включают операторы, вам потребуется использовать конкатенацию. Например, чтобы подсчитать ячейки больше A1, вам нужно соединить ">" с "A1" следующим образом:
Конкатенация выполняется первой. Если A1 содержит число 10, это формула после конкатенации:
Обратите внимание, что шаблон такой же, как описано выше: если критерии включают операторы, они должны быть заключены в кавычки ("").
Вот еще примеры использования конкатенации в критериях:
Содержит определенный текст
Одна сложная ситуация возникает, когда вы хотите проверить, содержит ли ячейка определенный текст. Для функций, которые поддерживают подстановочные знаки (такие как СЧЁТЕСЛИМН, СУММЕСЛИМН и т. д.), для этого можно использовать подстановочные знаки. Например, для подсчета ячеек, содержащих слово "красный" в любом месте ячейки с помощью СЧЁТЕСЛИМН, вы можете использовать звездочку следующим образом:
Однако многие другие функции (например, функция ЕСЛИ) не поддерживают подстановочные знаки. В этом случае вы можете объединить ISNUMBER и SEARCH, чтобы создать критерии, которые проверяют ячейку на частичное совпадение. На приведенном ниже экране D3 содержит следующую формулу:
Вы можете использовать это выражение в качестве критерия внутри ЕСЛИ следующим образом
Перевод: если где-либо в ячейке A1 встречается слово "красный", вернуть "красный".
Это работает, потому что ПОИСК возвращает числовую позицию, если найдено "красное", а ISNUMBER возвращает ИСТИНА. Если нет, ПОИСК возвращает ошибку, а ISNUMBER возвращает ЛОЖЬ. Подробнее см. на этой странице.
Вложенные ЕСЛИ
Обратите внимание, что сначала мы тестируем низкие баллы, а затем постепенно увеличиваем их.
Константы массива в критериях
Константы массива — это жестко закодированные массивы с фиксированными значениями, например: . Иногда их можно использовать в качестве критериев для создания простых логических критериев ИЛИ. Например, на приведенном ниже экране ячейка F4 содержит следующую формулу:
Перевод: СУММА продаж, где цвет "красный" ИЛИ "золотой".
Поскольку мы задаем СУММЕСЛИМН два значения критерия, он возвращает два результата. Затем функция СУММ возвращает сумму двух результатов.
Простые критерии формулы массива
Формулы массива — сложная тема, но критерии для простых формул массива могут быть довольно простыми. Классический пример — использование функции ЕСЛИ для «отфильтровывания» значений, которые следует исключить, и обработки результата другой функцией.
На приведенном ниже экране формула в G4 выглядит следующим образом:
где "регионы" — это именованный диапазон B3:B8, а "итого" — это именованный диапазон D3:D8.
Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter.
Результатом является максимальное значение для каждого региона.
Для критериев мы используем выражение:
Это сравнивает все значения региона с "Запад" из F4 и возвращает следующий результат массива в логическом тесте для IF:
Окончательный массив, возвращаемый IF, выглядит следующим образом:
В массив попадают только значения, связанные с регионом "Запад". Значения, связанные с регионом "Восток", являются FALSE.
Затем функция MAX возвращает наибольшее значение в массиве, игнорируя все значения FALSE.
Расширенные критерии формулы
Ниже приведены ссылки на более сложные примеры критериев формулы. У каждой ссылки есть снимок экрана и полное объяснение.
Если вы новичок в Excel для Интернета, вы скоро обнаружите, что это больше, чем просто таблица, в которой вы вводите числа в столбцах или строках. Да, вы можете использовать Excel в Интернете, чтобы найти итоги для столбца или строки чисел, но вы также можете рассчитать платеж по ипотеке, решить математические или инженерные задачи или найти лучший сценарий на основе переменных чисел, которые вы подключаете.
Excel для Интернета делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак плюс или минус) и функции, которые действительно расширяют возможности формулы.
Например, следующая формула умножает 2 на 3, а затем добавляет к этому результату 5, чтобы получить ответ 11.
В следующей формуле используется функция ПЛТ для расчета платежа по ипотеке (1073,64 долл. США), основанного на процентной ставке 5 % (5 %, разделенные на 12 месяцев, равняется месячной процентной ставке) за 30-летний период (360 месяцев). ) для кредита в размере 200 000 долларов США:
Вот несколько дополнительных примеров формул, которые можно ввести на листе.
=A1+A2+A3 Складывает значения в ячейках A1, A2 и A3.
=SQRT(A1) Использует функцию SQRT для возврата квадратного корня из значения в A1.
=TODAY() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции листа ПРОПИСН.
=IF(A1>0) Проверяет ячейку A1, чтобы определить, содержит ли она значение больше 0.
Части формулы
Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.
<р>1. Функции: функция PI() возвращает значение числа пи: 3,142. <р>2. Ссылки: A2 возвращает значение в ячейке A2. <р>3. Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2. <р>4. Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.Использование констант в формулах
Использование операторов вычисления в формулах
Операторы определяют тип вычисления, которое необходимо выполнить для элементов формулы. Существует порядок, в котором выполняются вычисления по умолчанию (это соответствует общим математическим правилам), но вы можете изменить этот порядок, используя круглые скобки.
Типы операторов
Существует четыре различных типа операторов вычисления: арифметические операции, сравнение, конкатенация текста и ссылка.
Арифметические операторы
Для выполнения основных математических операций, таких как сложение, вычитание, умножение или деление; комбинировать числа; и выводить числовые результаты, используйте следующие арифметические операторы.
Арифметический оператор
Операторы сравнения
Вы можете сравнить два значения с помощью следующих операторов. Когда два значения сравниваются с помощью этих операторов, результатом является логическое значение — либо ИСТИНА, либо ЛОЖЬ.
Оператор сравнения
> (знак больше)
= (знак больше или равно)
Больше или равно
(не равно знаку)
Оператор объединения текста
Используйте амперсанд (&), чтобы соединить (объединить) одну или несколько текстовых строк, чтобы получить единый фрагмент текста.
Текстовый оператор
Соединяет или объединяет два значения для создания одного непрерывного текстового значения
"Север"&"ветер" приводит к "Борей"
Ссылочные операторы
Объедините диапазоны ячеек для вычислений с помощью следующих операторов.
Оператор ссылки
Оператор диапазона, который создает одну ссылку на все ячейки между двумя ссылками, включая две ссылки.
Оператор объединения, который объединяет несколько ссылок в одну ссылку
Оператор пересечения, создающий одну ссылку на ячейки, общие для двух ссылок
Порядок, в котором Excel для Интернета выполняет операции в формулах
В некоторых случаях порядок, в котором выполняются вычисления, может повлиять на возвращаемое значение формулы, поэтому важно понимать, как определяется порядок и как можно изменить порядок, чтобы получить нужные результаты.
Порядок расчета
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равенства (=).Excel в Интернете интерпретирует символы, следующие за знаком равенства, как формулу. После знака равенства следуют вычисляемые элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычисления. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет оператора
Если вы объединяете несколько операторов в одной формуле, Excel в Интернете выполняет операции в порядке, указанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом (например, если формула содержит оператор умножения и деления), Excel в Интернете оценивает операторы слева направо.
Описание
Отрицание (как в –1)
Умножение и деление
Сложение и вычитание
Соединяет две строки текста (объединение)
Использование скобок
Чтобы изменить порядок вычисления, заключите в круглые скобки ту часть формулы, которая будет вычисляться первой. Например, следующая формула дает 11, так как Excel в Интернете выполняет умножение перед сложением. Формула умножает 2 на 3, а затем добавляет к результату 5.
Наоборот, если вы используете круглые скобки для изменения синтаксиса, Excel для Интернета суммирует 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере круглые скобки, заключающие первую часть формулы, заставляют Excel для Интернета сначала вычислить B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.< /p>
Использование функций и вложенных функций в формулах
Функции – это предопределенные формулы, которые выполняют вычисления с использованием определенных значений, называемых аргументами, в определенном порядке или структуре. Функции можно использовать для выполнения простых или сложных вычислений.
Синтаксис функций
Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.
<р>1. Структура. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка. <р>2. Имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3. <р>4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, введите =ROUND( и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.Ввод функций
При создании формулы, содержащей функцию, вы можете использовать диалоговое окно "Вставить функцию", чтобы упростить ввод функций рабочего листа. Когда вы вводите функцию в формулу, диалоговое окно «Вставить функцию» отображает имя функции, каждый из ее аргументов, описание функции и каждого аргумента, текущий результат функции и текущий результат всей формулы. .
Чтобы упростить создание и редактирование формул и свести к минимуму опечатки и синтаксические ошибки, используйте автозаполнение формул. После того как вы введете = (знак равенства) и начальные буквы или триггер отображения, Excel в Интернете отобразит под ячейкой динамический раскрывающийся список допустимых функций, аргументов и имен, которые соответствуют буквам или триггеру. Затем вы можете вставить элемент из раскрывающегося списка в формулу.
Вложенные функции
В некоторых случаях вам может понадобиться использовать функцию в качестве одного из аргументов другой функции. Например, следующая формула использует вложенную функцию СРЗНАЧ и сравнивает результат со значением 50.
<р>1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.Ограничения уровня вложенности Формула может содержать до семи уровней вложенности функций. Когда одна функция (назовем ее Функцией Б) используется в качестве аргумента в другой функции (назовем ее Функцией А), Функция Б действует как функция второго уровня. Например, функция СРЗНАЧ и функция СУММ являются функциями второго уровня, если они используются в качестве аргументов функции ЕСЛИ. Функция, вложенная во вложенную функцию СРЗНАЧ, становится функцией третьего уровня и т. д.
Использование ссылок в формулах
Ссылка определяет ячейку или диапазон ячеек на листе и сообщает 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
Создание ссылки на другой лист В следующем примере функция листа AVERAGE вычисляет среднее значение для диапазона B1:B10 на листе Marketing в той же книге.
<р>1. Относится к рабочему листу под названием "Маркетинг" <р>2. Относится к диапазону ячеек от B1 до B10 включительно <р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеекРазница между абсолютными, относительными и смешанными ссылками
Относительные ссылки Относительная ссылка на ячейку в формуле, например 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, ссылки.
Использование имен в формулах
Вы можете создавать определенные имена для представления ячеек, диапазонов ячеек, формул, констант или таблиц Excel для Интернета. Имя — это значимое сокращение, упрощающее понимание назначения ссылки на ячейку, константы, формулы или таблицы, каждое из которых может быть трудно понять с первого взгляда. Следующая информация показывает распространенные примеры имен и то, как их использование в формулах может улучшить ясность и сделать формулы более понятными.
Читайте также: