Функции счетесли, суммесли

Электронные таблицы. Основные понятия

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

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

Адрес ячейки –координаты ячейки, образуемые названием столбца по горизонтали (А, В, С...) и номерами строки по вертикали (1, 2...). Например, А2, С3,... Такой адрес называется относительным, а ссылка на него в расчетах – относительной ссылкой.

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

Относительная ссылка – форма записи адреса ячейки, при которой этот адрес изменяется при копировании формулы (записывается =А1).

Абсолютная ссылка –форма записи адреса ячейки, при которой этот адрес остается неизменным при копировании формулы (записывается =$А$1).

Смешанная ссылка – это форма записи адреса ячейки, при котором при копировании формулы меняется одна из координат (обозначается: =$А1 или =А$1).

Диапазон ячеек – некоторое количество смежных ячеек. Они могут располагаться внутри столбца, строки или в виде прямоугольного блока. Обозначение их, соответственно, А1:А4, А1:D1, А1:D4.

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

Сортировка данных –расположение данных в определенном порядке по какому-либо признаку. Текстовые данные – в алфавитном или обратном порядке, числовые – по возрастанию или убыванию.

Фильтрация данных – отбор данных, удовлетворяющих определенным критериям. В качестве критериев используют условия, в которых содержимое ячейки и требуемое значение связываются оператором сравнения («=», «<», «>»…). Для проведения фильтрации предусмотрена команда меню Данные/Фильтр/Автофильтр (Расширенный фильтр).

Диаграмма – графическое представление табличных данных. Для построения диаграмм необходимо использовать «Мастер диаграмм», вызов которого осуществляется путем активизации кнопки «Диаграмма» на панели инструментов.

Типы диаграмм – наборы диаграмм различных видов, имеющие характерные внешние признаки. В Excel предусмотрены следующие типы диаграмм: «С областями», «Линейчатая», «Гистограмма», «График» и т.д.

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

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

Функция ЕСЛИ используется при проверке условий для значений и формул. Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. В логическом выражении, которым описывается проверяемое условие, используются операторы сравнения и логические операторы И, ИЛИ, НЕ.

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

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

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

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

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

Линия тренда – линия, аппроксимирующая точки массива данных. Используется при построении диаграмм и графиков.

Массивы – совокупность данных, расположенных в смежных ячейках, которые записываются в следующем виде А1:С5.

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

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

Формула массива – специальный вид формул. Одна формула массива позволяет производить действия над диапазоном ячеек. Выражение вида {=(A1:A4)*A6} является формулой массива.

При создании формулы массива предварительно необходимо выделить диапазон ячеек, в который предполагается поместить результат, а создание формулы массива завершается нажатием комбинации клавиш <Ctrl + Shift + Enter>.

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

Операторы сравнения – математические знаки сравнения двух величин или выражений. К ним относятся: «=», «<», «>», и т.д.

2. ЛАБОРАТОРНАЯ РАБОТА №1
Создание и редактирование таблиц. Функции и вычисления в Excel. Графические
возможности Excel

ЦЕЛЬ РАБОТЫ

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

ПЛАН РАБОТЫ

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

2. Создание таблицы. Анализ основных приемов редактирования таблиц.

3. Работа с листами рабочей книги.

4. Знакомство с правилами составления формул. Использование Мастера функций для ввода функций в формулы.

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

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

7. Построение диаграмм. Редактирование и форматирование диаграмм. Создание новых типов диаграмм. Построение диаграмм на основе сводных таблиц. Добавление графических объектов на рабочие листы.

ПРАКТИЧЕСКОЕ ЗАДАНИЕ

1. Запустить программу Excel

2. На Листе1 создать таблицу «Расчет заработной платы» со столбцами: ФИО, Должность, Оклад, Подоходный налог, Пенсионные отчисления, К выдаче. (Ввести название таблицы в ячейку А1. Ввести заголовки столбцов в ячейки А2, В2…)

3. Заполнить исходные данные по 10 сотрудникам (ФИО, Должность, Оклад).

 

Расчеты с помощью простых формул. Относительная и
абсолютная адресация.

4. Рассчитать подоходный налог по формуле: Подоходный налог= Оклад*13%.

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

Проследите, как изменяются относительные ссылки на ячейки.

5. Рассчитать пенсионные отчисления по формуле: Пенсионные отчисления = Оклад*1%.

6. Заполнить столбец К выдаче по формуле: К выдаче = Оклад - Подоходный налог - Пенсионные отчисления.

7. Сохранить файл под именем «Расчет зарплаты» в своей папке.

8. Для продолжения работы скопируйте таблицу с Листа1 на Лист2 и удалите рассчитанные значения столбцов Подоходный налог, Пенсионные отчисления, К выдаче.

9. Отступив от таблицы вниз 3 строки, введите текст:

 

Подоходный налог	13%.
Пенсионные отчисления	1%.

 

10. Заполните столбцы Подоходный налог, Пенсионные отчисления, используя ссылки на соответствующие ячейки.

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

11. Заполнить столбец К выдаче

 

Использование функций. Функции СУММ, СРЗНАЧ, МИН, МАКС.

12. Непосредственно под таблицей добавьте строку ИТОГО и подсчитайте в ней суммарный оклад всех сотрудников, суммарный подоходный налог, суммарные пенсионные отчисления и общую сумму к выдаче. Для этого используйте кнопку Автосуммирование [S] на панели инструментов. Проверьте в строке формул, какая функция при этом используется, какой диапазон ячеек суммируется.

Не забывайте время от времени повторно сохранять файл.

13. Выделите итоговое значение под столбцом К выдаче, выясните, какие ячейки влияют на этот результат. Для этого используйте команду Сервис-Зависимости-Влияющие ячейки, повторив эту команду несколько раз. Чтобы убрать стрелки, используйте Сервис-Зависимости=Убрать все стрелки.

14. Выделите ячейку, в которой находится ставка подоходного налога (13%), выясните, на что она влияет. Используйте команду Сервис-Зависимости-Зависимые ячейки. Уберите стрелки.

15. Отступив вниз 2-3 строки, введите в ячейки текст:

 

Средний оклад	Максимальный оклад	Минимальный оклад

 

и подсчитайте соответствующие величины с помощью функций СРЗНАЧ, МИН, МАКС.

Логические функции ЕСЛИ, И, ИЛИ.

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

Используйте логическую функцию ЕСЛИ.

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

Используйте функции ЕСЛИ, И.

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

Форматирование таблицы

19. Оформите таблицу в соответствии с образцом. Используйте рамки, заливку, шрифтовое оформление, команду Формат-ячейки.

Построение диаграмм.

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

 

3. ЛАБОРАТОРНАЯ РАБОТА №2
Обработка данных в табличном процессоре Excel.
Управление списками в среде Excel.

ЦЕЛЬ РАБОТЫ

Освоение технологии работы со списками. Анализ возможностей поиска и фильтрации данных в среде Excel. Использование функций работы с базами данных в решении задач.

ПЛАН РАБОТЫ

1. Планирование списка или базы данных. Использование форм для создания списков.

2. Сортировка данных. Знакомство с многоуровневой сортировкой.

3. Подведение итогов в списках и базах данных. Изучение методов поиска данных в списках.

4. Знакомство с правилами составления критериев отбора данных. Работа с Автофильтром. Изучение возможностей Расширенного фильтра. Применение метода Расширенного фильтра для поиска и отбора данных.

5. Изучение возможностей анализа данных с использованием сводных таблиц. Освоение технологии формирования сводных таблиц. Анализ данных и подведение итогов с помощью сводных таблиц.

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

ПРАКТИЧЕСКОЕ ЗАДАНИЕ

1. Открыть таблицу, созданную на предыдущем занятии (список сотрудников).

Функции СЧЕТЕСЛИ, СУММЕСЛИ

2. Подсчитать количество штатных сотрудников на предприятии.

3. Подсчитать суммарный оклад штатных сотрудников.

4. Подсчитать средний оклад штатных сотрудников.

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

Сортировка записей

6. Отсортировать таблицу так, чтобы фамилии сотрудников располагались по алфавиту.

7. Отсортировать таблицу так, чтобы в начале списка находились сотрудники с наибольшим количеством детей.

8. Выполнить двухуровневую сортировку – по категории и по алфавиту.

9. Выполнить трехуровневую сортировку, поля для сортировки выбрать самостоятельно.