Суммесли(диапазон;условие;диапазон_суммирования)

ИНФОРМАТИКА

 

 

Методические указания

по выполнению лабораторных работ

в среде табличного процессора EXCEL 2007

для студентов всех форм обучения

 

Специальности: все, кроме 080502(8)

 

 

Санкт-Петербург

2009

Допущено

редакционно-издательским советом СПбГИЭУ

в качестве методического издания

Составители:

ст. преподаватель Г.А. Мамаева

доцент Н.Н. Махальцева

Рецензент

д-р техн. наук, проф. В.В. Котов

 

 

Подготовлено на кафедре

вычислительных систем и программирования

 

 

Одобрено научно-методическим советом Университета

 

Отпечатано в авторской редакции с оригинал-макета,

представленного составителями

 

© СПбГИЭУ, 2009

СОДЕРЖАНИЕ

 

ВВЕДЕНИЕ.......................................................................................4

ЛАБОРАТОРНАЯ РАБОТА № 1. 5

Создание и оформление таблиц на одном.. 5

рабочем листе. 5

 

ЛАБОРАТОРНАЯ РАБОТА № 2. 22

Графическое представление табличных данных. 22

 

ЛАБОРАТОРНАЯ РАБОТА № 3. 36

Структурирование, консолидация данных, 36

построение сводных таблиц и диаграмм.. 36

 

ЛАБОРАТОРНАЯ РАБОТА № 4. 51

Использование сценариев модели “что-если”, 51

средств подбора параметра и поиска решения. 51

для анализа данных. 51

 

ЛАБОРАТОРНАЯ РАБОТА № 5. 63

Создание, редактирование и использование шаблонов. 63

 

ЛАБОРАТОРНАЯ РАБОТА № 6. 71

Математические функции МОБР, МОПРЕД и МУМНОЖ. 71

Запись макросов с помощью макрорекордера. 71

и способы выполнения макросов. 71

 

Список литературы.. 87

 

ВВЕДЕНИЕ

 

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

Версия Microsoft Office Excel 2007, помимо новых возможностей, отличается еще и новым интерфейсом, а, следовательно, и новыми методами и приемами работы.

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

Основное новшество интерфейса программы Microsoft Excel 2007 – лента. Это широкая полоса, которая расположена в верхней части окна и содержит все команды. На ленте выделены основные задачи для каждого приложения, а каждая задача представлена вкладкой. С помощью ленты можно быстро находить необходимые команды, которые упорядочены в логические группы, собранные на вкладках. Каждая вкладка связана с видом выполняемого действия. Чтобы увеличить рабочую область, некоторые вкладки выводятся на экран только по мере необходимости.

Чтобы можно было исследовать большие объемы данных на листах, Office Excel 2007 поддерживает листы размером до одного миллиона строк и 16-ти тысяч столбцов. Так сетка Office Excel 2007 состоит из 1 048 576 строк и 16 384 столбцов, что обеспечивает увеличение числа строк на 1500% и столбцов на 6300% по сравнению с приложением Microsoft Office Excel 2003.

Основные технические характеристики и ограничения листа и книги MS Office EXCEL 2007

Параметр	Максимальное значение
Количество открытых книг	Ограничено объемом доступной оперативной памяти и ресурсами системы
Количество листов в книге	Ограничено объемом доступной оперативной памяти (по умолчанию 3 листа)
Общее количество знаков в ячейке	32 767 знаков
Количество числовых форматов в книге	От 200 до 250, в зависимости от установленной языковой версии Excel
Пределы масштабирования	от 10 до 400 процентов
Количество уровней отмены	100
Количество вычисляемых ячеек в надстройке «Поиск решения»	200
Количество цветов в книге	16 миллионов цветов (32-битовый спектр с полным доступом к 24-битовому спектру)

 

ЛАБОРАТОРНАЯ РАБОТА № 1

Создание и оформление таблиц на одном

Рабочем листе

 

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков по созданию простых таблиц:

· ввод данных (констант и формул) в таблицу, в том числе использование автозаполнения;

· редактирование рабочего листа (копирование, перемещение, удаление и редактирование данных);

· числовое и стилистическое форматирование рабочего листа, в том числе выравнивание, границы, использование цвета и узоров, изменение ширины столбцов, условное форматирование.

 

Основные сведения о построении формул

Формула в EXCEL – это такая комбинация констант (значений), ссылок на ячейки, имен, функций и операторов, по которой из заданных значений выводится новое.

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

В формулах используются следующие арифметические операторы: ^ возведение в степень, * умножение, / деление, + сложение, - вычитание;

Ссылки применяются для обозначения ячеек или групп ячеек рабочего листа.

Для построения ссылок используются заголовки столбцов и строк рабочего листа.

Существует три типа ссылок: относительные, абсолютные и смешанные.

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

Абсолютная ($A$1) – указывает, как найти ячейку на основании её точного местоположения на рабочем листе.

Смешанная (A$1, $A1) – указывает, как найти другую ячейку на основе сочетания абсолютной ссылки на строку и относительной на столбец и наоборот.

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

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

 

 

СУММЕСЛИ

Функция СУММЕСЛИ суммирует ячейки, отвечающие заданному критерию.

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

Диапазон – определяет интервал вычисляемых ячеек.

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

Диапазон_суммирования – фактические ячейки для суммирования. Суммируются те ячейки диапазона, которые удовлетворяют условию. Если диапазон суммирования отсутствует, то суммируются ячейки аргумента «диапазон».

СЧЕТЕСЛИ

Функция СЧЕТЕСЛИ подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию.