В excel в формуле участвует ячейка с формулой

Смотрите видео после статьи

видео может дополнять статью или содержать её полностью

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

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

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

Нужны ли «комплексные» формулы?

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

Частым вопросом является такой: «можно ли вообще создавать в Excel вложенные формулы или так делать нельзя». Давайте рассмотрим это на примере простых вычислений.

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

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

Что касается использования встроенных в Excel формул (функций), то на первый взгляд использование их путём вложения друг в друга невозможно. Такую точку зрения часто обосновывают тем, что при вставке формулы в ячейку программа показывает всплывающее окно, предназначенное для ручного или автоматического (при помощи мышки) выбора диапазонов ячеек, отдельных ячеек или отдельных значений (как числовых, так и текстовых). На скриншоте ниже приведён пример для вставки формулы вычисления среднего значения («СРЗНАЧ»).

[нажмите на картинку для увеличения]

Справка: как сохранять фото с сайтов

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

Пример создания формулы в формуле в Excel

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

Что нужно сделать: вычислим сумму средних значений обоих столбцов.

Если не использовать комплексные вычисления, то нам бы потребовалось целых три ячейки:

• В первой ячейке: вычислить среднее значение для колонки 1 (столбец А) при помощи функции СРЗНАЧ;
• Во второй колонке сделать то же самое, но для колонки 2 (столбец B);
• В третьей ячейке при помощи функции СУММ сложить два промежуточных результата;

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

Чтобы понять, как это сделать, нам необходимо рассмотреть синтаксис используемых «встроенных» формул Excel:

• Функция «СУММ»
  Применяется для сложения двух или более чисел или диапазонов чисел.
  Синтаксис: СУММ(X; Y; Z;)
• Функция «СРЗНАЧ»
  Применяется для вычисления среднего значения двух или более чисел или диапазонов чисел.
  Синтаксис: СРЗНАЧ(X; Y; Z;)

Здесь X, Y, Z — это или конкретные числа, указанные вручную, или адреса ячеек, или диапазон ячеек вида «A1:A10». Обратите внимание, что при указании в качестве параметров диапазонов ячеек, аргумент у функций может быть всего один; при указании отдельных адресов ячеек или чисел — минимум два аргумента.

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

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

• СУММ(СРЗНАЧ(A1:A5); СРЗНАЧ(В1:В5))

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

Комплексные формулы в Excel — это не сложно

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

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

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

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

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

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

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

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

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

Формулы в Excel для чайников

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

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.

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

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

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

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

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

• %, ^;
• *, /;
• +, -.

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



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

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:



2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.



3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

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

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9



2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».



3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

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



2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5



3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

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

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.



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



4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

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

Ill show you how to see the cells used in a formula/function in Excel and also how to tell which cells are using that particular formula/function.

This is a simple yet very helpful feature in Excel and it allows you to visually represent the relationship between the cells/formulas/functions in the worksheet.

This is actually very easy to do, just select the formula/function for which you want to see the cells it uses or references, and then go to the Formulas tab and click the Trace Precedents button:

Formulas tab > Trace Precedents

The best part about this feature is that you can now select other cells and go about your work in this worksheet while keeping these arrows visible.  This is what makes this different than just double-clicking a formula/function, which also shows the cells used in it.

Now, if you wanted to see if there were any cells that referenced this cell/function/formula, simply select it and then click the Trace Dependents button on the Formulas Tab.

Formulas Tab > Trace Dependents

If you want to remove these arrows, simply click the Remove Arrows button or click the arrow next to that button and select which arrows you would like to remove, either the precedent or the dependent ones.

Now we are back where we started.

Remember that if you just want a quick view of the cells that are used in a formula or function you just have to double-click the desired cell and you will see a nice representation of the precedent cells in the worksheet.

However, this representation of the precedent cells will not stay visible once you have deselected the cell or continued working in the worksheet, and this is why the Trace Precedents and Trace Dependents feature is so useful.

I hope this tutorial was helpful!

Similar Content on TeachExcel

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!