Excel базовые формулы. Формулы в Excel: создание и расчет. Формула вычисления исходной суммы. Сумма без НДС

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

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

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

Максимум, минимум и средний показатель.
- Процентное соотношение чисел
- Разные критерии, включая критерий Стьюдента
- А так же еще много полезных показателей

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

Применение простых формул в Excel.

Рассмотрим формулы на простом примере суммы двух чисел для того, чтобы понять принцип их работы. Примером будет являться сумма двух чисел. Переменными будут выступать ячейки А1 и В1, в которые пользователь будет вводить числа. В ячейке С3 выведется сумма этих двух чисел в том случае, если на ней задана следующая формула:
=СУММ(А1;В1).

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

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

Как создать формулу в программе?

В прошлом примере рассмотрен пример суммы двух чисел, с чем справится каждый и без помощи Excel, но когда надо посчитать сумму более, чем 2 значения, то это займет большее время, поэтому можно выполнить сумму сразу трех ячеек, для этого просто нужно написать следующую формулу в ячейку D1:
=СУММ(А1:B1;C1).

Но бывают случаи, когда нужно сложить, к примеру, 10 значений, для этого можно использовать следующий вариант формулы:
=СУММ(А1:А10), что будет выглядеть следующим образом.

И точно так же с произведением, только вместо СУММ использовать ПРОИЗВЕД.

Также можно использовать формулы для нескольких диапазонов, для этого нужно прописать следующий вариант формулы, в нашем случае произведения:
=ПРОИЗВЕД(А1-А10, В1-В10, С1-С10)

Комбинации формул.

Кроме того, что можно задать большой диапазон чисел, можно также и комбинировать различные формулы. К примеру, нам нужно сложить числа определенного диапазона, и нужно посчитать их произведение с умножением на разные коэффициенты при разных вариантах. Допустим, нам нужно узнать коэффициент 1.4 от суммы диапазона (А1:С1) если их сумма меньше 90, но если их сумма больше или равна 90, то тогда нам нужно узнать коэффициент 1.5 от этой же суммы. Для этой, с виду сложной, задачи задается всего одна простая формула, которая объединяет в себе две базовые формулы, и выглядит она так:

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

Как можно заметить, в этом примере были использованы две формулы, одна из которых ЕСЛИ, которая сравнивает указанные значения, а вторая СУММ, с которой мы уже знакомы. Формула ЕСЛИ имеет три аргумента: условие, верно, неверно.

Рассмотрим формулу поподробнее опираясь на наш пример. Формула ЕСЛИ получает три аргумента. Первым является условие, которое проверяет меньше сумма диапазона 90 или нет. Если условие верно, то выполняется второй аргумент, а если ложно, то будет выполнен третий аргумент. То есть, если мы введем значение в ячейки, сумма которых будет меньше 90, то выполнится умножение этой суммы на коэффициент 1,4, а если их сумма будет больше или равна 90, то тогда произойдет умножение на коэффициент 1,5.

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

Базовые функции Excel

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

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

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

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

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

Функция ВПР

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

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

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

Это происходит из-за того, что данная функция имеет еще и четвертый аргумент, который имеет только два значения, ИСТИНА или ЛОЖЬ, а так как он у нас не задан, то по умолчанию он стал в позицию ИСТИНА.

Округление чисел, используя стандартные функции

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

Чтобы округлить значение ячейки в большую сторону вам понадобится формула "ОКРУГЛВВЕРХ". Стоит обратить внимание на то, что формула принимает не один аргумент, что было бы логичным, а два, и второй аргумент должен быть равен нулю.

На рисунке хорошо видно, как произошло округление в большую сторону. Следовательно, информацию в ячейке А1 можно менять как и во всех других случаях, потому что она является переменной, и используется в качестве значения аргумента. Но если вам необходимо округлить число не в большую, а меньшую сторону, то эта формула не поможет вам. В данном случае вам необходима формула ОКРУГЛВНИЗ. Округление произойдет к ближайшему целому числу, которое меньше нынешнего дробного значения. То есть, если в примере на картинке задать вместо формулы ОКРУГЛВВЕРХ формулу ОКРУГЛВНИЗ, то результат будет уже не 77, а 76.

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

Доброго дня уважаемые радиолюбители!
Приветствую вас на сайте “ “

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

Точно так же эффективно использовать формулы для модернизации уже готовых схем. К примеру, для того, чтобы выбрать правильный резистор в схеме с лампочкой, можно применить базовый закон Ома для постоянного тока (о нем можно будет прочесть в разделе “Соотношения закона Ома” сразу после нашего лирического вступления). Лампочку можно заставить, таким образом, светить более ярко или, наоборот - притушить.

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

Соотношения закона Ома

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

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

U - напряжение (В),

I - ток (А),

Р - мощность (Вт),

R - сопротивление (Ом),

Потренируемся на следующем примере: пусть нужно найти мощность схемы. Известно, что напряжение на ее выводах составляет 100 В, а ток- 10 А. Тогда мощность согласно закону Ома будет равна 100 х 10 = 1000 Вт. Полученное значение можно использовать для расчета, скажем, номинала предохранителя, который нужно ввести в устройство, или, к примеру, для оценки счета за электричество, который вам лично принесет электрик из ЖЭК в конце месяца.

А вот другой пример: пусть нужно узнать номинал резистора в цепи с лампочкой, если известно, какой ток мы хотим пропускать через эту цепь. По закону Ома ток равен:

I = U / R

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

Что же в этой формуле есть что? Рассмотрим переменные подробнее.

> U пит (иногда также обозначается как V или Е): напряжение питания. Вследствие того, что при прохождении тока через лампочку на ней падает какое-то напряжение, величину этого падения (обычно рабочее напряжение лампочки, в нашем случае 3,5 В) нужно вычесть из напряжения источника питания. К примеру, если Uпит = 12 В, то U = 8,5 В при условии, что на лампочке падает 3,5 В.

> I : ток (измеряется в амперах), который планируется пропустить через лампочку. В нашем случае – 50 мА. Так как в формуле ток указывается в амперах, то 50 миллиампер составляет лишь малую его часть: 0,050 А.

> R : искомое сопротивление токоограничивающего резистора, в омах.

В продолжение, можно проставить в формулу расчета сопротивления реальные цифры вместо U, I и R:

R = U/I = 8,5 В / 0,050 А= 170 Ом

Расчёты сопротивления

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

Почему нужно обращать внимание на способ соединения компонентов между собой? На то есть сразу несколько причин.

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

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

Расчет сопротивления последовательных резисторов

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

Rобщ = Rl + R2 + R3 + … (столько раз, сколько есть элементов)

В данном случае величины Rl, R2, R3 и так далее - сопротивления отдельных резисторов или других компонентов цепи, а Rобщ - результирующая величина.

Так, к примеру, если имеется цепь из двух соединенных последовательно резисторов с номиналами 1,2 и 2,2 кОм, то суммарное сопротивление этого участка схемы будет равно 3,4 кОм.

Расчет сопротивления параллельных резисторов

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

R общ = R1 * R2 / (R1 ­­+ R2)

где R1 и R2 - сопротивления отдельных резисторов или других элементов цепи, а Rобщ -результирующая величина. Так, если взять те же самые резисторы с номиналами 1,2 и 2,2 кОм, но соединенные параллельно, получим

776,47 = 2640000 / 3400

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

Расчёты ёмкости

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

Расчет емкости параллельных конденсаторов

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

Собщ = CI + С2 + СЗ + …

В этой формуле CI, С2 и СЗ - емкости отдельных конденсаторов, а Собщ суммирующая величина.

Расчет емкости последовательных конденсаторов

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

Собщ = С1 * С2 /(С1+С2)

где С1 и С2 - значения емкости каждого из конденсаторов, а Собщ - общая емкость цепи

Расчет емкости трех и более последовательно соединенных конденсаторов

В схеме имеются конденсаторы? Много? Ничего страшного: даже если все они связаны последовательно, всегда можно найти результирующую емкость этой цепи:

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

Расчёт энергетических уравнений

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

ватт-часы = Р х Т

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

Расчёт постоянной ёмкости RC-цепочки

В электронных схемах часто используются RC-цепочки для обеспечения временных задержек или удлинения импульсных сигналов. Самые простые цепочки состоят всего лишь из резистора и конденсатора (отсюда и происхождение термина RC-цепочка).

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

Каким же образом можно рассчитать постоянную времени RC-цепочки? Поскольку эта схема состоит из резистора и конденсатора, в уравнении используются значения сопротивления и емкости. Типичные конденсаторы имеют емкость порядка микрофарад и даже меньше, а системными единицами являются фарады, поэтому формула оперирует дробными числами.

T = RC

В этом уравнении литера Т служит для обозначения времени в секундах, R - сопротивления в омах, и С - емкости в фарадах.

Пусть, к примеру, имеется резистор 2000 Ом, подключенный к конденсатору 0,1 мкФ. Постоянная времени этой цепочки будет равна 0,002 с, или 2 мс.

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

Расчёты частоты и длины волны

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

Расчет частоты сигнала

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

Частота = 300000 / длина волны

Длина волны в данной формуле выражается в миллиметрах, а не в футах, аршинах или попугаях. Частота же дана в мегагерцах.

Расчет длины волны сигнала

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

Длина волны = 300000 / Частота

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

Приведем пример расчета. Пусть радиолюбитель общается со своим другом на частоте 50 МГц (50 миллионов периодов в секунду). Подставив эти цифры в приведенную выше формулу, получим:

6000 миллиметров = 300000 / 50 МГц

Однако чаще пользуются системными единицами длины - метрами, поэтому для завершения расчета нам остается перевести длину волны в более понятную величину. Так как в 1 метре 1000 миллиметров, то в результате получим 6 м. Оказывается, радиолюбитель настроил свою радиостанцию на длину волны 6 метров. Прикольно!

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

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

Как посчитать проценты в Excel – основная формула расчета

Прежде, чем приступить к вычислению процентов, необходимо определиться с терминологией. Термин «процент» означает количество долей из всех 100 долей целого. Математическое определение процента – дробь, числитель которой определяет искомое количество частей, а знаменатель – общее. Результат умножается на 100 (т.к. целое – 100%). Работая с электронной таблицей, формула для определения процента выглядит следующим образом:

Часть/целое = Процент

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

Пример 1

Перед вами ряд данных, внесенных, например, в колонку D (D2, D3, D4, D5, …). Необходимо рассчитать, 5% от каждого значения.

• Активируете соседнюю с первым значением (или любую другую) ячейку – в ней будет располагаться результат вычислений.
• В ячейке E2 записываете выражение «=D2/100*5» или «=D2*5%».
• Жмете Enter.
• «Протяните» ячейку E2 на необходимое число строк. Благодаря маркеру автозаполнения по указанной выше формуле будет произведен расчет и для остальных значений.

Пример 2

Перед вами находятся 2 колонки значений – например, реализованные пирожные (D2, D3, D4, D5, …) и общее количество выпечки (E2, E3, E4, E5, …) каждого вида. Необходимо определить, какая часть продукции реализована.

• В ячейке, где будет рассчитан результат (например, F) записываете выражение «=D2/E2».
• Жмете Enter и «протягиваете» ячейку на необходимое число строк. Использование маркера автозаполнения позволит применить данную формулу для всех последующих ячеек и произвести верные расчеты.
• Для перевода результата в формат процентов выделите необходимые ячейки и воспользуйтесь командой Percent Style. Для активации последней можно кликнуть правой клавишей мыши и выбрать в появившемся перечне пункт «Формат ячеек» – «Процентный». При этом вы указываете желаемое число десятичных знаков. Или же перейдите в раздел «Главная» – «Число» и выберите вид «Процентный».

Как посчитать проценты в Excel – процент от суммы

Для вычисления доли каждой части относительно общей суммы используйте выражение «=A2/$A$10», где A2 – интересующее значение, общая сумма указана в ячейке A10. Как быть, если интересующая вас позиция встречается в таблице несколько раз? В таком случае воспользуйтесь функцией SUMIF (СУММЕСЛИ) с параметрами:

SUMIF(range,criteria,sum_range)/total

СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)/общая сумма

• Перемещаетесь в ячейку, где будет получен результат.
• Записываете выражение «=СУММЕСЛИ(C2:C10;F1;D2:D10)/$D$14» (или =SUMIF (C2:C10;F1;D2:D10)/$D$14), где

C2:C10, D2:D10 – диапазоны значений, в пределах которых происходят вычисления,

F1 – ячейка, в которой указана исследуемая характеристика,

D14 – ячейка, в которой рассчитана сумма.

Как посчитать проценты в Excel – изменение в процентах

Необходимость в таких вычислениях часто возникает в ходе оценки прироста или убыли по результатам деятельности. Итак, объемы продаж по категориям продукции за 2015г. внесены в колонку D, аналогичные данные за 2016г. – в колонку E. Необходимо определить на сколько процентов увеличился или уменьшился объем продаж.

• В ячейке F2 указываете формулу «=(E2-D2)/D2».
• Переводите данные ячейки в формат Процентный.
• Для вычисления прироста или убыли для остальных категорий (ячеек), протяните F2 на необходимое количество строк.
• Оцениваете результат. Если значение положительное – вы имеете прирост, если отрицательное – убыль.

Speaker Deck SlideShare

Основы использования формул в Excel. Правила ввода формул, разрешенные математические операции, порядок вычисления. Функция и формула, часто используемые функции группы Автосумма.

Навыки экзамена Microsoft Office Specialist (77-420):

Теоретическая часть:

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

Видеоверсия

Текстовая версия

Именно возможность производить различного рода вычисления и снискали мировую популярность табличному процессору Excel из пакета Microsoft Office.

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

Основы построения формул

Если в начале ячейки поставить знак «=», то Excel начинает воспринимать ячейку как формулу.

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

По умолчанию, в ячейке, содержащей формулу, для пользователя отображается результат ее вычисления, если нужно посмотреть сами формулы, сделать этом можно командой отображения формул «Показать формулы», группа «Зависимости формул», вкладка «Формулы», либо нажать горячее сочетание Ctrl+` (клавиша тильда стоит в начале цифрового ряда на клавиатуре).

Горячее сочетание

Ctrl+` переключает режимы отображения данные/ формулы в Excel

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

Константа – это значение, которое вводится непосредственно в формулу (число, текст, дата).

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

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

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

Порядок вычисления формулы

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

1. Отрицательные значения (-);
2. Проценты (%);
3. Возведение в степень (^);
4. Умножение (*) и деление (/);
5. Сумма (+) и разность (-).

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

Несколько примеров работы вычислений в Excel:

Горячее сочетание

Если во время ввода формулы вы передумали, то просто нажмите «Esc» и значение в ячейке не будет изменено. Если формулу уже изменили (завершили ввод с помощью Enter), то вернуть старое значение можно с помощью команды «Отменить» на панели быстрого доступа или сочетания Ctrl+Z.

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

1. Использование ссылок в формулах

Видеоверсия

Текстовая версия

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

Каждая ячейка находится на пересечении строк и столбцов и имеет соответствующее обозначение:

• «E2» – столбец «E», вторая строка;
• «F2» – столбец «F», вторая строка;
• «G2» – столбец «F», вторая строка.

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

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

Простая формула пересчета актуального заработка в рубли вводится в ячейку C2, а потом, с помощью маркера автозаполнения растягивается на остальные дни, как можно заметить, для ячейки C3 в формуле используется заработок за 02.03.2016, хотя формула вводилась «=B2*71», т.е. при сдвиге на одну ячейку вниз, введенная относительная ссылка, также меняет свой адрес. То же самое происходит, если растянуть маркер автозаполнения в любую из сторон (влево, вправо, вверх или вниз), относительная ссылка всегда будет находится на одном расстоянии от ячейки с формулой, где она используется. В данном случае, это на одну ячейку влево, т.е., если потянуть маркер автозаполнения вправо, то в формуле будет использоваться столбец «C» (и число в зависимости от текущей строки).

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

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

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

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

Знак доллара можно ввести с клавиатуры, но быстрее будет нажать клавишу «F4» на относительной ссылке, т.е. на ссылке на которой установлен курсор. Повторное нажатие на клавишу «F4» будет убирать/добавлять знак $ возле строки/столбца. Будут появляться промежуточные значения, ссылки с одним знаком доллара – это смешанные ссылки, их рассмотрение будет чуть позже.

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

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

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

В таком случае следует использовать смешанные ссылки. Смешанная ссылка – это ссылка, которая по одному из направлений (строке или столбце) абсолютная, а по другому – относительная. Какая часть ссылки ведет себя как абсолютная ссылка определяется знаком доллара.

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

Здесь используется два вида смешанных ссылок: первый ссылается на сумму заработка в долларах и зафиксирован по столбцам позволяет, растягивая формулу вниз, использовать для каждого дня свой заработок, а второй ссылается на курс зафиксирован по строкам позволяет изменять курс при автозаполнении формулы влево или вправо. Здесь важно понимать, что для пересчета заработка в три валюты формула была введена всего ОДИН! раз в ячейку C4. Все остальные ячейки были заполнены с помощью автозаполнения.

Какие еще бывают типы ссылок

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

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

1. Использование диапазонов данных в формулах

Видеоверсия

Текстовая версия

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

Именование диапазонов данных

Если вы часто работаете с определенным диапазоном, дать ему имя бывает достаточно удобно, чтобы в последствии оперировать не безымянным диапазоном/ячейкой: «C3:C15», а понятным «прибыль_2015» и т.п.

Для именования и навигации по именованным диапазонам и ячейкам используется группа «Определенные имена» вкладки «Формулы», а также окно «Имя» (Name box), которое мы начали рассматривать во втором вопросе второго занятия.

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

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

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

Выделение столбца «В евро» и использование команды «Создать из выделенного» равнозначно выделению диапазона E4:E13 и присвоение ему имени «В_евро».

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

Как использовать именованные диапазоны

Именованные диапазоны используются в формулах наравне со стандартными диапазонами, но есть некоторые особенности. Во-первых, именованный диапазон ВСЕГДА является абсолютной ссылкой, во-вторых, в формуле достаточно начать писать имя именованного диапазона и Excel его предложит, если есть несколько похожих («заработок_доллары», «заработок_гривны» и т.д.) то можно мышкой будет выбрать нужный.

Именованный диапазон в формулу можно вставить с помощью команды «Использовать в формуле» группы «Определенные имена».

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

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

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

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

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

1. Длина имени не более 255 символов.
2. Начинаться имя диапазона может либо с буквы, либо с символов «_» и «\». В самом имени можно использовать цифры, буквы, знак подчеркивания. Например, «\имя_ячейки», или «_имя\ячейки.1» – допустимые названия, а «1имя_ячейки» – нельзя т.к. начинается с цифры.
3. Имя не может состоять из одиночных букв: «R», «r», «C»,»c», поскольку их ввод в окошко «Имя» приводит к выделению строки (row) или столбца (column).
4. В именах нельзя использовать пробелы, Microsoft рекомендует их заменять символом подчеркивания «_» или точкой «.». Например, «доходы_октябрь», «прибыль.год».
5. Имя диапазона не может быть таким же как имя ссылки, например, «A5» или «$B$5».

1. Введение в функции. Отображение дат и времени с помощью функций

Видеоверсия

Текстовая версия

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

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

Удобный справочник по работе с функциями представлен на нашем сайте:

Введение в функций

На заметку

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

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

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

Для суммы есть горячее сочетание клавиш «Alt+=», можно просто ввести его возле диапазона с данными и Excel выделит наиболее подходящий (по мнению программы) диапазон, который можно откорректировать, а можно выделить диапазон и нажать горячее сочетание и Excel в зависимости от диапазона (горизонтальный или вертикальный) поместит суммы в ячейку под, или над диапазоном.

Функции даты и времени

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

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

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

Например, введя «15.03.2016» числовой формат будет установлен на «Дата», если потом просто удалить дату с помощью клавиши Del (равнозначно очистке содержимого) и ввести число, например, 5, то в ячейке отобразится дата: 05.01.1900. Поэтому нужно либо полностью очищать ячейку (вместе с форматами), либо после ввода данных изменить числовой формат ячейки.

Функция СЕГОДНЯ (TODAY)

Функция СЕГОДНЯ () или TODAY () в английской версии Excel проста в понимании и ее синтаксист предельно прост, т.к. аргументы отсутствуют в принципе. Данная функция возвращает текущую дату. При открытии книги всегда будет отображена актуальная дата, поэтому если нужно зафиксировать конкретную дату ее придется ввести вручную.

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

Само по себе использование функции СЕГОДНЯ выглядит не самым интересным занятием, но нужно понимать, что при построении формул мы может использовать несколько функций. Для примера можно использовать СЕГОДНЯ, при вычислении своего возраста, в этом случае формула примет вид:

Функция ТДАТА (NOW)

Функция ТДАТА () или NOW () в английской версии Excel, очень похожа на предыдущую за тем лишь исключением, что возвращает не просто текущую дату, но и точное время. Время в Excel представлено дробным числом.

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

1. Работа с часто используемыми функциями (Автосумма)

Видеоверсия

Текстовая версия

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

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

В «Автосумму» входят функции: , и .

Группу функций «Автосумма» можно найти на вкладке «Главная» в группе «Редактирование».

Группа функций «Автосумма» представлена на вкладке «Формулы» в группе «Библиотека функций».

Функция СУММ (SUM)

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM.

Создание формулы, ссылающейся на значения в других ячейках

Просмотр формулы

Ввод формулы, содержащей встроенную функцию

Скачивание книги "Учебник по формулам"

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

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

Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.

Части формулы Excel

Формула также может содержать один или несколько таких элементов, как функции , ссылки , операторы и константы .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте