При помощи Microsoft Excel удобно выполнять различные вычисления, оперируя данными, расположенными на рабочем листе. По формулам Excel производит вычисления: ∙ некоторые исходные данные заносятся в ячейки, Excel производит расчет по формуле, заданной в другой (или других) ячейке и вносит результат в ячейку с формулой. ∙ Microsoft Excel сам производит требуемые подсчеты с помощью заданной пользователем формулы и результат помещает в ячейку, содержащую эту формулу.
Формула в Excel всегда должна начинаться со знака » p7 ft2″>Формула представляет собой последовательность операндов (чисел или ссылок на ячейки) объединенных операторами − знаками арифметических операций или функций. При вычислениях с помощью формул соблюдается принятый в математике порядок выполнения арифметических операций.
В формулах используются следующие символы: ∙ Знаки арифметических действий: + — * / ∙ Числа любого формата, например =23; =0,326; =-1,256; =25*1,26 ∙ Адреса ячеек, содержимое которых участвует в вычислениях, например, =D3; =A2*$C$12. Можно набирать адреса ячеек с клавиатуры на английском языке, а можно просто щелкать мышью по ячейкам с нужными адресами – адрес появится в формуле. ∙ Стандартные функции, которые можно вставить через Мастер функций . Например, =МАКС(A1:G6) – вычисление максимального значения из диапазона ячеек. =СУММ(D1:D8) – вычисление сумм значений из диапазона ячеек.
В формулах можно сочетать числа, адреса ячеек, функции, например, =D3+1,6*СУММ(D15:D38); =23*7,5-МАКС(A1:G16). В MS Excel 2010 – вкладка Формулы кнопка Вставить функцию . Будет активизирована панель формул, введется знак равенства и появиться диалоговое окно Мастер функций — шаг 1 из 2 . Для ввода формулы нужно: 1. Выделить ячейку (щелкнуть по ней), в которую будем вводить формулу. 2. Ввести знак «=», затем набрать нужную формулу. 3. Нажмите клавишу Enter.
Копирование и перемещение формул
Формулы можно копировать в другие ячейки за маркер заполнения . При копировании формул в зависимости от типа ссылок, входящих в копируемую формулу, осуществляется их настройка. В формулах Excel используется три типа ссылок: относительная, абсолютная и смешанная адресация ячеек и диапазонов ∙ Относительная адресация (А3).
При ее использовании в формулах Excel запоминает расположение относительно текущей ячейки. Так, например, когда Вы вводите формулу «=В1+В2» в ячейку В4, то Excel интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше». Если Вы скопируете формулу из ячейки В4 в С4, Excel также интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше». Таким образом, формула в ячейке С4 примет вид «=С1+С2».
∙ Абсолютная адресация ($B$1). Если используется абсолютная адресация, то при копировании формулы ссылка на конкретную ячейку не меняется . Для ее задания необходимо перед именем столбца и номером строки ввести символ $. Можно изменить относительный адрес на абсолютный, используя клавишу F4. ∙ Смешанная адресация (В$5). Символ ставиться только там, где необходим. Такая ссылка при копировании корректируется частично. Корректируется только та часть ссылки перед которой нет $. Таким образом, при копировании и перемещении формул, содержащих адреса ячеек, относительные адреса перемещаются вместе с формулой ( изменяются ), а абсолютные адреса остаются неизменными .
| 1 | A | B | C | D |
| 12 | 2 | =A1+B1 | =В1+С1 | |
| 2 | 166 | 4 | =А2+В2 | |
| 3 | ||||
| 4 |
Например, в ячейке С1 содержится формула =А1+В1. При копировании формулы на одну ячейку вниз (в ячейку С2), она изменится так =А2+В2. При копировании формулы на одну ячейку вправо (в ячейку D1), она изменится так =В1+С1.
| 1 | A | B | C | D |
| 12 | 2 | =$A$1+$B$1 | =$A$1+$B$1 | |
| 2 | 166 | 4 | =$A$1+$B$1 | |
| 3 | ||||
| 4 |
А если в ячейке С1 содержится формула =$A$1+$B$1, то куда бы вы ее ни копировали, она останется неизменной.
Автоматическое суммирование строк и столбцов
При работе с Microsoft Excel часто приходится суммировать данные в строках или столбцах. Для этого можно воспользоваться кнопкой Автосумма стандартной панели инструментов: 1 способ — Выделить ячейку, в которую будет помещена формула. — Щелкните на кнопке Автосумма . После этого Excel автоматически выделит диапазон для суммирования. — Если диапазон выделен правильно, то нажмите клавишу Enter, иначе выделите нужный вам диапазон и уже после этого нажмите клавишу Enter. 2 способ — Выделите диапазон, данные в котором надо просуммировать. — Нажмите кнопку Автосумма . — В MS Excel 2010 – вкладка Формулы кнопка Автосумма . Задание 1 Рассчитать премию, аванс и итоговые выплаты служащих. 1. Создайте таблицу по образцу:
2. Заполните столбец ФИО пятью произвольными фамилиями, столбец Оклад – произвольными числовыми значениями в денежном (руб.) формате с двумя знаками после запятой. 3. Вычислить с помощью формул колонку Премия как 40% от оклада , Аванс как 20% от оклада. 4. Заполнить колонку К выдаче с помощью формул.
5. Добавить столбец В долларах . В нем перевести данные из столбца К выдаче в доллары (в ячейке, например, В10 введите текущий курс доллара). Подсказка : используйте абсолютную адресацию. 6. С помощью Автосуммы подсчитайте сумму по столбцу К выдаче и В долларах. 7. Сохранить файл в своей подпапке под именем Ведомость.
Использование функций
Функции призваны расширить возможности основных арифметических и логических формул. Каждая функция имеет следующую базовую структуру: ФУНКЦИЯ(аргумент1; аргумент2; …) После имени функции следует список аргументов (это могут быть ссылки на ячейки таблицы, в которых находятся числовые данные) разделенных точкой с запятой. Функции вводят в таблицу в составе формул или отдельно. Например, формула =СУММ(С22:С26) использует функцию СУММ для того, чтобы сложить значения в диапазоне ячеек С22:С26. Эта формула аналогична формуле =(С22+С23+С24+С25+С26) с перечислением адресов всех ячеек. Названия функций можно набирать с клавиатуры или вводить автоматически с помощью Мастера Функций .
Ввод функций
Функции вводятся, как и все другие данные, но следует придерживаться следующих правил: Имя функции вводится либо прописными, либо строчными буквами. Excel всегда преобразует имена функций в прописные буквы, если они набраны без ошибки. Аргументы должны заключаться в скобки. Аргументы отделяются друг от друга точкой с запятой.
Применение мастера функций
Если синтаксис функции или ее аргумента неизвестен, то можно воспользоваться Мастером Функций . Он позволяет выбирать требуемую функцию из списка и предлагает ввести соответствующие аргументы. Для автоматического ввода функции с помощью Мастера функций необходимо:
1. Выбрать Вставка / Функция или щелкнуть на кнопке Вставка функции стандартной панели инструментов. Будет активизирована панель формул, введется знак равенства и появиться диалоговое окно Мастер функций — шаг 1 из 2 . 2. В списке Категория выделить тип требуемой функции.
Если нет уверенности, выбрать Полный алфавитный перечень . 3. Выделить нужную функцию в списке Функция . 4. Щелкнуть ОК . Появиться диалоговое окно, которое выводит доступные для функции аргументы. 5. Для каждого обязательного аргумента и выбранного необязательного ввести значение или ссылку на ячейку в соответствующее текстовое поле. (Можно выделить необходимые ячейки с помощью мыши).
Чтобы диалоговое окно не мешало выделять нужные ячейки перетащите его к нижней границе экрана. Для этого поместите указатель мыши в любую часть диалогового окна (не на строки ввода и кнопки), нажмите левую кнопку мыши и, не отпуская ее, перетащите окно. После выделения ячеек мышью в текстовом поле (например Число 1) появиться адрес ячейки или диапазона.
6. По окончании ввода значений щелкнуть ОК. Функция с аргументами будет вставлена в ячейку. В MS Excel 2010 – вкладка Формулы, раздел Библиотека функций. Задание 2 1. Откройте файл Ведомость. 2. С помощью Мастера функций вычислите максимальное значение по окладу, минимальное значение по премии и среднее по авансу.
Задание 3 Вычислите средний возраст Вашей группы. Откройте Лист2 в файле Ведомость и создайте таблицу: 2. Заполните столбец Возраст. В первую ячейку этого столбца внесите формулу: ГОД(СЕГОДНЯ())-ГОД(С3). 3. ГОД(СЕГОДНЯ()) – дает год сегодняшней дня, ГОД(С3) – дает год рождения. Таким образом, мы найдем разность текущего года и года рождения, а это и есть возраст.
4. Скопируйте формулу в остальные ячейки. 5. В столбце Возраст в последней ячейке с помощью Мастера функций вставьте функцию СРЗНАЧ. 6. Сохраните файл. Задание 4 Заполните таблицу умножения чисел, используя копирование единственной формулы с соответствующими ссылками.
Источник: studfile.net
Формулы и функции в Excel
Все формулы в Excel начинаются со знака равенства «=». Они оперируют с числами, текстом, названиями ячеек, функциями. Главные отличия от обычных математических формул – формула в Excel задается одной строкой, вместо переменных используются названия ячеек. Для вычислений применяются следующие операции:
Для указания очередности действий используются скобки. Вот образцы корректных формул:
Пример простейших вычислений:
Применение функций
Для автоматизации расчетов применяются разнообразные функции. Их можно вставлять нажатием кнопки «Вставить функцию». Альтернативный вариант – нажатие комбинации Shift+F3 (для ноутбуков Shift+Fn+F3). Появляется диалоговое окно, в котором надо выбрать категорию. Далее определяется конкретная функция, задаются ее аргументы, нажимается «ОК».
Вот пример пошагового вычисления квадратного корня числа. Вызвали диалоговое окно, выбрали раздел «Математические», далее «КОРЕНЬ»:
Задали аргумент (в данном случае это B1):
Нажали «ОК»:
Математические вычисления
Одна из самых востребованных математических функций – СУММ, она предназначена для суммирования значений ячеек. СУММ может работать с целым диапазоном:
Здесь сложили B1, B2 и B3.
Также применяется суммирование отдельных ячеек:
А здесь сложили B1 и B3.
Аналог СУММ – ПРОИЗВЕД, позволяет перемножать значения. Пример перемножения диапазона:
В категории «Математические» также представлены средства для тригонометрических вычислений, для работы с целыми числами, для округления дробей.
Логические функции
В разделе «Логические» есть средства для работы с логическими значениями. Самый простой вариант – присвоить ячейке значение ИСТИНА или ЛОЖЬ:
Как и где используют функцию «Подбор параметра» в Excel
Можно инвертировать содержимое:
ЕСЛИ позволяет выстраивать сложные конструкции. Применяется в таком формате:
ЕСЛИ(логическое_выражение;значение1;значение2)
В качестве логического выражения можно использовать одну из операций сравнения:
Примеры операций сравнения: 2B4, F5>=10.
Пример использования ЕСЛИ:
Обработка текста
В Excel имеются средства и для несложных операций с текстовыми величинами. ДЛСТР возвращает длину текстового аргумента, например, =ДЛСТР(«Волга впадает в Каспийское море») даст результат 31.
НАЙТИ осуществляет поиск одного текста в другом и возвращает номер позиции первого вхождения. Если ввести =НАЙТИ(«ас»;»Василий»;1), то получим 2.
ПОДСТАВИТЬ заменяет в тексте один фрагмент другим. =ПОДСТАВИТЬ(«Все нормально!»;»е»;»ё») даст результат «Всё нормально!». Если в качестве третьего аргумента указать пустую строку, то фрагмент будет просто удален из всего текста.
Чтобы объединить несколько строк в одну, можно использовать СЦЕПИТЬ. =СЦЕПИТЬ(«Добрый «;»день») создаст небольшую фразу из двух слов.
Дата и время
В Excel много удобных средств для обработки времени и дат. В приведенном ниже примере в A1 поместили текущую дату с помощью формулы =СЕГОДНЯ(), потом разбили ее на составные части. Для этого применили конструкции =ДЕНЬ(A1), =МЕСЯЦ(A1), =ГОД(A1). Результат:
Чтобы получить текущее время, наберите =ТДАТА(), затем измените формат ячейки правой кнопкой мыши («Формат ячеек…» -> «Число» -> «Время»), выберите удобное представление. Из текущего времени также можно выделить составные части (используя СЕКУНДЫ, МИНУТЫ, ЧАСЫ).
Источник: composs.ru
Математические функции в Microsoft Excel
В программе Excel разработчиками заложено огромное количество различных функций, но, пользователи чаще всего пользуются математическими. Давайте рассмотрим их и подробнее остановимся на самых популярных.
Содержание скрыть
Использование математических функций в программе
В категорию математических функций входит более 60 различных операторов, которые позволяют выполнять различные вычисления.
Вставить функцию в свободную ячейку таблицы можно по-разному:
- Жмем кнопку “Вставить функцию” (fx) слева от строки формул. Выполнить данное действие можно, находясь в любой вкладке.
- Переключаемся во вкладку “Формулы”. Здесь также представлена кнопка “Вставить функцию” – в левом углу ленты инструментов.
- Нажимаем комбинацию клавиш Shift+F3, чтобы вызвать Мастер функций.
Результатом любого из вышеописанных способов будет открытие окна вставки функции. Здесь мы выбираем категорию “Математические”.
Теперь, когда категория выбрана, в поле ниже отмечаем требуемую функцию и щелкаем OK.
После этого откроется окно с аргументами для заполнения.
Примечание: Если мы, находясь во вкладке “Формулы”, в группе инструментов “Библиотека функций” нажмем по значку математических функций, сразу откроется список операторов, которые мы можем выбрать, минуя окно вставки функции.
Стоит учитывать, что в предлагаемом перечне присутствуют не все операторы, но самые необходимые здесь все же есть, и в большинстве случаев их достаточно.
Теперь перейдем к детальному рассмотрению самых популярных функций.
СУММ
- Как посчитать сумму чисел в строке в Эксель
- Как посчитать в Экселе сумму столбца
Пожалуй, это самая популярная функция, которая используется в Эксель. С помощью нее выполняется суммирование числовых данных. Формула функции:
В аргументах можно указать как конкретные числа, так и ссылки на ячейки, содержащие числовые значения. Причем указать координаты можно вручную (с помощью клавиш клавиатуры) или методом клика/выделения непосредственно в самой таблице.
Для перехода к заполнению следующего аргумента достаточно кликнуть по полю напротив него или нажать клавишу Tab.
СУММЕСЛИ
Данная функция позволяет считать сумму чисел с заданным условиями, с помощью которых будет выполняться отбор значений, учитывающихся в суммировании. Формула выглядит следующим образом:
В аргументах функции указывается диапазон ячеек (вручную или путем выделения в таблице), значения которых нужно просуммировать. В качестве критерия можно задать следующие условия (в кавычках):
- больше (“>”)
- меньше (“ <“)
- не равно (“<>”)
Аргумент “Диапазон_сумирования” заполнять не обязательно.
ПРОИЗВЕД
Смотрите также: “Как умножать в Экселе: числа, ячейки, столбцы, формула, функция”
С помощью данного оператора выполняется умножение чисел. Синтаксис выглядит следующим образом:
В аргументах функции, как и в СУММ, можно указывать как конкретные числа, так и адреса ячеек (диапазоны ячеек), которые содержат числовые значения.
ЧАСТНОЕ
Смотрите также: “Деление в Экселе: формула, как сделать”
Чаще всего для деления используется формула со знаком “/” между делимым и делителем: =Число1/Число2 .
Однако в программе также есть отдельная функция для выполнения деления, синтаксис которой представлен ниже:
Заполнить нужно два аргумента: Числитель (Делимое) и Знаменатель (Делитель).
СТЕПЕНЬ
- Возведение числа в квадрат в Эксель
- Как возвести число в степень в Эксель
Оператор позволяет возвести число в указанную степень. Формула выглядит так:
В аргументах функции указывается само число, а также, степень, в которую нужно его возвести.
КОРЕНЬ
Смотрите также: “Как извлечь корень в Эксель: квадратный, кубический, в степени”
С помощью данного оператора можно извлечь квадратный корень из числа. Синтаксис выглядит следующим образом:
Заполнить требуется только один аргумент – “Число”.
ОКРУГЛ
Смотрите также: “Как округлить число в Excel”
Функция применяется для выполнения еще одного распространенного математического действия – округления чисел (по общематематическим правилам, т.е., к ближайшему по модулю значению). Синтаксис функции представлен ниже:
В аргументе “Число” указывается значение, которое требуется округлить. В числе разрядов, соответственно, пишем количество цифр, которые хотим оставить после запятой.
Также, в Excel доступны операторы ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ, которые, как следует из их названий, используются для округления до ближайшего верхнего и нижнего числа, соответственно (по модулю).
ABS
Смотрите также: “Как посчитать модуль числа в Эксель: формула, функция”
Позволяет получить модуль числа. Формула функции представлена ниже:
Заполнить нужно всего один аргумент – “Число”, модуль которого требуется найти.
LOG
Смотрите также: “Функция логарифма в Excel: формула расчета”
С помощью этого оператора определяется логарифм числа по заданному основанию. Синтаксис функции представлен в виде:
Необходимо заполнить два аргумента: Число и Основание логарифма (если его не указать, программа примет значение по умолчанию, равное 10).
Также для десятичного логарифма предусмотрена отдельная функция – LOG10.
ОСТАТОК
Применяется для получения остатка от деления чисел. Формула оператора выглядит следующим образом:
Для того, чтобы получить результат, требуется заполнить значения двух аргументов: Число и Делитель.
Заключение
Таким образом, мы разобрали самые популярные математические функции, которые используются в Excel. Однако возможности программы гораздо шире, и в ее инструментарии можно найти функцию для успешного выполнения практически любой задачи.
Источник: microexcel.ru