Файл: Лекция Электронная таблица Excel, основные возможности 3 1 Мастер функций MicrosoftExcel, простые вычисления 3.docx

Лекция 1. Электронная таблица Excel, основные возможности 3

5.1 Мастер функций MicrosoftExcel, простые вычисления 3

1.2 Математические и статистические функции 5

Лекция 2. Логические функции 8

2.1 Логические функциив Excel 8

2.2 Вложенные функции 8

2.3 Расчеты с использованием функций баз данных 10

Лекция 3. Построение графиков и диаграммв Excel, фильтрация баз данных, создание сводных таблиц 14

3.1 Последовательность построения диаграмм, режимы работы 14

3.2 Редактирование диаграмм 19

3.3 Фильтрация баз данных 21

3.4 Создание сводных таблиц 28

Лекция 1. Электронная таблица Excel, основные возможности

План
1.1 Мастер функций, простые вычисления.

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

5.1 Мастер функций MicrosoftExcel, простые вычисления

Для проведения сложных расчетов с помощью формул можно (а чаще – просто необходимо) использовать стандартные функции MicrosoftExcel. Всего их более 500. Все они сгруппированы в несколько групп по типу решаемых задач. Каждая функция имеет свой список аргументов, который заключен в скобки и все аргументы отделены друг от друга точкой с запятой «;». Ниже приведены некоторые из функций.

Формулы в MicrosoftExcel

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

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

Пример записи формулы:

= А$1+100+ВЗ.

Ссылка — это запись адреса ячейки в составе формулы.

Например, в формуле =(А5+$С$3) содержатся две ссылки: А5 и $С$3.

Ссылки бывают относительные и абсолютные. Относительные ссылки при копировании или перенесении формулы меняются, обозначаются они, например, так: D1, F4. Абсолютные ссылки при копировании или перенесении формулы не изменяются.

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

абсолютными по строке – будет меняться только имя столбца, например, F$4;
абсолютными по столбцу – будет меняться только номер строки, например, $R3;
абсолютными и по строке, и по столбцу – ничего не будет изменяться, например, $E$3.

Для определения ссылки на ячейки, находящиеся на определенном листе, необходимо указать название этого листа. Например, Лист1!G3, 'Работа 3'!D2, 'Расчет окладов'!D2:F3.

Для определения ссылки на ячейки, находящиеся в определенной рабочей книге, необходимо указать название этой книги с указанием в квадратных скобках диска и папки, в которой книга находится. Например, [C:\lab1.xls]Лист1!G3, [H:\LENA\L2.XLS]'Работа 3'!D2.

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

Для разных функций аргументов может быть от 1 до 30, а для некоторых функций аргументов может не быть.

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

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

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

Кнопка Отмена формулы

Панель формул

Кнопка Ввод формулы

Кнопка Изменить формулу

Рисунок 1 – Вставка функций
После выбора нужной функции необходимо задать ее аргументы (рисунок 12). Например, для функции СУММ аргументом должны являться числа, которые можно задать их перечислением в окне функции или указанием адреса нужного диапазона ячеек.

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

=ОКРУГЛ(СЛЧИС()*(100-30)+30;2) использует вложенную функцию СЛЧИС.

1.2 Математические и статистические функции

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

Функция СРЗНАЧ – подсчитывает среднее арифметическое чисел, находящихся по указанному адресу ячейки или по указанным адресам блока ячеек.

Общий вид : =СРЗНАЧ (число1; число2; число3; …)

Например, формула =СРЗНАЧ(В1:ВЗ;С4:С8;2) определит, сколько чисел находится в блоках ячеек В1:ВЗ и С4:С8, сложит их, к сумме прибавит 2 и найдет их среднее арифметическое.
Функция ABS– вычисляет абсолютное значение (модуль) указанного числа.

Общий вид : =ABS(число)
Например, формула =ABS(В1) определит модуль числа, записанного в ячейку B1. В результате расчета по формуле =ABS(-57) получим ответ 57.
Функция СТЕПЕНЬ – возводит число в указанную степень.

Общий вид : =СТЕПЕНЬ(число;степень)
Например, формула =СТЕПЕНЬ(В1;3) возведет число, записанное в ячейку B1, в куб. Расчет по формуле =СТЕПЕНЬ(2;-2) дает ответ 0,25, т.е. определит чему равно 2 во 2-й степени и единицу разделит на это число.
Функция СЛЧИС() - задает случайные числа в интервале от 0 до 1.

Общий вид : =СЛЧИС()

Если надо получить число в интервале от А до В, то необходимо воспользоваться формулой: =СЛЧИС()*(В-А)+А.
Например, для задания случайного числа в интервале от 10 до 230 надо ввести в ячейку формулу:

=СЛЧИС()*(230-10)+10
Функция ОКРУГЛ – округляет число до заданного числа знаков после запятой по правилам математики.

Общий вид : =ОКРУГЛ(число; число_десятичных_знаков)

Округление происходит по правилам математики.
Функция СУММ - суммирует числа, находящиеся по указанному адресу ячейки или по указанным адресам блока ячеек.

Общий вид : =СУММ (число1; число2; число3; …)

Например, формула =СУММ(В1:ВЗ;С4:С8;2) суммирует числа, находящиеся в блоках ячеек В1:ВЗ и С4:С8 с числом 2.

Функция СЧЁТ – подсчитывает количество чисел, находящихся по указанному адресу ячейки или по указанным адресам блока ячеек.

Общий вид : =СЧЁТ(число1; число2; число3; …)

Например, формула =СЧЁТ(В1:ВЗ;С4:С8;2) определит, сколько чисел находится в блоках ячеек В1:ВЗ и С4:С8, и прибавит 1, т.к. имеется число 2.

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

Общий вид:

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

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

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

2 Критерий – только одно условие, которое может быть текстом или числом.

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

Дана таблица 1

1) Необходимо определить: число пассажиров самолетов.
Таблица 1 – Транспортные средства

	A	B	C
1	Наименование транспортного средства	Цвет	Число пассажиров
2	Поезд	Синий	250
3	Автомобиль	Белый	5
4	Самолет	Белый	250
5	Автомобиль	Черный	7
6	Поезд	Белый	206
7	Трамвай	Синий	100
8	Троллейбус	Белый	98
9	Самолет	Синий	260