Как создать сложную формулу в excel: Знакомство со сложными формулами в Excel

2=4.

Содержание

Деление

Далее мы выполним все операции умножения и деления, в порядке следования слева направо. Поскольку деление встречается раньше умножения, то деление выполняется первым: 3/4=0,75.

Умножение

Теперь мы выполним оставшуюся операцию умножения: 0,75*4=3.

Сложение

Далее мы выполним все операции сложения и вычитания, в порядке следования слева направо. Поскольку сложение встречается раньше вычитания, то сложение выполняется первым: 10+3=13.

Вычитание

В заключение остается последнее действие – вычитание: 13-1=12.

В итоге мы получили ответ: 12.

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

Как видите, в этом нет ничего сложного!

Оцените качество статьи. Нам важно ваше мнение:

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

Более сложные формулы в Excel 2013

Смотрите также аргументами.2$A$2:$A$25будет вычислен подчёркнутый формула не посчитала. ячейки с формулами»). Например, чтобы представитьВвод учетом изменения диапазона остается прежней в в нескольких формулах. клавиши SHIFT+F3. диапазонов. возведения числа вВставить функцию результат на величинуПримечание:Ссылки в ячейке соотнесены= (знак равенства)равно выбранному названию

фрагмент. На следующих

Всё посчитано, просто здесь. значения 10, 20, . Введите имя, например, в листов. обеих ячейках: =$A$1.

Хотите узнать больше?

Вы также можете3. Аргументы. Существуют различные

B7:D7 C6:C8 степень, а оператор

support.office.com⁪>

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

», просто наберите =СЧЕТЕСЛИ(A1:C2,»>5″).
ячейки
Мы стараемся как можно
со строкой.
Равно

отдела изображениях мы видим нужно настроить форматВ Excel в 30 и 40, качестве аргумента формулы.

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

 задавать ссылки на типы аргументов: числа,В некоторых случаях порядок * («звездочка») — для Когда напечатаете » =СЧЁТЕСЛИ( «,A1 оперативнее обеспечивать вас

Формула с абсолютной ссылкойМеньше$H$7 результат работы формулы ячейки, числа. Смотрите формулу можно вводить введите {10,20,30,40}. ЭтаАвтозавершение формул

ссылок, при котором
абсолютной ссылкой

Ввод формулы

ячейки разных листов текст, логические значения

вычисления может повлиять
умножения. вместо ввода «A1:C2». актуальными справочными материалами
ссылается на одну>, иТеперь посмотрим, как с в статье «Число разные символы, которые константа массива является

 . Используйте раскрывающийся список автозавершения

нумеруются и строки,Смешанные ссылки одной книги либо (ИСТИНА и ЛОЖЬ), на возвращаемое формулойКонстанта представляет собой готовое вручную выделите мышьюКогда вы копируете формулу, на вашем языке. и ту жеБольше

ЛОЖЬ этой же формулой Excel. Формат».

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

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

массивы, значения ошибок значение, поэтому для (не вычисляемое) значение, этот диапазон. Excel автоматически подстраивает

1. Эта страница переведена ячейку. То естьМеньше или равно

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

В данном самоучителе мы рассказали обо всем, что связано с формулами в редакторе 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;«

Как вставлять формулы в Экселе

Способ 1: Кнопка «Вставка функции»

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

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

Затем переходите к инструменту «Вставка функции» путем клика по отведенной для этого кнопке на верхней панели.

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

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

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

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

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

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

МАКС, как и другие функции, например самая распространенная СУММ, может включать в себя несколько списков аргументов и вычислять значения из всех них. Для этого заполняйте в таком же порядке идущие следом блоки «Число2», «Число3» и т. д.

После нажатия по кнопке «ОК» или клавише Enter формула вставится в выделенную ранее ячейку с уже отобразившимся результатом. При нажатии по ней на верхней панели вы увидите синтаксическую запись формулы и по необходимости сможете ее отредактировать.

Способ 2: Вкладка «Формулы»

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

Перейдите на вкладку «Формулы» через верхнюю панель.

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

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

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

Способ 3: Ручное создание формулы

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

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

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

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

Поставьте открывающую и закрывающую скобку, внутри которых и будут написаны условия.

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

Результат формулы отобразится после нажатия по клавише Enter.

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

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

Подробнее: Полезные функции в Microsoft Excel

Способ 4: Вставка математической формулы

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

Откройте вкладку «Вставка» и разверните раздел «Символы».

Начните создание формулы, щелкнув по кнопке «Уравнение».

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

По завершении можно перемещать формулу в любое место и изменять ее внешние параметры.

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

Подробнее: Проблемы с вычислением формул в Microsoft Excel

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Формулы EXCEL с примерами — Инструкция по применению

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

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

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

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

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

В программе Excel можно производить самые сложные математические вычисления.

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

С помощью программы пользователь может выполнять не только вычисления и подсчеты, но и логические проверки.

В программе можно вычислить целый комплекс показателей, в числе которых:

максимальные и минимальные значения;
средние показатели;
проценты;
критерий Стьюдента и многое другое.

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

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

При этом необходимость вводить дополнительные данные и параметры отпадает.

Как применять простые формулы в программе?

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

Для этого необходимо ввести в одну ячейку одно число, а во вторую – другое.

Например, В Ячейку А1 – число 5, а в ячейку В1 – 3. Для того чтобы в ячейке А3 появилось суммарное значение необходимо ввести формулу:

=СУММ(A1;B1).

Вычисление суммарного значения двух чисел

Определить сумму чисел 5 и 3 может каждый человек, но вводить число в ячейку С1 самостоятельно не нужно, так как в этом и замысел расчета формул.

После введения итог появляется автоматически.

При этом если выбрать ячейку С1, то в верхней строке видна формула расчета.

Если одно из значений изменить, то перерасчет происходит автоматически.

Например, при замене числа 5 в ячейке В1 на число 8, то менять формулу не нужно, программа сама просчитает окончательное значение.

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

Сумма дробных чисел

В Excel можно производить любые арифметические операции: вычитание «-», деление «/», умножение «*» или сложение «+».

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

Любая формула должна начинаться знаком «=».

Если вначале не поставить «равно», то программа не сможет выдать необходимое значение, так как данные введены неправильно.

Создание формулы в Excel

В приведенном примере формула =СУММ(A1;B1) позволяет определить сумму двух чисел в ячейках, которые расположены по горизонтали.

Формула начинается со знака «=». Далее задана функция СУММ. Она указывает, что необходимо произвести суммирование заданных значений.

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

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

=СУММ(A1;B1;C1).

Формула суммы трех заданных чисел

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

Например,

=СУММ(A1:A10). На рисунке арифметическая операция будет выглядеть следующим образом:

Определение диапазона ячеек для формулы сложения

Также можно определить произведение этих чисел. В формуле вместо функции СУММ необходимо выбрать функцию ПРОИЗВЕД и задать диапазон ячеек.

Формула произведения десяти чисел

Совет! Применяя формулу «ПРОИЗВЕД» для определения значения диапазона чисел, можно задать несколько колонок и столбцов. При выборе диапазона =ПРОИЗВЕД(А1:С10), программа выполнит умножение всех значений ячеек в выбранном прямоугольнике. Обозначение диапазона – (А1-А10, В1-В10, С1-С10).

Комбинированные формулы

Диапазон ячеек в программе указывается с помощью заданных координат первого и последнего значения. В формуле они разделяются знаком «:».

Кроме того, Excel имеет широкие возможности, поэтому функции здесь можно комбинировать любым способом.

Если нужно найти сумму трех чисел и умножить сумму на коэффициенты 1,4 или 1,5, исходя из того, меньше ли итог числа 90 или больше.

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

=ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

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

В примере задействованы две функции – ЕСЛИ и СУММ. Первая обладает тремя аргументами:

условие;
верно;
неверно.

В задаче – несколько условий.

Во-первых, сумма ячеек в диапазоне А1:С1 меньше 90.

В случае выполнения условия, что сумма диапазона ячеек будет составлять 88, то программа выполнит указанное действие во 2-ом аргументе функции «ЕСЛИ», а именно в СУММ(А1:С3)*1,4.

Если в данном случае происходит превышение числа 90, то программа вычислит третью функцию – СУММ(А1:С1)*1,5.

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

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

Встроенные функции программы

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

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

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

Для того чтобы выбрать, следует выбрать пункт «вставить функцию».

Выбор функции из предлагаемого списка

Эту же операцию можно произвести с помощью комбинации на клавиатуре — Shift+F3 (раньше мы писали о горячих клавишах Excel).

Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.

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

Мастер функций

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

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

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

Выбор функции и заполнение полей

Далее нужно заполнить поля чисел и выбрать условие. Таким же способом можно найти самые различные функции, в том числе «СУММЕСЛИ», «СЧЕТЕСЛИ».

Функция ВПР

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

После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца.

Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий. Задача – по предложенному номеру найти фамилию.

Применение функции ВПР

Формула показывает, что первым аргументом функции является ячейка С1.

Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

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

Вычисление заданной фамилии с помощью функции ВПР

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

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

Поиск фамилии с пропущенными номерами

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

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

Округление чисел с помощью функций

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

А полученное значение можно использовать при расчетах в других формулах.

Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

Первый аргумент – 76,375, а второй – 0.

Округление числа с помощью формулы

В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

Округление происходит до целого числа. В нашем случае до 77 или 76.

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

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

Вся правда о формулах программы Microsoft Excel 2007

Формулы EXCEL с примерами — Инструкция по применению

« Предыдущая запись Следующая запись »
Работа с формулами Excel. Использование встроенных функций Excel
Создание электронной таблицы непредставимо без различных вычислений. Объясняется это просто: один из важнейших этапов изучения – освоение того, как осуществляется работа с формулами в Excel. Важно понимать, что помимо расчета различных значений программа позволяет работать с массивами: находить среднее, максимальное и минимальное значение, вычислять сумму и прочее.
Особенности расчетов в Excel
Excel позволяет пользователю создавать формулы разными способами:
ввод вручную;
применение встроенных функций.
Отличительной чертой формулы в Экселе независимо от способа создания заключается в том, что она начинается знаком равно (=). Если ячейка содержит формулу, в самой таблице пользователь видит результат вычисления. Выражение, по которому была рассчитана ячейка, отражает строка состояния.
Двойной щелчок по интересующей ячейке позволяет выделить цветом параметры, использованные в процессе вычисления.

Ручное создание формул Excel
Ввод вручную применим, когда необходимо осуществить простые расчеты (сложение, вычитание, умножение, деление) небольшого количества данных. Чтобы ввести ее следует выполнить следующие шаги:
щелчком левой кнопки мыши выделяем ячейку, где будет отображаться результат;
нажимаем знак равенства на клавиатуре;
вводим выражение;
нажимаем Enter.
Создать формулу можно с использованием чисел и при помощи ячеек, содержащих данные. В первом случае значения вводятся с клавиатуры, во втором – нужные ячейки выделяются щелчком мыши.

Между операндами ставят соответствующий знак: +, -, *, /. Легче всего их найти на дополнительной цифровой клавиатуре.
Использование функций Майкрософт Эксель
Помимо ручного ввода формулы в Экселе задаются при помощи встроенных функций. Используемые величины называют аргументами.
Для выбора требуемой функции нужно нажать на кнопку fx в строке состояния или (если вы работаете в 2007 excel) на треугольник, расположенный около значка автосуммы, выбрав пункт меню «Другие функции».

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

Категории функций Эксель
Функции, встроенные в Excel, сгруппированы в несколько категорий:
Финансовые позволяют производить вычисления, используемые в экономических расчетах, связанных обычно с ценными бумагами, начислением процентов, амортизацией и другими показателями;
Дата и время. Эти функции позволяют работать с временными данными, например, можно вычислить день недели для определенной даты;
Математические позволяют произвести расчеты, имеющие отношения к различным областям математики;
Статистические позволяют определить различные категории статистики – дисперсию, вероятность, доверительный интервал и другие;
Для обработки ссылок и массивов;
Для работы с базой данных;
Текстовые используются для проведения действия над текстовой информацией;
Логические позволяют установить условия, при которых следует выполнить то или иное действие;
Функции проверки свойств и значений.
Правила записи функций Excel

В процессе создания функций следует четко соблюдать ряд правил использования знаков препинания. Если пренебрегать этим правилом, программа не сумеет распознать функцию и аргументы, а значит, результат вычислений окажется неверным.
Если функция стоит в самом начале, перед ее написанием обязательно должен идти знак равенства. Он помогает Excel определить необходимость проведения вычислений.
Все аргументы должны быть записаны в круглых скобках. Не допускается наличие пробелов между скобкой и функцией.
Для разделения аргументов используется знак «;». Если для вычисления используется массив данных, начало и конец его разделяются двоеточием.
Редактирование формул Microsoft Excel

Нередко работа с формулами в MS Excel требует внесения изменений в нее уже после создания. Редактируют использованные аргументы, числовые значения, математические знаки.
Для изменения выражения следует щелкнуть по ячейке, в которой она содержится. После этого можно воспользоваться следующими вариантами:
кликнуть в строке состояния;
нажать на клавиатуре F2;
либо два раза щелкнуть мышью по ячейке. (как вам удобнее)
В результате любого из предложенных действий выражение в ячейке станет доступно для изменений. После их внесения для сохранения результата достаточно нажать на клавиатуре клавишу Enter.
Ошибки в формулах Excel

В случае неверного ввода аргументов результат вычислений может быть непредсказуем. В том случае, если в процессе работы с формулами в Excel возникнет ситуация, когда вычисление будет невозможно, программа сообщит об ошибке. Расшифруем наиболее часто встречающиеся:
### – ширины столбца недостаточно для отображения результата;
#ЗНАЧ! – использован недопустимый аргумент;
#ДЕЛ/0 – попытка разделить на ноль;
#ИМЯ? – программе не удалось распознать имя, которое было применено в выражении;
#Н/Д – значение в процессе расчета было недоступно;
#ССЫЛКА! – неверно указана ссылка на ячейку;
#ЧИСЛО! – неверные числовые значения.
Копирование формул Excel
В случае необходимости формулу можно внести в несколько ячеек. Для этого можно использовать несколько способов:
при ручном вводе достаточно выделить необходимый диапазон, ввести формулу и нажать одновременно клавиши Ctrl и Enter на клавиатуре;
для ранее созданного выражения необходимо подвести мышку в левый нижний угол ячейки и, удерживая зажатой левую клавишу, потянуть.
Абсолютные и относительные ссылки в Эксель
В процессе создания электронных таблиц пользователь неизбежно сталкивается с понятием ссылок. Они позволяют обозначить адрес ячейки, в которой находятся те или иные данные. Ссылка записывается в виде А1, где буква означает номер столбца, а цифра – номер строки.
В процессе копирования выражений происходит смещение ячейки, на которую оно ссылается. При этом возможно два типа движения:

при вертикальном копировании в ссылке изменяется номер строки;
при горизонтальном перенесении изменяется номер столбца.
В этом случае говорят об использовании относительных ссылок. Такой вариант полезен при создании массивных таблиц с однотипными расчетами в смежных ячейках. Пример формулы подобного типа – вычисление суммы в товарной накладной, которое в каждой строке определяется как произведение цены товара на его количество.
Однако может возникнуть ситуация, когда при копировании выражений для расчетов требуется, чтобы она всегда ссылалась на одну и ту же ячейку. Например, при переоценке товаров в прайсе может быть использован неизменный коэффициент. В этом случае возникает понятие абсолютной ссылки.
Закрепить какую-либо ячейку можно, используя знак $ перед номером столбца и строки в выражении для расчета: $F$4. Если поступить таким образом, при копировании номер ячейки останется неизменным.
Относительные ссылки
Абсолютные ссылки
Имена в формулах Эксель
При создании объемных таблиц использовать стандартные ссылки на ячейки зачастую становится неудобно. В этом случае гораздо удобнее использовать так называемые имена. То есть определенной ячейке, массиву или формуле задается конкретное имя. Такие заголовки гораздо легче запоминаются.
Еще одно преимущество использования имени в Excel проявляется при изменении структуры таблицы. В этом случае не придется вручную править все выражения. Достаточно изменить ссылки в определении имен. В результате будут скорректированы все выражения, в которых они были использованы.
Для того чтобы присвоить имя, следует выделить необходимую ячейку или область и воспользоваться соответствующим пунктом меню.
excel 2007

excel 2010
В открывшемся окне задается любое удобное пользователю название. После этого такое имя может быть использовано в любом месте, где требуется прописать ссылку на соответствующую ячейку.
Использование формул в процессе создания таблиц в программе Excel позволяет значительно упростить работу в ней и создать качественные таблицы.
Огромное количество способов их применения ведет к необходимости тщательного изучения возможностей программы. Важно также регулярно совершенствовать свои знания, стараясь изучать нововведения и подстраивая их под свою работу.
Excel 2016: создание более сложных формул
Урок 14: Создание более сложных формул
/ ru / excel2016 / intro-to-formulas / content /
Введение
У вас может быть опыт работы с формулами, которые содержат только один оператор, например 7 + 9 . 2)
Умножение и деление , в зависимости от того, что наступит первым
Сложение и вычитание , в зависимости от того, что наступит первым
Мнемоника, которая поможет вам запомнить заказ: PEMDAS или P lease E xcuse M y D ear A Unt S ally.
Щелкните стрелки в слайд-шоу ниже, чтобы узнать, как порядок операций используется для вычисления формул в Excel.
Хотя эта формула может показаться сложной, мы можем использовать порядок действий шаг за шагом, чтобы найти правильный ответ.
Сначала мы начнем с вычислений, заключенных в круглые скобки. В этом случае нам нужно вычислить только одно: 6-3 = 3.
Как видите, формула уже выглядит проще.2 = 4.
Далее мы решим любое умножение и деление, работая слева направо. Потому что деление
Видео: Более сложные формулы в Excel 2013
Посмотрите это видео, чтобы узнать, как создавать более сложные формулы с использованием нескольких операторов, ссылок на ячейки и функций.
Хотите больше?
Используйте автосумму для суммирования чисел
Суммируйте числа, создав формулу
Теперь мы создадим более сложные формулы, используя несколько операторов, ссылок на ячейки и функций.
Мы будем рассчитывать комиссию с продаж исходя из чистой выручки.
Для этого я беру продажную цену и вычитаю из нее стоимость проданных товаров.
Это указано в скобках, чтобы убедиться, что это происходит первым.
Затем я умножаю чистую прибыль на процент комиссии в B4, 3%.
Возвращает комиссию.
Давайте посмотрим, как это работает в формуле.
Поскольку ссылки на ячейки заключены в круглые скобки, сначала из продажной цены вычитается стоимость проданных товаров.
Это возвращает чистую прибыль в размере 3200 долларов.
Затем эта сумма умножается на 3%, возвращая комиссию в размере 96 долларов.
Теперь мы собираемся определить взвешенные баллы для студентов.
Под взвешенными баллами я подразумеваю, что разные тесты составляют разный процент итоговой оценки учащихся.
Тесты 1 и 2, каждое составляет 10%, а среднесрочные и заключительные тесты — по 40%.
Для этого я умножаю результат теста 1 для Боба на его вес.
Я нажимаю F4 после выбора значения веса в B3, чтобы при копировании формулы Вес для теста 1 всегда оставался ячейкой B3.
Это называется Абсолютная ссылка ячейки .
Затем я складываю среднесрочную оценку Боба, умноженную на его вес в B4, и снова нажимаю F4.
Я выполняю те же шаги для теста 2 и финального теста.
Чтобы скопировать формулу, я щелкаю ячейку, помещаю указатель мыши в правый нижний угол ячейки, пока не появится черный знак +, щелкаю левой кнопкой мыши и перетаскиваю границу вправо.
Вот взвешенные баллы учащихся.
Давайте посмотрим, что происходит в этой формуле для взвешенной оценки Боба.
Каждая из оценок за тест и их веса умножаются, в результате получается эта формула.
Затем числа складываются, в результате получается 74,5.
Какое значение возвращает эта формула? Сначала мы делаем самые внутренние круглые скобки, 5 минус 3 равно 2, в результате получается эта формула.
Затем мы делаем оставшиеся круглые скобки, 10 умножаем на 2 равно 20 и 1 минус 4 равно -3, в результате получаем эту формулу.
Теперь мы выполняем умножение и деление слева направо.
6 умножить на 20 — 120, разделить на 3 — 40, в результате получится эта формула.
Наконец, мы делаем сложение и вычитание слева направо. 4 + 40 это 44, — 3 это 41.
Вы можете использовать функции в формулах с несколькими операторами, включая вложенные круглые скобки.
Функции и ссылки на ячейки оцениваются и обрабатываются как их результирующие числа.
Скобки, следующие за именем функции, действуют только как контейнер для аргументов функции, таких как числа или ссылки на ячейки, и не влияют на порядок операций в формуле.
Давайте посмотрим, что происходит в этой формуле.
Функции и ссылки на ячейки вычисляются, в результате получается эта формула. Операции в круглых скобках выполняются первыми, в результате получается формула 12 минус 3, а это 9.
Теперь у вас есть хорошее представление о том, как выполнять базовую математику в Excel.
Конечно, есть чему поучиться.
Итак, ознакомьтесь с кратким изложением курса в конце и, что лучше всего, изучите Excel 2013 самостоятельно.
Как создать сложную формулу, шаг за шагом
Некоторые формулы, которые вы видите в Excel, могут быть чрезвычайно сложными. Но все сложные формулы строятся из простых шагов. В этом видео мы шаг за шагом построили более сложную формулу.
Когда вы смотрите на сложную формулу в Excel, на первый взгляд вы можете быть полностью сбиты с толку. Но все сложные формулы — это всего лишь небольшие шаги, сложенные вместе.
Позвольте мне показать вам пример
Вот список имен.Мы хотим вытащить имя из полного имени.
В Excel есть функция LEFT, которая извлекает текст слева, так что начнем с этого.
Вместо того, чтобы объяснять, как работает LEFT, давайте просто попробуем посмотреть, что он делает.
Как только я открою круглые скобки, Excel сообщит нам, что нам нужно предоставить. С помощью LEFT нам нужно предоставить текст, который является обязательным, и что-то под названием num_chars, что является необязательным.
Давайте просто добавим наши имена в функцию и опустим num_chars, чтобы посмотреть, что произойдет.
Мы получаем первый символ. Итак, начало.
Теперь добавим значение для num_chars. Это будет меняться для каждого имени, но для простоты я просто жестко закодирую число 5.
Когда я копирую эту формулу, я получаю первые пять символов каждого имени.
Итак, теперь вы можете понять, в чем заключается основная проблема. Нам нужно найти способ дать функции LEFT правильное количество символов для извлечения для каждого имени.
Для этого мы можем использовать другую функцию под названием FIND.
FIND принимает два обязательных аргумента: текст, который мы ищем (пробел), и текст, который мы ищем — в этом случае мы будем использовать адрес ячейки, содержащей имя.
Последний аргумент не обязателен, поэтому мы его опустим.
Вы можете видеть, что НАЙТИ возвращает число. И это число представляет собой положение символа пробела в имени.
Мы можем использовать это число в функции LEFT для num_chars.
Когда я его копирую, мы получаем список имен.
Итак, это выглядит правильно, но на самом деле есть проблема. LEFT извлекает все символы до заданного вами числа включительно. Это означает, что на самом деле у нас есть пробел в конце всех этих имен.
Чтобы избавиться от места, мне нужно сделать одно простое изменение.
Мне нужно вычесть 1 из результата, который мы получаем от FiND. Таким образом, мы не будем занимать место.
Наконец, я создам комбинированную формулу в последнем столбце. Окончательная формула выглядит более сложной, но на самом деле это просто функция LEFT, с небольшой причудливой работой с использованием FIND, чтобы вычислить количество символов для извлечения.
Это простой пример, но идея одинакова для всех сложных формул.
Начните с большой картины и работайте маленькими шагами, чтобы получить нужный результат.
Как создать формулу в Excel, шаг за шагом от простого к сложному
Как создать формулу в Excel
Как создать простую формулу в Excel
Для создания формулы в Excel необходимо начинать со знака равенства «=». Если знак равенства отсутствует, то все, что введено в ячейку, не будет считаться формулой.
Вот как создать простую формулу, которая представляет собой формулу для сложения, вычитания, умножения и деления. Формула сложения с использованием знака плюс «+», формула вычитания с использованием знака минус «-», формула умножения с использованием знака звездочки «*» и формулы деления с использованием косой черты «/».
Формула сложения
В ячейках B1 и B2 два числа. Как создать формулу в Excel, чтобы добавить их обоих?
Есть несколько способов написать формулу.Первый способ — использовать клавиатуру и клавиши со стрелками, второй — использовать клавиатуру и мышь, а третий — использовать клавиатуру, вводя непосредственно формулу и адрес соответствующей ячейки.
Для вышеуказанного сложения формула будет использоваться первым способом.
Поместите курсор в ячейку B4 и введите знак равенства «=»
Нажмите кнопку со стрелкой вверх, наведите указатель на ячейку B1, курсор превратится в пунктирную линию.
Введите знак плюса «+» для операции сложения
Снова нажмите кнопку со стрелкой вверх, наведите указатель на ячейку B2
Нажмите клавишу ENTER
Результат показан ниже
Формула вычитания
Как написать в excel формулу для вычитания числа1 на число2 ?.Формулу редукции использовать вторым способом, то есть с помощью клавиатуры и мыши.
Поместите курсор в ячейку B5 и введите знак равенства «=»
Щелкните мышью ячейку B1
Введите отрицательный знак «-» для операции вычитания
Щелкните мышью ячейку B2
Нажмите клавишу ENTER
Более подробную информацию вы можете увидеть на анимации ниже.
Формула умножения
Как составить в excel формулу для умножения числа1 на число2.Для формулы умножения используется третий способ использования клавиатуры, записывая непосредственно формулу и адрес задействованной ячейки.
Поместите курсор в ячейку B6 и введите знак равенства «=»
Тип B1
Введите звездочку «*» для операций умножения
Тип B2
Нажмите клавишу ENTER
Более подробную информацию вы можете увидеть на анимации ниже.
Дивизион Формула
Как делать формулы в Excel, чтобы разделить число1 на число2.
Поместите курсор в ячейку B7 и введите знак равенства «=»
Наведите курсор на ячейку B1
Введите косую черту «/» для операции деления
Наведите курсор на ячейку B2
Нажмите клавишу ENTER
Результат показан ниже
Как просматривать формулы в Excel
Самый простой способ увидеть формулу в ячейке — это посмотреть на строку формул. Наведите курсор на ячейку, содержащую формулу, тогда в строке формул отобразится формула в ячейке.Строка формул расположена под меню ленты.
На рисунке выше показана формула, содержащаяся в ячейке B4. Чтобы увидеть формулы в других ячейках, просто переместите курсор в нужную ячейку и просмотрите формулу в строке формул.
Панель формул может отображать формулы только в активной ячейке, то есть может отображаться только одна формула. Чтобы отобразить все формулы на листе, прочтите статью ниже «Как отображать формулы ячеек в Excel»
Как редактировать формулу в Excel
Есть два способа редактировать формулу: с помощью клавиши F2 или с помощью строки формул.
Редактирование клавишей F2
Установите курсор в ячейку, содержащую формулу, затем нажмите клавишу F2. Появится содержимое формулы, а ячейки, участвующие в формуле, будут отмечены цветным прямоугольником.
На рисунке ниже показана существующая формула сложения в ячейке B4. Формула состоит из двух ячеек: B1 и B2. Адрес ячейки B1 окрашен в голубой цвет, тогда ячейка B1 будет окружена линией такого же цвета, как и ячейка B2, цвет линий вокруг нее такой же, как цвет адреса ячейки B2.
Например, формула будет отредактирована путем добавления числа 2. Введите знак плюса «+», число 2, а затем нажмите клавишу ENTER.
Результаты показаны ниже. Формула в ячейке B4 изменилась.
Редактирование с помощью строки формул
Поместите курсор в ячейку, содержащую формулу, затем щелкните раздел панели формул. Формула в ячейке появится автоматически. Дисплей будет таким же, как при нажатии клавиши F2.
На рисунке ниже показана существующая формула вычитания в ячейке B5. Например, формула будет редактироваться путем вычитания числа 2. Введите число 2 со знаком минус «-», затем нажмите клавишу ENTER.

Как скопировать формулу в Excel
Ячейку, содержащую формулу, можно скопировать, как и любые другие данные. Разница, которая копируется, — это формула, а не значение ячейки, и адрес ячейки, образующий формулу, будет изменен в зависимости от местоположения.
См. Изображение ниже. Ячейка B4 содержит формулу, складывающую значение ячеек B1 и B2. Формула будет скопирована и помещена в диапазон C4: F4.
Поместите курсор в ячейку B4. Сделайте копию (CTRL + C). Выберите диапазон C4: F4, затем вставьте его (CTRL + V).
Результат показан ниже.
Значение ячеек от C4 до F4 не равно значению ячейки B4, поскольку Excel скопировал формулу, а не значение ячейки. Ячейка C4 содержит формулу, складывающую значения C1 и C2, а также ячейку D4 до F4; все они содержат формулу, которая складывает значения ячеек в строке 1 и строке 2 в одном столбце.
Другой способ скопировать формулу в excel
Помимо использования клавиатуры, есть еще один способ скопировать формулу — использовать мышь. Например, формула из ячейки B5 будет скопирована. Щелкните ячейку B5, наведите указатель мыши на нижний правый угол ячейки B5, пока форма курсора не станет тоньше. Щелкните и удерживайте, затем перетащите курсор до ячейки F5. Результат показан ниже.
Есть ли другой способ скопировать формулу мышкой, конечно 😊.Например, будет скопирована формула из диапазона B6: B7. Выберите диапазон B6: B7, затем щелкните правой кнопкой мыши и выберите копию. Выберите диапазон C6: F7, щелкните правой кнопкой мыши и выберите «Вставить». Результат как показано ниже.
Как вставить Special в Excel
Если копируется ячейка, содержащая формулу, копируются формулы, а не ее значение. Вопрос в том, что если вы хотите скопировать значение, а не формулы. Решение — использование специальной пасты.
Например, есть данные, подобные изображению выше.Диапазон A4: F7 в основном содержит формулу, что, если диапазон скопирован и помещен в диапазон A9: F11 ?.
Выберите диапазон A4: F7. Сделайте копию (CTRL + C). Поместите курсор в ячейку A9, а затем сделайте вставку (CTRL + V). Результаты показаны ниже.
Результаты разные. Если этот флажок установлен, ячейка B9 содержит формулу = B6 + B7, а также другие ячейки в диапазоне B9: F12, содержащие формулы.
Только для копирования его значения. Выберите диапазон A4: F7. Сделайте копию (CTRL + C). Поместите курсор в ячейку A9, а затем выполните специальную вставку (CTRL + ALT + V).Появится диалоговое окно, как показано ниже. Выберите «Значения», затем нажмите ОК.
Результаты показаны ниже.
Результаты соответствуют диапазону A4: F7. При повторной проверке содержимое ячейки B9 и других ячеек в диапазоне B9: F12 не является формулой, а является числом.
Как отображать формулы ячеек в Excel
Чтобы увидеть существующую формулу в уже обсужденной ранее ячейке. Есть один недостаток, формула видна только в одной ячейке.Если вы хотите увидеть все формулы, существующие на листе, единственный способ — использовать команду «Показать формулы».
Местоположение находится в меню ленты, вкладке «Формулы», формуле группы аудита. Щелкните один раз, и все данные, полученные из формулы, отобразят ее формулу. Щелкните еще раз; он вернется в исходное состояние.
Если вы хотите использовать команду Показать формулы быстрее, используйте сочетание клавиш CTRL + `(серьезное ударение, в левой части клавиатуры 1 и над левой вкладкой)
Формулы
В этом разделе рассматриваются основные навыки ввода формул в рабочий лист Excel.Целью данной главы является построение личного кассового бюджета. Большинство финансовых консультантов рекомендуют всем домохозяйствам составлять и поддерживать личный бюджет для достижения и поддержания крепкого финансового здоровья. Составление и ведение личного бюджета — это навык, который вы можете практиковать в любой момент своей жизни. Независимо от того, управляете ли вы своими расходами во время учебы в колледже или поддерживаете финансы семьи из четырех человек, личный бюджет может быть жизненно важным инструментом при принятии финансовых решений.Excel может превратить управление вашими деньгами в увлекательное и полезное занятие.
Рисунок 2.1 «Заполненная книга личного кассового бюджета» показывает заполненную книгу, которая будет продемонстрирована в этой главе. Обратите внимание, что эта книга содержит четыре листа. Первый рабочий лист, Сводка бюджета , содержит формулы, которые используют или ссылаются на данные из трех других рабочих листов. В результате рабочий лист «Сводка бюджета » служит для обзора данных, которые были введены и рассчитаны на трех других рабочих листах книги.
Создание базовой формулы
Видеоурок: Основные формулы
Формулы Используются для вычисления различных математических результатов в Excel и могут использоваться для создания практически любых пользовательских вычислений, необходимых для вашей цели. используются для расчета различных математических результатов в Excel и могут использоваться для создания практически любых пользовательских расчетов, необходимых для вашей цели. Кроме того, при построении формулы в Excel вы используете местоположения ячеек, которые при добавлении в формулу становятся ссылками на ячейки Адреса ячеек, используемые в формулах.Excel ссылается на значения, введенные в ячейки, используемые в формулах, для получения выходных данных. Когда значения изменяются в ячейке, на которую ссылается формула, Excel автоматически создает новые выходные данные. Когда ссылка на ячейку используется сама по себе в месте расположения ячейки (адресу ячейки предшествует знак равенства [ = ]), Excel отображает значение, введенное в эту ячейку. Это означает, что Excel использует или ссылается на введенное число в ячейку при вычислении математического вывода.В результате, когда числа в ссылках на ячейки изменяются, Excel автоматически создает новый результат. Это то, что дает Excel возможность создавать различные сценарии «что, если», которые будут объяснены позже в этой главе.
Чтобы продемонстрировать построение базовой формулы, мы начнем работать с рабочим листом «Сведения о бюджете» в рабочей книге «Персональный бюджет», который показан на рисунке 2.2 «Рабочий лист с подробным бюджетом». Чтобы заполнить этот рабочий лист, мы добавим несколько формул и функций.Таблица 2.1 «Определения категорий расходов» содержит определения для каждой из категорий расходов, перечисленных в диапазоне A3: A11. Когда вы разрабатываете личный бюджет, эти категории определяются на основе того, как вы тратите свои деньги. Вполне вероятно, что каждый человек может иметь разные категории или определять одни и те же категории по-разному. Поэтому важно просмотреть определения в таблице 2.1 «Определения категорий расходов», чтобы понять, как мы определяем эти категории, прежде чем продолжить.
Рисунок 2.2 Рабочий лист деталировки бюджета
Таблица 2.1 Определения категорий расходов
Категория Определение
Коммунальные услуги Деньги, потраченные на электричество, тепло и воду, а также на кабель, телефон и доступ в Интернет
Продукты питания Деньги, потраченные на продукты, туалетные принадлежности и сопутствующие товары
Бензин Деньги на топливо для автомобилей
Одежда Деньги, потраченные на одежду, обувь и аксессуары
Страхование Деньги, потраченные на страхование домовладельцев или автомобилей
Налоги Деньги, потраченные на школу и налоги на имущество (в этом примере личного бюджета предполагается, что мы владеем собственностью).
Развлечения Деньги, потраченные на развлечения, включая рестораны, билеты в кино и театр, вечеринки и т. Д.
Отпуск Деньги, потраченные на отпуск
Разное Включает любые другие категории расходов, такие как учебники, программное обеспечение, журналы, школьные или рабочие принадлежности и т. Д.
Первая формула, которую мы добавим в рабочий лист Сведения о бюджете , рассчитает значения ежемесячных расходов.Формула будет построена таким образом, чтобы значения из столбца «Годовые расходы» делились на 12. Это покажет, сколько денег будет потрачено в месяц для каждой из категорий, перечисленных в столбце A. Ниже объясняется, как эта формула работает. создано:
Щелкните вкладку рабочего листа Сведения о бюджете , чтобы открыть рабочий лист.
Щелкните ячейку C3.
Введите знак равенства ( = ). Когда первый символ, введенный в ячейку, является знаком равенства, он сигнализирует Excel о необходимости выполнения вычисления или создания логического вывода.
Тип D3 . Это добавляет D3 к формуле, которая теперь является ссылкой на ячейку. Excel будет использовать любое значение, введенное в ячейку D3, для вывода.
Введите символ косой черты (/). Это символ деления в Excel. Как показано в таблице 2.2 «Математические операторы Excel», математические операторы в Excel немного отличаются от тех, которые используются в обычном калькуляторе.
Введите число 12 .При этом значение в ячейке D3 делится на 12. В этой формуле — число или постоянное числовое значение, используемое при построении формул. Степень / Показатель
Почему?
Использовать ссылки на ячейки
Ссылки на ячейки позволяют Excel динамически создавать новые выходные данные при изменении одного или нескольких входов в ссылочных ячейках.Ссылки на ячейки также позволяют отслеживать, как рассчитываются результаты в формуле. В результате, вам никогда не следует использовать калькулятор для определения математического вывода и ввода его в ячейку рабочего листа . Это лишает Excel возможности ссылаться на ячейки, а также лишает вас возможности отслеживать формулу для определения того, как создаются выходные данные.
Рисунок 2.3 «Добавление формулы в рабочий лист» показывает, как формула отображается в ячейке C3 до нажатия клавиши ENTER.Рисунок 2.4 «Вывод формулы для ежемесячных расходов» показывает вывод формулы после нажатия клавиши ENTER. Ежемесячные расходы на коммунальные услуги составляют 250 долларов США, поскольку формула берет годовые расходы в ячейке D3 и делит его на 12. Если значение в ячейке D3 изменяется, формула автоматически выводит новый результат. Мы рассчитываем ежемесячные расходы для каждой категории, потому что людям часто платят и выставляют счета за эти товары на ежемесячной основе. Эта формула позволяет сравнить ваш ежемесячный доход с вашими ежемесячными счетами, чтобы определить, достаточно ли у вас дохода для оплаты этих расходов.
Рисунок 2.3 Добавление формулы в рабочий лист
Рисунок 2.4 Вывод формулы для ежемесячных расходов
Почему?
Использовать универсальные константы
Если вы используете константы или числовые значения в формуле Excel, они должны быть универсальными константами, которые не меняются, например количество дней в неделе, недель в году и т. Д. Не вводите значения, существующие в ячейках, в формулу Excel .Это лишит Excel возможности ссылаться на ячейки, а это означает, что если значение в ячейке, которую вы используете в формуле, изменится, Excel не сможет создать новый результат.
формул и функций в Excel
Введите формулу | Редактировать формулу | Приоритет операторов | Копировать / вставить формулу | Вставить функцию | 10 лучших функций | Ссылки на ячейки
Формула — это выражение, которое вычисляет значение ячейки. Функции являются предопределенными формулами и уже доступны в Excel .
Например, ячейка A3 ниже содержит формулу, которая добавляет значение ячейки A2 к значению ячейки A1.
Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1: A2.
Введите формулу
Чтобы ввести формулу, выполните следующие действия.
1. Выберите ячейку.
2. Чтобы сообщить Excel, что вы хотите ввести формулу, введите знак равенства (=).
3. Например, введите формулу A1 + A2.
Совет: вместо ввода A1 и A2 просто выберите ячейки A1 и A2.
4. Измените значение ячейки A1 на 3.
Excel автоматически пересчитывает значение ячейки A3.Это одна из самых мощных функций Excel!
Редактировать формулу
Когда вы выбираете ячейку, Excel показывает значение или формулу ячейки в строке формул.
1. Чтобы изменить формулу, щелкните строку формул и измените формулу.
2. Нажмите Enter.
Приоритет оператора
Excel использует порядок вычислений по умолчанию.Если часть формулы заключена в круглые скобки, она будет вычислена первой. Затем он выполняет вычисления умножения или деления. Как только это будет завершено, Excel добавит и вычтет остаток из вашей формулы. См. Пример ниже.
Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.
Другой пример,
Сначала Excel вычисляет часть в круглых скобках (A2 + A3).Затем он умножает этот результат на значение ячейки A1.
Копировать / вставить формулу
Когда вы копируете формулу, Excel автоматически корректирует ссылки на ячейки для каждой новой ячейки, в которую копируется формула. Чтобы понять это, выполните следующие действия.
1. Введите формулу, показанную ниже, в ячейку A4.
2а. Выделите ячейку A4, щелкните правой кнопкой мыши и затем нажмите Копировать (или нажмите CTRL + c)…
… затем выберите ячейку B4, щелкните правой кнопкой мыши и затем нажмите Вставить в разделе «Параметры вставки:» (или нажмите CTRL + v).
2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите его в ячейку B4. Это намного проще и дает точно такой же результат!
Результат. Формула в ячейке B4 ссылается на значения в столбце B.
Вставить функцию
Все функции имеют одинаковую структуру. Например, СУММ (A1: A4). Имя этой функции — СУММ. Часть в скобках (аргументы) означает, что мы передаем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Непросто запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, в этом вам поможет функция вставки в Excel.
Чтобы вставить функцию, выполните следующие шаги.
1. Выберите ячейку.
2. Нажмите кнопку «Вставить функцию».
Откроется диалоговое окно «Вставить функцию».
3. Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ в категории «Статистика».
4. Щелкните OK.
Откроется диалоговое окно «Аргументы функции».
5.Щелкните поле Диапазон и выберите диапазон A1: C2.
6. Щелкните поле Criteria и введите> 5.
7. Щелкните OK.
Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек, превышающих 5.
Примечание: вместо использования функции вставки просто введите = СЧЁТЕСЛИ (A1: C2, «> 5»). Когда вы придете к: = СЧЁТЕСЛИ (вместо ввода A1: C2, просто выберите диапазон A1: C2.
10 основных функций
Ниже вы можете найти обзор 10 наиболее часто используемых функций Excel (+ дополнительные приемы при создании формул в Excel). Загрузите файл Excel и попробуйте вставить эти функции.
1. СЧЕТ
Чтобы подсчитать количество ячеек, содержащих числа, используйте функцию СЧЁТ.
Примечание: используйте COUNTBLANK для подсчета пустых ячеек и COUNTA для подсчета всех непустых ячеек.
2. СУММА
Чтобы суммировать диапазон ячеек, используйте функцию СУММ (см. Второе изображение на этой странице). Вы также можете использовать функцию СУММ в Excel для суммирования всего столбца.
Примечание. Вы также можете использовать функцию СУММ в Excel для суммирования всей строки. Например, = СУММ (5: 5) суммирует все значения в 5-й строке.
3. IF
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно истинно, и другое значение, если ложно.
Объяснение: если оценка больше или равна 60, функция ЕСЛИ возвращает Pass, иначе она возвращает Fail. Помните, чтобы быстро скопировать эту формулу в другие ячейки, щелкните в правом нижнем углу ячейки C2 и перетащите его в ячейку C6.
4. СРЕДНЕЕ
Чтобы вычислить среднее значение группы чисел, используйте функцию СРЕДНЕЕ (здесь нет ракетостроения). Приведенная ниже формула вычисляет среднее значение трех первых чисел в диапазоне A1: A6.
Объяснение: функция НАИБОЛЬШИЙ возвращает константу массива {20,15,10}. Эта константа массива используется в качестве аргумента функции СРЗНАЧ, давая результат 15.
5. СЧЕТЕСЛИ
Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые содержат ровно звездочку + последовательность из нуля или более символов.
Объяснение: звездочка (*) соответствует серии из нуля или более символов.Посетите нашу страницу о функции СЧЁТЕСЛИ для получения дополнительной информации и примеров.
6. СУММИФ
Функция СУММЕСЛИ ниже суммирует значения в диапазоне B1: B5, если соответствующие ячейки в диапазоне A1: A5 содержат ровно круг + 1 символ.
Объяснение: вопросительный знак (?) Соответствует ровно одному символу. Посетите нашу страницу о функции СУММЕСЛИ для получения дополнительной информации и примеров.
7. ВПР
Функция ВПР ниже ищет значение 53 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент). Значение 4 (третий аргумент) указывает функции VLOOKUP вернуть значение в той же строке из четвертого столбца красной таблицы.
Примечание. Посетите нашу страницу о функции ВПР, чтобы узнать больше об этой мощной функции Excel.
8. МИН.
Чтобы найти минимальное значение, используйте функцию MIN. Это так просто, как кажется.
9. МАКС
Чтобы найти максимальное значение, используйте функцию MAX.
Примечание: посетите нашу главу о статистических функциях, чтобы узнать больше о Excel и статистике.
10.SUMPRODUCT
Чтобы вычислить сумму произведений соответствующих чисел в одном или нескольких диапазонах, используйте мощную функцию СУММПРОИЗВ в Excel.
Объяснение: функция СУММПРОИЗВ выполняет следующие вычисления: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500. Загрузите файл Excel и попробуйте вставить эти функции.
Ссылки на ячейки
Ссылки на ячейки в Excel очень важны.По умолчанию Excel использует относительные ссылки. При копировании формулы относительная ссылка изменяется.
1. См. Формулу в ячейке D2 ниже. Ячейка D2 ссылается на ячейки B2 и C2 (указывает на них). Обе ссылки относительны.
2. Чтобы быстро скопировать формулу из ячейки D2 в другие ячейки, выберите ячейку D2, щелкните в правом нижнем углу ячейки D2 и перетащите ее в ячейку D5.
Чек:
Заключение: ячейка D3 ссылается на ячейки B3 и C3.Ячейка D4 ссылается на ячейки B4 и C4. Ячейка D5 ссылается на ячейки B5 и C5.
Абсолютная ссылка в Excel относится к фиксированному месту на листе. Когда вы копируете формулу, абсолютная ссылка никогда не изменяется.
3. Ячейка C2 ниже относится к ячейке B2 и ячейке E2. Обе ссылки относительны.
4. Вот что происходит, когда мы копируем формулу из ячейки C2 в ячейку C3.
5. Зафиксируйте ссылку на ячейку E2, поместив символ $ перед буквой столбца и номером строки.
6. Чтобы быстро скопировать формулу из ячейки C2 в другие ячейки, выберите ячейку C2, щелкните в правом нижнем углу ячейки C2 и перетащите ее в ячейку C7.
Чек:
Вывод: абсолютная ссылка ($ E $ 2) остается прежней, а относительная ссылка (B2) меняется на B3, B4, B5, B6 и B7.