Математические и статистические функции
Занятие 9
Обработка информации из базы данных в MS Excel .
Формулы и функции
Построение простых формул
Порядок выполнения операторов
Ссылки на ячейки
Типы ссылок
Встроенные функции Excel
Вставка функций в формулы
Математические и статистические функции
Суммирование чисел: СУММ()
Расчет средних значений: СРЗНАЧ()
Подсчет числа элементов в списке: СЧЁТ( ), СЧЁТЗ( ) и СЧИТАТЬПУСТОТЫ( )
Определение крайних значений: MAKC() и МИН(), НАИБОЛЬШИЙ() и НАИМЕНЬШИЙ()
Использование условных функций
Проверка и анализ данных
Подсчет только указанных ячеек: СЧЁТЕСЛИ( )
Суммирование только указанных ячеек: СУММЕСЛИ( )
Подсчет и суммирование со множественными критериями: СЧЁТЕСЛИМН( ) и СУММЕСЛИМН( )
Ссылки на ячейки за пределами текущего рабочего листа
Ошибки в формулах
Построение простых формул
Кроме чисел и текста, в ячейках таблицы могут находиться формулы.
Если ввести формулу в ячейку, то в ней отобразится результат расчета по этой формуле. А сама формула появляется в строке формул при выборе этой ячейки.
Все формулы представляют собой некоторые сочетания следующих компонентов:
· Знак равенства (=). Любая формула должна начинаться со знака равенства. Это указывает Excel, что ячейка содержит формулу, а не просто обычный текст.
· Операторы: арифметические операторы (сложение (+), вычитание (-), умножение (*), деление (/) и возведение в степень (Ù)), конкатенация (объединение текстовых строк &), и логические операторы (равно (=), больше (>), меньше (<),больше или равно(>=), меньше или равно (<=), не равно (<>)).
|
|
|
· Числа.
· Ссылки на ячейки. Эти ссылки указывают на ячейку или на диапазон ячеек, из которых вам требуется получить данные для осуществления вычислений.
· Функции. Это специализированные формулы, встроенные в Excel, которые позволяют осуществлять широкий спектр вычислений, например, вычисляют суммы и средние значения, прибыль, косинусы и др.
Порядок выполнения операторов
При вычислениях по формулам Excel придерживается определенных правил, определяющих последовательность выполнения действий. Действия выполняются в следующем порядке:
1. Скобки (любые вычисления в скобках всегда выполняются в первую очередь).
2. Возведение в степень.
3 Деление и умножение.
4. Сложение и вычитание.
5. Конкатенация.
6. Логические операторы.
Когда Ехcel встречает формулы, которые содержат операторы с одинаковым приоритетом, он вычисляет эти операторы слева направо.
В формулах можно также использовать вложенные скобки, т.е. скобки, заключенные в другие скобки. Если в формуле есть вложенные скобки, то Excel вычисляет сначала выражения, которые находятся в самых "внутренних" скобках, а затем движется изнутри наружу.
|
|
|
Ссылки на ячейки
Чтобы выполнять вычисления на базе информации, уже имеющейся в таблице, необходимо использовать ссылки на ячейки. Каждая ссылка на ячейку указывает на другую ячейку, например, ссылка, которая указывает на ячейку А1, выглядит как =А1.
: Задание 1. Создание простейшей формулы
1. Загрузите Microsoft Excel.
2. Откройте файл электронных таблиц Анализ данных.ваша фамилия.
3. Перейдите на лист Договоры.
4. Подсчитайте сумму договора в долларах. Для этого:
- введите в ячейку Н1 текст Сумма договора ($)
- введите в ячейку А18 текст Курс $
- введите в ячейку В18 число (текущий курс доллара)
- введите в ячейку Н2 формулу =G2*B18 (при вводе формулы ячейки G2 и B18 выделяйте мышкой).
Типы ссылок
В формулах используется три типа ссылок на ячейки и диапазоны:
· Относительные ссылки. При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы.
· Абсолютные ссылки. Эти ссылки не изменяются при копировании формул.
· Смешанные ссылки. В этих ссылках номер строки (или столбца) является абсолютным, а столбца (строки) — относительным.
|
|
|
Различие между разными типами ссылок проявляется при копировании формул. Копировать формулы можно обычным способом, как любые другие ячейки, или использовать для копирования маркер автозаполнения.
Напоминание
Для копирования следует выделить ячейку с копируемыми данными и перетащить маркер автозаполнения по строке или столбцу:
: Задание 2. Копирование формулы
1. Скопируйте формулу из ячейки Н2 в ячейки, расположенные ниже (для остальных договоров). Для копирования используйте маркер автозаполнения. Что произошло с формулой при копировании? Верны ли расчеты?
Относительные ссылки автоматически настраиваются при изменении местоположения формулы. Пример относительной ссылки: А1.
Абсолютные ссылки используются в тех случаях, если необходимо, чтобы при копировании или перемещении формул ссылки в формулах оставались прежними. В абсолютной ссылке перед буквой столбца и номером строки ставится знак доллара ($). Например, $А$1.
Смешанные ссылки используются в тех случаях, если необходимо, чтобы в формуле были абсолютными или строка или столбец: А$1 или $А1.
Неотносительные (т.е. абсолютные или смешанные) ссылки можно ввести вручную, вставив в нужных местах знаки доллара. Можно также воспользоваться клавишей F4: при вводе ссылки в ячейку — либо вручную, либо путем указания — нужно нажать клавишу F4 несколько раз, чтобы программа «прокрутила» по циклу все четыре типа ссылок.
|
|
|
: Задание 3. Изменение типа ссылки
1. Измените в формуле в ячейке Н2 тип ссылки на ячейку В18 на абсолютную: =G2*$B$18 (для этого в строке редактирования формул установите курсор на ссылку и нажмите клавишу F4).
2. Заново скопируйте формулу ниже для остальных договоров. Проверьте правильность расчетов.
Встроенные функции Excel
Excel содержит огромное количество встроенных функций, которые вы можете использовать в своих формулах. К ним относятся как достаточно распространенные функции, например СУММ(), СРЗНАЧ() и КОРЕНЬ(), так и функции, специально предназначенные для определенных целей, например статистические или инженерные. Функции существенно повышают эффективность формул. Они могут упростить формулы и сделать их понятнее.
Во всех функциях используются круглые скобки. Данные внутри круглых скобок называются аргументами. Даже в том случае, если в функции нет аргументов, все равно после имени функции обязательно должны быть пустые скобки.
Если в функции используется несколько аргументов, то каждый из них отделяется точкой с запятой.
Одним из важных достоинств функций является то, что в них в качестве могут использовать ссылки на ячейки.
Часто необходимо сослаться не на отдельную ячейку, а на диапазон ячеек. Диапазон — это группа из нескольких ячеек:
· Точка с запятой (;) отделяет ячейки, если их более одной. Например, А1;В7;Н9 является диапазоном ячеек, который содержит три ячейки.
· Двоеточие (:) отделяет левый верхний и правый нижний углы блока ячеек. Например, А1:А5 — это диапазон, который включает ячейки А1, А2, A3, А4 и А5.
Excel позволяет указывать диапазоны, выделяя ячейки с помощью мыши, вместо того чтобы вводить их вручную.
Нельзя вводить диапазоны непосредственно в формулы, их необходимо указывать в функциях.
Вставка функций в формулы
Для вставки функции в формулу используется команда Формулы, Библиотека функций. Этот способ особенно полезен, если вы не помните названия функции или не знаете, какую функцию применить.
| ||||
 | ||||
Щелкните на нужной функции, чтобы открыть диалоговое окно Аргументы функции, в котором вы имеете возможность задать значения аргументов выбранной функции. Кроме того, в этом окне имеется ссылка Справка по этой функции, щелчок на которой переносит в справочную систему Excel с открытой статьей, посвященной данной функции.
Другой метод вставки функции в формулу заключается в использовании диалогового окна Мастер функций. Открыть это окно можно одним из следующих способов:
Откроется окно Мастера функций:
| ||||
 | ||||
Диалоговое окно Мастер функций помогает выбрать нужную функцию, даже если вы не знаете точно, какую функцию следует применить. Введите в поле Поиск функции краткое описание того, что должна делать функция, и щелкните на кнопкеНайти. После этого Excel выведет в полеВыберите функцию список подходящих функций, среди которых вы сможете выбрать нужную. При этом в нижней части окна появляется ее синтаксис и краткое описание.
Обнаружив нужную функцию, выделите ее и щелкните на кнопке ОК. Появится окно Аргументы функции, вид которого изменяется в зависимости от выбранной функции:
 |
После того как определите все аргументы функции, щелкните на кнопке OK, Excel закроет диалоговое окно Аргументы функции и вернется в режим ввода формулы.
Математические и статистические функции
Суммирование чисел: СУММ()
Суммирование групп чисел производится почти в каждой таблице. Эта задача выполняется функцией СУММ(), которая просто складывает все, что ей передается.
Функция СУММ() принимает более 200 аргументов, каждый из которых может быть отдельной ссылкой на ячейку или диапазоном ячеек:
СУММ(А1;А2) складывает две ячейки
СУММ(А2:А12) складывает ячейкив диапазоне с А2 по А12
СУММ(А2:А12;В5;429,1;35000) складывает ячейки в диапазоне с отдельной ячейкой, на которую указывает ссылка, и двумя числами
Пользователи так часто суммируют значения строки или столбца, чтоExcel предлагает кнопку, специально предназначенную только для этой цели: Автосумма. Чтобы найти ее, воспользуйтесь командой Формулы, Библиотека функций, Автосумма:
 |
Когда вы нажимаете кнопку Автосумма, Excel пытается сделать предположение о том, какие ячейки вы хотите сложить. Например, если вы находитесь в конце строки, Excel предполагает, что вы хотите сложить все числовые значения во всех столбцах слева. Если вы находитесь внизу столбца чисел, Excel предполагает, что вы хотите сложить значения в столбце. При этом Excelобводит выбранный диапазон рамкой. Если диапазон указан правильно, нужно нажать клавишу Enter, если вы хотите суммировать другой диапазон, то выберите его с помощью мыши.
: Задание 4. Функция суммирования СУММ()
1. Просуммируй общую стоимость договоров. Для этого установите курсор на листе Склад в ячейку G17 и выполните команду Формулы, Библиотека функций, Автосумма.
Функция автосуммирования предназначена не только для сложения. Вы также можете использовать ее для вычисления средних количеств, максимумов и минимумов. Чтобы сделать это, не щелкайте на основной части кнопки Автосумма. Вместо этого щелкните на стрелке, расположенной справа на кнопке Автосумма, чтобы получить список возможных вычислений, включающий суммирование, получение среднего, подсчет количества и нахождение максимума или минимума:
Дата добавления: 2019-01-14; просмотров: 517; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!