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

Работа с формулами в Excel

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

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

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

• = («знак равно») – равенство, равно. Обычно отвечает за начало формулы;
• + («плюс») – сложение. Может складывать как числа, так и выражения;
• — («минус») – вычитание;
• * («звёздочка») – умножение;
• / («наклонная вправо») – делит;
• ^ («циркумфлекс») – возводит в степень.

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

Работа с простейшими формулами

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

1. В готовой таблице выделите ту ячейку, куда хотите получить результат вашей формулы.
2. Поставьте в этой ячейке знак «=». Он отвечает за начало формулы. Также вы можете его поставить не в самой ячейке, а в специальном поле, что расположено сверху. Здесь уже как вам будет самим удобно. Рассмотрим пример формулы a+b.
3. После знака равно кликните левой кнопкой мыши по ячейки, значение из которой вам нужно взять в качестве первого.
4. Автоматически установится наименование этой ячейки (латинская буква и номер) после знака равно. Здесь же вам требуется установить оператор, отвечающий за действие с этими ячейками – плюс, минус, равно, делить и т.д.
5. Нажмите теперь левой кнопкой мыши по той ячейки, которая будет выполнять функции b в нашей формуле.

Чтобы получить результат расчётов, нажмите на клавишу Enter.

По такой инструкции можно делать формулы с несколькими переменными, а не только вида a+b. Например, вы можете сделать по аналогии формулу вида a+b+c, a+b+c+d и т.д.

Примеры вычислений

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

1. Задайте формулу вида a*b, чтобы рассчитать полученную сумму от продажи товара. Вид у формулы в Excel будет таким: =(название ячейки с количеством товара, например, B3)*(название ячейки с ценой, например, C3).
2. Нажмите Enter, чтобы увидеть полученный результат в ячейки суммы у определённого товара. Учтите, что в формуле не должно быть пустых ячеек. В противном случае вместо результата вы получите просто сообщение об ошибке.

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

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

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

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

В ячейку напротив столбца «Сумма» укажите формулу вида: =([название ячейки с количеством первой партии товара, например, B3]+[название ячейки с количеством второй партии товара, например, C3])*[название ячейки с ценой, например, D3].

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

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

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

Использование в качестве калькулятора

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

1. На листе выберите ячейку, в которой собираетесь происходить расчёты.
2. Выделите эту ячейку и поставьте в ней знак равно.
3. Пропишите нужные действия. Сюда можно прописать и сложный пример по типу: 444*89+((78-660)*9)/15).

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

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

Источник: public-pc.com

Работа в Excel с формулами и таблицами для чайников

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

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

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

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

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

Оператор	Операция	Пример
+ (плюс)	Сложение	=В4+7
– (минус)	Вычитание	=А9-100
* (звездочка)	Умножение	=А3*2
/ (наклонная черта)	Деление	=А7/А8
^ (циркумфлекс)	Степень	=6^2
= (знак равенства)	Равно
Больше
=	Больше или равно
<>	Не равно

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

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

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

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

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

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

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

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

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

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

Источник: exceltable.com

10 формул Excel, которые пригодятся каждому

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

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

С помощью формулы вы можете:

• посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
• посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
• посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

СЧЁТЗ

Англоязычный вариант: =COUNTA(A1:A10)

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

ДЛСТР

Англоязычный вариант: =LEN(A1)

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

СЖПРОБЕЛЫ

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)

• B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
• A1:B13 = таблица, в которой находится искомое значение.
• 2 – номер столбца, где находится искомое значение.
• ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

Формула: =ЕСЛИ(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

Англоязычный вариант: =IF(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена».

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; “Норма выполнена”; “Норма не выполнена”)

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&” “&ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&” “&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между ” “, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;” “; B1) или в англоязычном варианте =concatenate(A1;” “; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

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

Источник: blog.teachmeplease.ru

7 полезных формул для тех, кто считает деньги в эксель-таблице

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

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

Соединить текст из разных ячеек

Иногда надо быстро собрать данные из разных ячеек в одной. Поочередно копировать долго и неудобно, поэтому лучше использовать формулу с амперсандом — знаком «&».

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

Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

Подобрать значения для нужного результата

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

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

Как следить за бюджетом

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

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

Обновить курс валют

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

Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

Планировать действия

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

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

Вот так: =ЕСЛИ (ячейка с ценой акции >= цена выгодной продажи; “продавать”; ЕСЛИ (ячейка с ценой акции Это самый простой пример. Формула «Если» может совмещать данные из разных ячеек и таблиц и быть частью сложных формул, когда при нужном условии происходит умножение или другое действие

Выделить цветом нужные данные

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

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

Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

Суммировать только нужное

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

Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

Расставить по порядку

В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

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

Источник: journal.tinkoff.ru

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

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

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

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

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

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

Содержание:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: geek-nose.com

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

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

1. Выделить одно из полей.
2. Ввести знак «=» в нее или в строку над листом.
3. Ввести значения.
4. Нажать Enter и исчислить результат.
5. Захватить поле и растянуть алгоритм на другие, где он тоже будет применен.

Для чего применяются формулы в Эксель

Excel — это одна из программ пакета Microsoft office, созданного для производства различных документов. Эксель позволяет создавать табличные документы, в нем удобно делать и самые простые вычисления, используя как калькулятор, и сложные расчеты, такие как, например, строительные сметы.

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

Как вставить формулу в ячейку

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

Ввести значения: числа и алгебраические знаки (в этом случае после нажатия клавиши «Ввод» в выбранном поле появится результат алгебраического действия) или значения из других полей, тогда необходимо выделить эти поля и поставить необходимый алгебраический знак между ними. Например, в выделенном поле нам необходимо получить сумму стоимостей единицы Товара 5 и Товара 8. В таком случае после постановки знака «=» необходимо выделить поле С6, потом поставить «+», а затем выделить С9.

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

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

Как создать таблицу с формулами

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

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

Чтобы рассчитать общую стоимость каждого наименования товаров, необходимо стоимость единицы каждого из них умножить на количество единиц этого товара. Вычислим общую стоимость Товара 1. Для этого выделяем поле D2, ставим знак равенства, выделяем В2, прописываем алгебраический символ умножения «*» и выделяем С2.

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

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

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

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

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

Как обозначить постоянную ячейку

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

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

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

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

Примеры формул в Экселе

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

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

Сформируем цельные даты в столбце D. Для этого выделим D2, а в меню формул выбираем «ДАТА».

В появившемся окне впишем наименования ячеек, в которых содержится информация о годе, месяце и дне, это А2, В2 и С2 соответственно.

После нажатия кнопки «ОК» в выделенном поле появится дата.

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

Текст

Функция текста дает возможность перевести ячейку в текстовый формат. То есть любое ее содержимое отныне является текстом и не сможет принимать участие в расчете. Если координату текстового поля включат в формулу, вычисления не произойдет, и система выдаст ошибку #ЗНАЧ!

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

Выделим верхнюю ячейку этого столбца — Е2 и выберем в меню формул «ТЕКСТ».

В появившемся окне заполним строки: значение — это координаты, откуда взято число, — D2, формат — текстовое обозначение — #руб.

После нажатия кнопки «ОК» в ячейке появится итоговая цифра с наименованием валюты.

Действие распространяем на ячейки ниже.

Автосумма

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

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

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

Другие примеры

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

Эксель — это наиболее полный и удобный сервис по работе с таблицами, но некоторые функции предоставляет и Ворд.

Инструкция, как вставить формулу в таблицу в Ворде, следующая:

1. Создать таблицу в файле.
2. Выделить одну из ячеек.
3. В разделе «Работа с таблицами» на вкладке «Макет» в группе «Данные» нажать кнопку «Формула».
4. В появившемся диалоговом окне создать алгоритм.

Источник: clubtk.ru