Эксель формулы все: Функции Excel (по алфавиту) — Служба поддержки Майкрософт

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

Главная » Макросы (VBA)

Автор Дмитрий Якушев На чтение 3 мин. Просмотров 3.4k.

Что делает макрос: Этот макрос помогает преобразовать все формулы в заданном диапазоне в значения.

Содержание

  1. Как макрос работает
  2. Код макроса
  3. Как этот код работает
  4. Как использовать

Как макрос работает

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

Код макроса

Sub PreobrazovatFormuliVZnacheniya()
'Щаг 1: Объявляем переменные
Dim MyRange As Range
Dim MyCell As Range
'Шаг 2: Сохраните книгу прежде, чем измените ячейки?
Select Case MsgBox("Перед изменением ячеек" & _
"Сохранить книгу?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook. Save
Case Is = vbCancel
Exit Sub
End Select
'Шаг 3: Определяем целевой диапазон
Set MyRange = Selection
'Шаг 4: Запускаем цикл по диапазону
For Each MyCell In MyRange
'Шаг 5: Если в ячейке есть формула, устанавливаем значение
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
'Шаг 6: Получаем следующую ячейку в диапазоне
Next MyCell
End Sub

Как этот код работает

  1. Шаг 1 объявляет две переменные объекта Range.
  2. При выполнении макрос уничтожает стек отката. Это означает, что вы не сможете отменить изменения, поэтому нужно сохранить книгу перед запуском макроса. Это делает Шаг 2.
  3. Здесь мы вызываем окно сообщения, которое спрашивает, хотим ли мы сохранить книгу в первую очередь. Затем он дает нам три варианта: Да, Нет и Отмена. Щелчок Да сохраняет книгу и продолжает с помощью макроса. Нажатие кнопки Отмена выходит из процедуры без запуска макроса. Щелчок Нет запускает макрос без сохранения рабочей книги.
  4. Шаг 3 заполняет переменную MyRange с целевым диапазоном. В этом примере мы используем выбранный диапазон — диапазон, который был выбран в электронной таблице. Вы можете легко установить переменную MyRange в определенном диапазоне, таком как Range («A1:Z100»). Кроме того, если ваш целевой диапазон является именованный диапазон, вы можете просто ввести его имя: Range («MyNamedRange»).
  5. тот этап начинает цикл через каждую ячейку в целевом диапазоне, активизируя каждую ячейку. После того, как ячейка активируется, макрос использует свойство HasFormula, чтобы проверить, содержит ли ячейка формулу. Если содержит, мы устанавливаем ячейку равную значению, которое отображается в ячейке. Это эффективно заменяет формулу с жестко предопределенным значением.
  6. Шаг 6 повторяет цикл, чтобы получить следующую ячейку. После просмотра всех ячеек в целевом диапазоне макрос заканчивается.

Как использовать

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

  1. Активируйте редактор Visual Basic, нажав ALT + F11 на клавиатуре.
  2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
  3. Выберите Insert➜Module.
  4. Введите или вставьте код.

— Excel


MS Office 2007: Microsoft Excel



Формулы массивов в Excel


Одна из наиболее интересных (и наиболее мощных) возможностей Excel — допустимость использования массивов в формулах.

Массив — это набор элементов, которые могут обрабатываться как единая группа или каждый в отдельности. В Excel массивы могут быть одно- или двухмерными. Измерения массивов непосредственно соответствуют строкам и столбцам. Например, одномерный массив может быть группой ячеек, которые размещены в одной строке (горизонтальный массив) или в одном столбце (вертикальный массив). Двухмерный массив размещается в нескольких строках и столбцах (рис.94). Трехмерных массивов Excel не поддерживает.



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

Задача 5.8. Формулы массивов для диапазонов ячеек


На рис.95 показан простой рабочий лист, на котором вычисляются

объемы продаж некоторых товаров. Чтобы вычислить объем продаж каждого конкретного товара (значения в столбце D), обычно используется формула умножения количества проданного товара (столбец С) и цены товара (столбец В). Например, в ячейке D2 будет формула =В2*С2, которая затем копируется на все оставшиеся ячейки столбца D. В данном случае получим пять отдельных формул в столбце D.

Другой способ вычисления пяти значений в столбце D — использование одной формулы массива. Эта формула займет диапазон D2:D5 и вернет сразу все пять искомых значений.

Для создания формулы массива следует выполнить следующее.

  1. Выделить диапазон, в котором должен содержаться результат. В нашем примере это диапазон D2:D5.
  2. Ввести формулу =В2:В5*С2:С5. Поставьте равно и выделите диапазон В2:В5, поставьте значок умножить и выделите диа­пазон С2:С5.
  3. Нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы ввести формулу как формулу массива (обычно для завершения ввода формулы следует нажать клавишу Enter).

Формула будет введена сразу во все пять выделенных ячеек. Если вы посмотрите в строку формул, то там будет представлена формула {=В2:В5*С2:С5}, т.е. Excel произвел попарное умножение элементов массивов В2:В5 и С2:С5 и создал новый массив стоимостей D2:D5.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с кла­виатуры бесполезно — они автоматически появляются при нажатии Ctrl+Shift+Enter (при удержании нажатых клавиш Shift и Ctrl производят нажатие клавиши Enter). Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока (Проверьте!). Excel заключает формулу в фигурные скобки, показывая тем самым, что это формула массива.  Эти скобки нельзя набирать вручную (формула будет воспринята как текст).

Задача 5.9. Формулы массивов для отдельных ячеек



Формулы массивов могут возвращать результат и в одну ячейку. На рис.96 в ячейку D7 введена формула массива

{=СУММ(В2:В5*С2:С5)}.

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

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

Формула, как и предыдущая, работает с двумя массивами, храня­щимися в диапазонах В2:В5 и С2:С7. Формула перемножает соответ­ствующие значения из этих массивов виртуальный массив, который существует только в памяти компьютера. Затем функция СУММ обраба­тывает этот новый массив и возвращает сумму его значений.

В данном случае вместо формулы массива можно воспользоваться функцией СУММПРОИЗВ, которая вернет тот же результат: =СУММПРОИЗВ(В2:В6;С2:С6).

Задача 5.10. Именованные массивы

Можно создать массив констант, присвоить ему имя и затем ис­пользовать именованный массив в формулах. Строго говоря, именованный массив представляет собой формулу, которой присвоено имя. Разберем на примере.

Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные в таблицу (рис.97).



Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора.

Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым (В2:В5) и третьим (С2:С5) столбцами таблицы последовательно и присвойте имена во вкладке Формулы-присвоить Имя. Диапазону В2:В5 присвойте имя Приход, а диапазону С2:С5 — имя Расход.

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

Вводим в диапазон D2:D5 формулу массива.

  • Выделим блок D2: D5. В этом блоке активна ячейка D2.
  • Наберем знак равенства =.
  • Нажмем функциональную клавишу F3. Появится окно Вставка имени. Выберем имя Приход и щелкнем Ок. Формула примет вид: =Приход.
  • Наберем знак минус (-).
  • Вновь нажмем клавишу F3. В диалоговом окне Вставка имени выберем имя Расход и щелкнем Ок. Формула примет вид: =Приход-Расход.
  • Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}.

Прокомментируем шаги. На третьем и пятом шаге выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще, и нет риска ошибиться в наборе имени.

Ввод формулы массива. Разумеется, формулу массива можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12 и повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9: С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы.

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

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

Чтобы отредактировать формулу массива, необходимо выделить все ячейки массива, активизировать строку формул и удалить фигурные скобки. По окончании редактирования формулы, следует нажать комбинацию клавиш Shift+Ctrl+Enter, чтобы внести изменения. Теперь содержимое всех ячеек массива изменится в соответствии с внесенными изменениями. (Попробуйте, например, ввести формулу {Приход-Расход-1} , потом отменить это.)

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

  1. Выделить весь диапазон, содержащий формулу массива.
  2. Нажать клавишу F2, чтобы перейти в режим редактирования.
  3. Нажать комбинацию клавиш Ctrl+Enter. Это действие вводит отдельные формулы (не формулу массива) в каждую ячейку выделенного диапазона.
  4. Изменить выделение диапазона так, чтобы он включал новые ячейки (или исключал лишние).
  5. Нажать клавишу F2.
  6. Нажать комбинацию клавиш Shift+Ctrl+Enter.

Коррекция формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год — 1996, приход — 240, расход 200.  Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, поступили бы просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат #ЗНАЧ!. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13 : В16-С13 : С16} — образовался второй блок, что не является корректным решением нашей задачи. Отменим последнее действие.

Для первого и второго случая подходы к решению будут разные. Для первого случая необходимо изменить именованные блоки.

  • Во вкладке Формулы-Диспетчер имен выделить Приход, и внизу окна в Диапазон изменить диапазон значений с В1:С5 на В1:С6. Для его изменения удалить имеющийся в окне диапазон и, перейдя на лист, выделить новый диапазон В1:С6. Точно также изменить диапазон для Расхода. После изменений диалоговое окно закрыть.
  • Выделить D2: D6, нажать клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажать клавиши Shift+ Ctrl+Enter.

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

  • Выделить D8 :D13 и нажать клавишу F2 для редактирования фор­мулы.
  • Изменить в формуле ссылки на диапазоны ячеек с В8:В12 на В8:В13 и с С8: С12 на С8:С13. Нажать сочетание клавиш Shift+Ctrl+Enter.

Коррекция табличной формулы при уменьшении блока. Теперь необходимо удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменить поименованные блоки (в результате в ячейке D6 результат отобразится как #Н/Д — недоступно). Выделить блок с формулой массива, нажать клавишу F2. Ввод закончить клавишами Ctrl+Enter.

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

Очистить последнюю строку таблицы. Выделить блок D2: D5, нажать клавишу F2, нажать клавиши Shift+Ctrl+Enter.

Аналогично поступить со второй таблицей:

  • Выделить диапазон с формулой массива.
  • Нажать комбинацию клавиш Ctrl+Enter, чтобы преобразовать формулу массива в обычную.
  • Удалить последнюю строчку таблицы.
  • Выделить диапазон для формулы массива, D9: D12.
  • Нажать F2 для ее редактирования. Используемые в формуле ссылки на диапазоны выделяются рамочками, закрашенными в разные цвета. Видно, что рамочки захватывают области удален­ной строки. Для корректировки ссылок в формуле можно умень­шить рамочки, схватив за любой нижний угол и подтянув к верху до последней используемой строки. Ссылки в формуле изменять­ся в соответствии с выделением. Но можно просто изменить в ссылках цифру 3 на 2 (В13 изменить на В12 и С13 на С12).
  • Закончить изменения клавишами Shift+Ctrl+Enter.

Задача 5.11. Создание массивов на основе значений ячеек диапазона.

На рис.98 показан рабочий лист, содержащий данные в диапазоне A1: С4.

Диапазон С8:F11 содержит массив, созданный на основе этих данных с помощью формулы {=А1:С4}

Массив в диапазоне С8 :F11 связан со значениями диапазона А1:С4.  


Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве.

Задача 5.12. Транспонирование массива.

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием и выполняется при помощи формулы массива и функции ТРАНСП.

Допустим, имеется двумерный массив ячеек (рис.99), который необходимо транспонировать.







Для размещения транспонированного массива следует выделить диапазон ячеек (рис. 100). Поскольку исходный массив ячеек состоял из 8 строк на 2 столбца, то необходимо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов. Далее в выделенном диапазоне ввести функцию транспонирования =ТРАНСП(, где в качестве аргумента функции является наш массив ячеек А1:В8. Заканчиваем ввод формулы комбинацией клавиш Ctrl+Shift+Enter и получаем «перевернутый массив» в качестве результата (рис. 101).




Задача 5.13. Таблица умножения.

Если вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке была таблица умножения вот такого вида (рис. 102).

При помощи формул массива она вся делается в одно движение:

  • ввести два диапазона чисел от 1 до 10 в строке 1 и столбце А;
  • выделить диапазон В2:К11;
  • ввести формулу =А2:A11*В1:К1;
  • закончить ввод формулы комбинацией клавиш Ctrl+Shift+Enter, чтобы Excel воспринял ее как формулу массива. Результат пред­ставлен ниже (рис. 103).

Задача 5.14. Выборочное суммирование.

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику (рис. 104).



В данном случае формула массива синхронно пробегает по всем эле­ментам диапазонов СЗ:С21 и ВЗ:В21, проверяя, совпадают ли они с задан­ными значениями из ячеек G4 и G5.

Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом, суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Можно применить и другой способ:

Нам необходимо просуммировать числа из диапазона D3:D21 (диапазон суммирования) при выполнении нескольких (двух) условий. Для каждой строки в случае одновременного равенства числа из диапазона СЗ:С21 числу G8, и числа из диапазона ВЗ:В21 числу G9, соответствующее число из диапазона суммирования будет суммироваться.

Для этого применим функцию СУММЕСЯИМН.

=СУММЕСЛИМН(D3:D21; СЗ:С21; G8; ВЗ:В21; G9)

На открывшейся форме СУММЕСЯИМН в окне Диапазон суммирования выбрать диапазон для суммирования (D3:D21). Для прописания условий в окне Диапазон_условия1 необходимо выбрать диапазон первого условия СЗ:С21, который равен критерию G8 — окно Условие1, в окне Диапазон_условия2 ввести диапазон второго условия ВЗ:В21, который должен равняться G9 — окно Условие2.

Попробуйте решить задачу двумя способами и сравните результат. 


Задача 5.15.


Вернемся к задаче обработки данных метеостанции (Задача 5.5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Функции, которые можно применять к списку, называются дистрибутивными.

Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:Н14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводится формула =ЕСЛИ(И(ВЗ>20; в3<80) ; 1; 0) и копируется в остальные ячейки блока F3:h24. В блоке выводятся нули и единицы.

Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 — выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 — в противном случае. Для подсчета суммы нормальных месяцев вводим в ячейку F25 формулу =СУММ (F3:F14) и копируем ее в блок G25:Н25. Количество нормальных месяцев посчитано.

А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ (ЕСЛИ (ВЗ : В14>0;ЕСЛИ (ВЗ :В14<80; 1; 0) ; 0) ) } и скопируем ее в С25:D25. (Таким образом, компьютер, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае — проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном случае — 0). Формула {=СУММ (ЕСЛИ (И(ВЗ>20;ВЗ<80) ;1;0) ) } не приведет к успеху, так как функция И не является дистрибутивной.

Этот пример показывает способ подсчёта в множестве количество элементов, удовлетворяющих определенному критерию.



Теперь вычислим суммарное количество осадков, выпавших и эти месяцы. Введите в А26 текст «Осадки в нормальные месяцы», в В26 — табличную формулу {=СУММ(ЕСЛИ(ВЗ:В14>20;ЕСЛИ(ВЗ:В14<80; ВЗ : В14; 0) ; 0) ) } и скопируйте ее в С26:D26. В Е25 и Е26 введите формулы для суммирования значений в строках (выделив блок В25:Е26, выберите значок Автосумма). Вы получите блок, показанный на рис. 105.


Задача 5.16.


В блоке A1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей.

Решение. На новом листе запишите любую последовательность чисел в блок A1:А10.

Окончательное решение можно записать одной формулой, поместив его в ячейку А12.

{=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(A1:А10)-1; «возрастающая»; «не является возрастающей»)}

Разбор этой формулы:

  • А2:А10-А1:А9 (т.е., из А10 вычитается А9, из А9 вычитается А8 и т.д.) — образует блок, состоящий из первых разностей элементов исходного блока;
  • ЕСЛИ (А2 : А10-А1: А9>0; 1; 0) — составляет блок из индикаторов положительных первых разностей;
  • СУММ (ЕСЛИ (А2 :А10-А1 :А9>0; 1; 0) ) — считает количество ненулевых элементов в блоке индикаторов;
  • СЧЕТ (A1: А10)-1 — рассчитывает размер блока индикаторов, равный уменьшенному на 1 размеру исходною блока;
  • и, наконец, если количество ненулевых элементов в блоке инди­каторов равно размеру блока индикаторов, то последователь­ность возрастающая, иначе — нет.

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





Формулы Excel: простые формулы

Урок 2: простые формулы

/en/excelformulas/about-this-tutorial/content/

Введение

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

Дополнительно: загрузите нашу рабочую тетрадь. 9 ) для показателей.

Стандартные операторы

Все формулы в Excel должны начинаться со знака равенства ( = ). Это связано с тем, что ячейка содержит или равна формуле и вычисляемому ею значению.

Понимание ссылок на ячейки

Хотя вы можете создавать простые формулы в Excel вручную (например, =2+2 или =5*5 ), большую часть времени вы будете использовать адресов ячеек для создания формулы . Это известно как создание ссылка на ячейку . Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, поскольку вы можете изменить значение ячеек, на которые ссылаются, без необходимости переписывать формулу.

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

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

Примеры простых формул

Чтобы создать формулу:

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

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

    Выбор ячейки B3

  2. Введите знак равенства (=) . Обратите внимание, как он отображается в ячейке и в формуле bar .

    Ввод знака =

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

    Ссылка на ячейку B1

  4. Введите математический оператор , который вы хотите использовать. В нашем примере мы напечатаем знак добавления ( + ).
  5. Введите адрес ячейки ячейки, на которую вы хотите сослаться второй в формуле: ячейка B2 в нашем примере. Вокруг указанной ячейки появится красная рамка .

    Ссылка на ячейку B2

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

    Полная формула и расчетное значение

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

Изменение значений с помощью ссылок на ячейки

Истинное преимущество ссылок на ячейки заключается в том, что они позволяют вам обновлять данные на листе без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки B1 с 1200 до 1800 долларов. Формула в ячейке B3 автоматически пересчитает и отобразит новое значение в ячейке B3.

Пересчитанное значение ячейки

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

Чтобы создать формулу методом «укажи и щелкни»:

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

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

    Выбор ячейки D3

  2. Введите знак равенства (=) .
  3. Выберите ячейку , на которую вы хотите сослаться первой в формуле: ячейка B3 в нашем примере. В формуле появится адрес ячейки , а вокруг указанной ячейки появится пунктирная синяя линия .

    Ссылочная ячейка B3

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

    Ссылка на ячейку C3

  6. Нажмите Введите на клавиатуре. Формула будет вычислено , и в ячейке отобразится значение .

    Завершенная формула и вычисленное значение

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

Копирование формулы в соседние ячейки с помощью маркера заполнения

Чтобы изменить формулу:

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

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

    Выбор ячейки B3

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

    Выбор формулы для редактирования

  3. Вокруг всех ячеек, на которые есть ссылки, появится рамка . В нашем примере мы изменим вторую часть формулы, указав ссылку на ячейку B2 вместо ячейки C2 .

    Неуместная ссылка на ячейку

  4. Когда закончите, нажмите Введите на клавиатуре или нажмите галочку в строке формул.

    Редактирование формулы

  5. Формула будет обновлена ​​ , а в ячейке будет отображаться новое значение .

    Новое рассчитанное значение

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

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

Вызов!

  1. Откройте существующую книгу Excel. Если вы хотите, вы можете использовать нашу рабочую тетрадь.
  2. Создайте простую формулу сложения, используя ссылок на ячейки . Если вы используете пример, создайте формулу в ячейке B4 для расчета общего бюджета.
  3. Попробуйте изменить значение ячейки, на которую ссылается формула. Если вы используете пример, измените значение ячейки B2 до 2000 долларов. Обратите внимание, как формула в ячейке B4 пересчитывает итог.
  4. Попробуйте использовать метод «наведи и щелкни» для создания формулы. Если вы используете пример, создайте формулу в ячейке G5 , которая умножает стоимость салфеток на количество , необходимое для расчета общей стоимости .
  5. Редактировать формулу с помощью строки формул. Если вы используете пример, отредактируйте формулу в ячейке B9 , чтобы изменить знак деления ( / ) на знак минус ( ).

Продолжать

Предыдущий: Об этом учебнике

Далее:Сложные формулы

/en/excelformulas/complex-formulas/content/

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

Урок 5: функции

/en/excelformulas/относительные-и-абсолютные-ссылки-ячеек/content/

Введение

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

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

Части функции

Для правильной работы функция должна быть написана особым образом, который называется синтаксисом . Основной синтаксис функции: знак равенства (=) , имя функции (например, SUM) и один или несколько аргументов . Аргументы содержат информацию, которую вы хотите вычислить. Функция в приведенном ниже примере добавит значения диапазона ячеек A1:A20.

Работа с аргументами

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

Например, функция =СРЗНАЧ(B1:B9) будет вычислять среднее значений в диапазоне ячеек B1:B9. Эта функция содержит только один аргумент.

Несколько аргументов должны быть разделены запятой . Например, функция =СУММ(A1:A3, C1:C2, E2) добавит значений всех ячеек в трех аргументах.

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

Существует множество функций. Вот некоторые из наиболее часто используемых функций:

  • SUM : Эта функция добавляет всех значений ячеек в аргумент.
  • СРЕДНЕЕ : Эта функция определяет среднее значений, включенных в аргумент. Он вычисляет сумму ячеек, а затем делит это значение на количество ячеек в аргументе.
  • COUNT : Эта функция подсчитывает количество ячеек с числовыми данными в аргументе. Эта функция полезна для быстрого подсчета элементов в диапазоне ячеек.
  • MAX : Эта функция определяет наибольшее значение ячейки , включенное в аргумент.
  • MIN : Эта функция определяет наименьшее значение ячейки , включенное в аргумент.
Чтобы использовать функцию:

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

  1. Выберите ячейку , которая будет содержать функцию. В нашем примере мы выберем ячейку C11 .
  2. Введите знак равенства (=) и введите желаемое имя функции . В нашем примере мы введем =AVERAGE .
  3. Введите диапазон ячеек для аргумента внутри круглых скобок . В нашем примере мы введем (C3:C10) . Эта формула добавит значения ячеек C3:C10, а затем разделит это значение на общее количество ячеек в диапазоне, чтобы определить среднее значение.
  4. Нажмите Введите на клавиатуре. Функция будет вычислена , а результат появится в ячейке. В нашем примере средняя цена за единицу заказанного товара составила $15,93 .

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

Работа с незнакомыми функциями

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

Затем вы можете ввести открывающую скобку, чтобы увидеть какие аргументы ему нужны.

Понимание вложенных функций

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

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

=РАБДЕНЬ(СЕГОДНЯ(),3)

Здесь у нас есть две разные функции, работающие вместе: функция РАБДЕНЬ и функция СЕГОДНЯ. Они известны как вложенных функций , поскольку одна функция помещается или вложенная в аргументы другой. Как правило, вложенная функция всегда вычисляется первой , точно так же, как скобки выполняются первыми в порядке операций. В этом примере функция СЕГОДНЯ будет вычисляться первой, поскольку она вложена в функцию РАБДЕНЬ.

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