Создание простых формул в Microsoft Excel. Формулы и функции в Excel

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

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

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

Для обработки значений и их диапазонов - от банальной суммы до методов математической статистики вроде медианы или моды - используются формулы. Это очень гибкий и при этом достаточно простой инструмент, который широко используется наряду с условным форматированием при создании таблиц с данными. В самом простом случае для записи формулы в ячейку используется обычный текстовый ввод. Чтобы провести, например, сложение значений из нескольких полей, можно прямо в ячейке написать «=СУММ()» и в скобках указать адреса значений через точку с запятой. Или, если они расположены рядом друг с другом, указать начальную и конечную через двоеточие. А чтобы закрепить в этой формуле ячейку, например, чтобы не менялись столбец или строка при копировании формулы в другую, поставьте перед нужным символом знак «$». Тогда Эксель не будет менять этот экранированный символ, при этом меняя другие.

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

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

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

Когда ввод в конструкторе будет завершён, нажмите ОК. На этом этапе Excel проверяет введённые значения на соответствие типов и на другие ошибки, поэтому может выкинуть текст ошибки. По нему вы сможете понять, что именно нужно изменить, чтобы формула работала так, как вы от неё ожидаете. Если же ошибок не будет, окно закроется, а в ячейке появится значение. Иногда бывает так, что какой-то из указанных диапазонов ещё не заполнен, но присвоенный ячейке тип содержимого соответствует нужному. Тогда на этапе проверки ошибок не будет, но и значение будет подсчитано без него, так что будьте внимательны.

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

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

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

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

Простые вычисления

Выделите любую ячейку, например А2;
В строчке со знаком Fx (расположена она вверху над таблицей) введите «=1+3» и нажмите «Enter»;
В выделенной ячейке вы увидите результат (4). В строчке формул останется введенное значение.

Также в excel можно использовать и другие арифметические символы: «-» – для вычитания, «*» – для умножения, «/» – для деления. Попробуйте вводить разные формулы в строчке Fx.

Основные правила ввода:

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

Формулы с ячейками

В excel вы можете применять формулы с указанием ячеек. Допустим, в ячейке В2 введите число 7, в ячейке В3 – число 20. В ячейке В4 укажите формулу «=В2+В3». В ячейке В4 вы увидите итог сложения в ячейках В2 и В3 – 27. Можно поменять значения в них, тогда автоматически изменится и результат в ячейке В4.

Применение мыши

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

Кликните по ячейке В4 левой клавишей мышки и вставьте знак «=»;
Выделите ячейку В2 и вставьте знак «+» на клавиатуре;
Кликните по ячейке В3 левой клавишей мыши два раза и нажмите «Enter».

Типы ссылок

Всего бывает 3 их разновидности:

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

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

Относительные ссылки

Укажите в полях таблицы:

В2 - 10 С2 – 100
В3 - 70 С3 – 700

В ячейке В4 укажите «=В2+В3», потом «Enter».

Далее установите курсор в правом нижнем углу В4, удержите правую клавишу мыши, протяните на ячейку С4, и отпустите клавишу манипулятора. В открывшемся контекстном меню выберите «Копировать ячейки». Значение из поля В4 скопируется в ячейку С4. Активируйте ячейку С4, вы увидите в ней формулу – «С2+С3». То есть, вроде вы скопировали формулу из поля В4, но значение получилось другое. Этим и отличается данный тип ссылок. Например, если вы возьмете ячейку В4 и скопируете в ячейку А54, например, формула будет получаться сложением ячеек над ней, то есть «А52+А53».

Абсолютные ссылки

Укажите в ячейках:

В2 - 10 С2 – 100
В3 - 70 С3 – 700
Введите в поле D2 значение 10.

В поле В4 укажите формулу «=В2+В3+$D$2». Скопируйте формулу из В4 в С4. Что в итоге получилось? Обычные ссылки изменились, а вот абсолютные не поменялись.

Допустим, чтобы ввести в поле В2 (Лист 1) ссылку на поле В7 (Лист2), выполните следующие шаги:

Выделите В2 и вставьте знак«=»;
Кликните по ярлыку «Лист 2»;
Выделите В7 и нажмите клавишу ввода;
Снова откроется Лист 1, а в поле В2 появится формула «=Лист2!В7».

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

Текст в формулах

Текстовые значения использовать для формул, если в них есть следующие символы: «0 – 9», «+», «-», «е», «Е», «/». Также можно использовать следующие символы форматирования: «$», «%», «(», «)», «пробел», «";;».

Например, если в ячейке В1 значение "Иван", а в ячейке В2 – "Иванов", а в ячейке В4 будет формула «=В2&В3», то результат получится "ИванИванов".

Знак «&;» – это текстовый оператор, амперсанд, который объединяет текст.

Чтобы вставить пробел, используется «&";; "&», то есть формула будет такой – «=В2&" "&В3».

Амперсанд используется и для объединения чисел с текстом. Например, если в первой ячейке число5, а во второй ячейке – ведер, то в результате получится «5ведер».

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

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

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

Введение

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

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

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

Выполнение базовых арифметических операций

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

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

Например, давайте представим, что нам необходимо сложить два числа - «12» и «7». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7». По окончании ввода нажмите клавишу «Enter» и в ячейке отобразится результат вычисления - «19».

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

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

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

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

Составление формул

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

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

Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.

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

С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.

В ячейку B7, введите знак равно (=) и… вместо того, чтобы вручную вбивать значение клетки B2, щелкните по ней левой кнопкой мыши. После этого вокруг ячейки появится пунктирная выделительная рамка, которая показывает, что ее значение попало в формулу. Теперь введите знак «+» и щелкните по ячейке B3. Далее проделайте тоже самое с ячейками B4, B5 и B6, а затем нажмите клавишу ВВОД (Enter), после чего появится то же значение суммы, что и в первом случае.

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

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

Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование . Теперь, вернемся к таблице и посмотрим, что же произошло.

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

Теперь посмотрим на саму формулу. Как и раньше, вначале идет знак равенства, но на этот раз за ним следует функция «СУММ» - заранее определенная формула, которая выполнит сложение значений указанных ячеек. Сразу за функцией идут скобки расположенные вокруг адресов клеток, значения которых нужно просуммировать, называемые аргументом формулы . Обратите внимание, что в формуле не указаны все адреса суммируемых ячеек, а лишь первой и последней. Двоеточие между ними обозначает, что указан диапазон клеток от B2 до G2.

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

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

Автозаполнение

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

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

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

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

Конечно, Excel не всегда верно «понимает», как нужно заполнить последующие клетки, так как последовательности могут быть довольно разнообразными. Представим себе, что нам необходимо заполнить строку четными числовыми значениями: 2, 4, 6, 8 и так далее. Если мы введем число «2» и попробуем переместить маркер автозаполнения вправо, то окажется, что программа предлагает, как в следующую, так и в другие ячейки вставить опять значение «2».

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

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

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

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

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

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

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

Добавление строк, столбцов и объединение ячеек

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

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

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

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

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

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

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

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

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

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

Белые неформатированные ряды в таблице мы оставили специально, что бы отделить доходную, расходную и итоговую часть друг от друга, написав в них соответствующие заголовки. Но перед тем как это сделать, мы изучим еще одну операцию в Excel - объединение ячеек .

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

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

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

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

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

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

Заключение

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

Теперь переведем эти расчеты в формулы понятные Excel. Для января (ячейки B14) формула очень проста и будет выглядеть так: «=B5-B12». А вот для ячейки С14 (февраль) выражение можно записать двумя разными способами: «=(B5-B12)+(C5-C12)» или «=B14+C5-C12». В первом случае мы опять проводим расчет баланса предыдущего месяца и затем прибавляем к нему баланс текущего, а во втором в формулу включается уже рассчитанный результат по предыдущему месяцу. Конечно, использование второго варианта для построения формулы в нашем случае гораздо предпочтительнее. Ведь если следовать логике первого варианта, то в выражении для мартовского расчета будет фигурировать уже 6 адресов ячеек, в апреле - 8, в мае - 10 и так далее, а при использовании второго варианта их всегда будет три.

Для заполнения оставшихся ячеек с D14 по G14 применим возможность их автоматического заполнения, так же как мы это делали в случае с суммами.

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

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

А вот и наша итоговая таблица с выполненными расчётами:

Теперь, при желании, вы уже самостоятельно сможете продолжать ее наполнение, вставляя как дополнительные статьи расходов или доходов (строки), так и добавляя новые месяцы (столбцы).

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

Здравствуйте!

Многие кто не пользуются Excel - даже не представляют, какие возможности дает эта программа! Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, складывать по условию и т.д. - в общем-то, по сути мини-язык программирования для решения "узких" задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал)...

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

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

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel - задают один странный вопрос: "ну и где тут таблица?". Между тем, все клеточки, что вы видите после запуска программы - это и есть одна большая таблица!

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

слева : в ячейке (A1) написано простое число "6". Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число "6".
справа : в ячейке (C1) с виду тоже простое число "6", но если выбрать эту ячейку, то вы увидите формулу "=3+3" - это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например "=3+5+8" (без кавычек). Результат вам писать не нужно - Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму - можно написать формулу двумя способами:

первый: "=5+6" (не совсем удобно, представьте, что в ячейке A1 - у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
второй: "=A1+B1" - а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!)

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

в строке 2 написать формулу "=A2+B2" , в строке 3 - "=A3+B3" и т.д. (это долго и утомительно, этот вариант никогда не используют);
выбрать ячейку D1 (в которой уже есть формула), затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже). Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! (Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)).

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 - то увидите формулу "=A2+B2" (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

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

Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже - это G2.

Далее в ячейке E2 пишется формула "=D2*G2" и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу "D3*G3", в 4-ю строку: "D4*G4" и т.д. Надо же, чтобы G2 везде оставалась G2...

Чтобы это сделать - просто измените ячейку E2 - формула будет иметь вид "=D2*$G$2". Т.е. значок доллара $ - позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже)...

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая "=A1+B1+C1" и т.п. Но в Excel есть более быстрые и удобные инструменты.

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

сначала выделяем ячейки (см. скрин ниже);
далее открываем раздел "Формулы" ;
следующий шаг жмем кнопку "Автосумма" . Под выделенными вами ячейками появиться результат из сложения;
если выделить ячейку с результатом (в моем случае - это ячейка E8 ) - то вы увидите формулу "=СУММ(E2:E7)" .
таким образом, написав формулу "=СУММ(xx)" , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк...

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

Я в своей таблицы буду использовать всего 7 строк (для наглядности), реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал "Саша". Как будет выглядеть формула:

"=СУММЕСЛИМН(F2:F7 ;A2:A7 ;"Саша") " - (прим .: обратите внимание на кавычки для условия - они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру "СУММ..."), сам подсказывает и подставляет возможные варианты - а формул в Excel"e сотни!;
F2:F7 - это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
A2:A7 - это столбик, по которому будет проверяться наше условие;
"Саша" - это условие, те строки, в которых в столбце A будет "Саша" будут сложены (обратите внимание на показательный скриншот ниже).

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

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию. Ну, например, сколько раз имя "Саша" встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой? Формула:

"=СЧЁТЕСЛИ(A2:A7 ;A2 ) " - где:

A2:A7 - диапазон, в котором будут проверяться и считаться строки;
A2 - задается условие (обратите внимание, что можно было написать условие вида "Саша", а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Теперь представьте более расширенную задачу: нужно посчитать строки где встречается имя "Саша", и где в столбце И - будет стоять цифра "6". Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).

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

=СЧЁТЕСЛИМН(A2:A7 ;A2 ;B2:B7 ;"6") (прим.: обратите внимание на кавычки - они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ;A2 - первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;"6" - второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

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

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

Самый простой способ, в котором просто невозможно запутаться - это использовать правило "квадрата", или пропорции. Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 - ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал "Саша", нужно найти какую...).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число). В принципе, используя это правило, запутаться в процентах практически невозможно .

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

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

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

В этом уроке мы рассмотрим «Как сделать формулу в Excel».

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

Формула складывается из трех основных частей:

Знака все равенства (=);
Одна или несколько ссылок;
Знаки математических действий (складывание, умножение и т.д.).

Ссылка на ячейку – это уникальная запись, однозначно идентифицирующая одну ячейку, например A4 или D9. К основным математическим действиям, которые используются в формулах, относят складывание (+), вычитание (-), умножение (*) и деление (/). В таблице приведены все математические операторы, которыми можно пользоваться в формулах.

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

Эта формула содержит три части.

Знак =. Показывает то, что написанный текст является формулой. Если написать в ячейку A4+C7, то MC Excel будет считать эту запись как обычный тест.

Ссылки на две ячейки. В нашем случайном примере это A4 и C
Математический оператор складывания (+).

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

Щелкните на ячейке, в которой нужно сделать формулу.

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

MC Excel выделит нужную вам ячейку.

Введите знак равенства (=).

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

Если вам нужно просуммировать числа, написанные в ячейках A4 и E8, то напишите =A4+E8.

Нажмите «Enter ».

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

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

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

MC Excel выделит нужную вам ячейку.