Как составить в экселе формулу: Функции Excel (по категориям)

возведение в степень;
% процент.

Создание формулы в Экселе начинается с написания знака равенства «=» в ячейке, в которой будет создаваться формула. Например, введя в ячейке следующую формулу «=2+3», после нажатия «Enter» мы увидим уже результат вычислений цифру «5», а не саму формулу. Но возможности Экселя не ограничиваются таким простым способом записи формулы. Для проведения вычислений в формуле можно указывать ссылки на другие ячейки, данные в которых должны использоваться для расчетов.

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

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

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

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

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

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

Также статьи на сайте chajnikam.ru связанные с программой Excel:
Как добавить столбец в Экселе?
Как создание выпадающего списка в Excel
Создание таблиц в Excel
Объединение ячеек в Экселе

Формулы в excel как сделать

знак «меньше или равно» — «3″;B3:C3)

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

=СУММЕСЛИМН(B3:B9;B3:B9;»>2″;B3:B9;»3″)

5. Результат всех формул получится следующим.

Математические функции и графики

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

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

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

2. Для того чтобы преобразовать значение «X», нужно указать следующие формулы.

=EXP(B4) =B4+5*B4^3/2

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

4. Выделяем всю таблицу. Переходим на вкладку «Вставка». Кликаем на инструмент «Рекомендуемые диаграммы».

5. Выбираем тип «Линия». Для продолжения кликаем на «OK».

6. Результат получился довольно-таки красивый и аккуратный.

Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.

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

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

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

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

2. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

3. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

4. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Заключение

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

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

Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

Файл примеров

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

Видеоинструкция

Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Возможно, вы делаете всё правильно, но что-то упускаете из виду. С помощью этого ролика вы должны разобраться со всеми проблемами. Надеемся, что подобные уроки вам помогли. Заглядывайте к нам чаще.

Добрый день.

Когда-то, написать самостоятельно формулу в Excel — для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…

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

И так, начнем…

1. Основные операции и азы. Обучение основам Excel.

Все действия в статье будут показываться в Excel версии 2007г.

После запуска программы Excel — появляется окно со множеством клеточек — наша таблица. Главная особенность программы в том, что она может считать (как калькулятор) ваши формулы, которые вы напишете. Кстати, добавить формулу можно в каждую ячейку!

Формула должна начинаться со знака «=». Это обязательное условие. Дальше вы пишите то, что вам нужно посчитать: например, «=2+3» (без кавычек) и нажимаете по клавише Enter — в результате вы увидите, что в ячейке появился результат «5». См. скриншот ниже.

Важно! Несмотря на то, что в ячейке А1 написано число «5» — оно считается по формуле («=2+3»). Если в соседней ячейке просто текстом написать «5» — то при наведении курсора на эту ячейку — в редакторе формулы (строка сверху, Fx) — вы увидите простое число «5».

А теперь представьте, что в ячейку вы можете писать не просто значение 2+3, а номера ячеек, значения которых нужно сложить. Допустим так «=B2+C2».

Естественно, что в B2 и C2 должны быть какие-нибудь числа, иначе Excel покажет нам в ячейке A1 результат равный 0.

И еще одно важное замечание…

Когда вы копируете ячейку, в которой есть формула, например A1 — и вставляете ее в другую ячейку — то копируется не значение «5», а сама формула!

Причем, формула изменится прямо-пропорционально: т.е. если A1 скопировать в A2 — то формула в ячейке A2 будет равна «=B3+C3». Excel сам меняет автоматически вашу формулу: если A1=B2+C2, то логично, что A2=B3+C3 (все цифры увеличились на 1).

Результат, кстати, в A2=0, т.к. ячейки B3 и С3 не заданы, а значит равны 0.

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

Если вы не хотите, чтобы B2 и С2 изменялись при копировании и всегда были привязаны к этим ячейкам, то просто добавьте к ним значок «$». Пример ниже.

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

2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)

Можно, конечно, каждую ячейку складывать, делая формулу A1+A2+A3 и т.п. Но чтобы так не мучатся, есть в Excel специальная формула, которая сложит все значения в ячейках, которые вы выделите!

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

Для этого переходим в ячейку, в которой будет показываться результат и пишем формулу: «=СУММ(C2:C5)». См. скриншот ниже.

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

2.1. Сложение с условием (с условиями)

А теперь представим, что у нас есть определенные условия, т.е. сложить надо не все значения в ячейках (Кг, на складе), а лишь определенные, скажем, с ценой (1 кг.) меньше 100.

Для этого есть замечательная формула «СУММЕСЛИМН«. Сразу же пример, а затем пояснение каждого символа в формуле.

=СУММЕСЛИМН(C2:C5;B2:B5;«90» — само условие, заключается в кавычки.

Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.

Формула приобретает вид:

=СЧЁТЕСЛИМН(B2:B6;»>90″;C2:C6;«

Excel 2010: Создание сложных формул

Урок 9: Создание сложных формул

/en/excel2010/printing/content/

Создание сложных формул

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

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

Сложные формулы

Простые формулы содержат одну математическую операцию, например 5+5 . Сложные формулы содержат более одной математической операции, например 5+5-2 . Когда в формуле более одной операции, порядок операций говорит нам, какую операцию вычислять первой. Чтобы использовать Excel для расчета сложных формул, вам необходимо понимать порядок операций.

Необязательно: Вы можете загрузить этот пример для дополнительной практики.

Орден операций

Excel рассчитывает формулы на основе следующих Орден операций :

1. Операции, заключенные в Сложных столовых. , в зависимости от того, что наступит раньше
2. Сложение и вычитание , в зависимости от того, что наступит раньше

Мнемоника, которая поможет вам запомнить порядок: P аренда E извините M y D ухо A unt S .

Пример 1

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

Пример порядка действий

Пример 2

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

Порядок действий Пример Excel

1. Сначала Excel рассчитает количество проданных семян в скобках: (19*1,99)=37,81 семян белой лилии и (33*1,99)=65,67 семян лилии.
2. Во-вторых, количество семян Белой Лилии Пита будет разделено на общее количество семян Лилии Пита: 37,81/65,67=0,5758 .
3. Наконец, он умножит результат на 100, чтобы получить значение в процентах: .5758*100=57,58 .

На основе этой сложной формулы результат покажет, что 57,58% от общего числа проданных семян Пит Лили были белыми. Из этого примера видно, что важно вводить сложные формулы с правильным порядком операций. В противном случае Excel не будет точно вычислять результаты.

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

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

1. Щелкните ячейку, в которой должен появиться результат формулы (например, F11 ).
2. Введите знак равенства (=) .
3. Введите открывающую скобку , затем щелкните ячейку, содержащую первое значение , которое вы хотите использовать в формуле (например, F4 ).
4. Введите первый математический оператор (например, знак сложения).
5. Щелкните ячейку, содержащую второе значение вы хотите в формуле ( F5, например), затем введите закрывающую скобку .
6. Введите следующий математический оператор (например, знак умножения).
7. Введите следующее значение в формулу (например, 0,055 вместо 5,5% налога ).

   Ввод формулы

8. Нажмите Введите для расчета формулы. Результаты показывают, что 2,12 доллара — это налог на заказ питомника.

   Результат в F11

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

Работа со ссылками на ячейки

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

Excel будет интерпретировать ссылки на ячейки как относительные или абсолютные . По умолчанию ссылки на ячейки представляют собой относительных ссылок . При копировании или заполнении они изменяются в зависимости от относительного положения строк и столбцов. Если вы скопируете формулу (=A1+B1) в строку 2, формула изменится на (=A2+B2).

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