MS Excel 2007 Занятие 2

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Практическое занятие №2. Функции и работа с данными в MS Excel 2007.

ЦЕЛЬ РАБОТЫ

Изучить встроенные функции MS Excel 2007. Изучить инструменты работы с массивами данных.

СОДЕРЖАНИЕ РАБОТЫ

1.Встроенные функции MS Excel 2007.

2.Условное форматирование.

3.Присвоение и использование имен ячеек.

4.Проверка данных при вводе.

5.Основные ошибки и способы их исправления.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Горячие клавиши

Shift+F3 – вызов мастера функций

Ctrl+Shift+Enter – формула массива

F4 – переход между типами ссылок в режиме редактирования формул

Условной форматирование

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

Рис. 2.1. Окно выбора типа условного форматирования

Для задания условного форматирования надо выделить блок ячеек и выбрать команду Главная – Стили – Условное форматирование. В открывшемся меню (Рис. 2.1) для задания определенного правила выделения ячеек можно выбрать пункты Правила выделения ячеек или Правила отбора первых и последних значений и задать необходимые условия. Либо создать свое правило отбора ячеек, использовав пункт Создать правило.

Также ячейки со значениями могут быть выделены:

1

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

-цветовыми гистограммами (Условное форматирование – Гистограммы) – отображение в ячейке горизонтальной полоски длиной, пропорциональной числу в ячейке;

-цветовыми шкалами (Условное форматирование – Цветовые шкалы) – задание фона ячеек градиентной заливкой с оттенком, зависящим от числового значения. (Например, при задании трехцветной заливки для значений меньше среднего применяется красный цвет фона, для средних – желтый, для больших – зеленый. Причем для заливки фона конкретной ячейки применяется свой оттенок цвета);

-значками (Условное форматирование – Наборы значков) – вставка в ячейки определенных значков в зависимости от процентных значений в ячейках. (При задании этого вида форматирования процентная шкала от 0 до 100% разбивается на 3 равные части для набора из трех значков, на 4 – для четырех и т. д. и для каждой части процентной шкалы назначается свой значок).

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

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

Рис. 2.2. Редактирование условного форматирования

Редактирование условного форматирования осуществляется через команду Управление стилями. В открывшемся окне диалога можно просматривать, редактировать и удалять все условные форматирования диапазона, листа, книги.

Форматирование с помощью объекта «таблица» В Excel 2007 одной из наиболее значимых новинок являются объекты «таблица». Для превращения диапазона ячеек со структурированными данными в объект «таблица» необходимо выполнить команду Вставка – Таблицы – Таблица. После этого с данным объектом можно производить значительно больше операций, чем с просто выделенным диапазоном ячеек, в том числе применять некоторые особенные способы форматирования.

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

2

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

принимает вид в соответствии с параметрами этого стиля. Также на панели Конструктор – Параметры стилей таблиц с помощью команд-флажков можно указать, какие элементы таблицы добавить или особым образом отформатировать (первый/последний столбец, чередующиеся строки/столбцы).

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

Проверка данных при вводе

Если необходимо быть уверенным в том, что на лист введены правильные данные, можно указать критерии, которые являются допустимыми для отдельных ячеек или диапазонов ячеек. Для задания проверки выполните команду Данные – Работа с данными

– Проверка данных. В появившемся окне (рис. 2.2) задайте критерии проверки на вкладке Параметры, текст сообщения-подсказки пользователю для ввода на вкладке Сообщение для ввода, текст сообщения об ошибке на вкладке

Сообщение об ошибке. После применения команды Данные – Работа с данными – Обвести верные данные все неверные данные будут обведены красными кружками.

Рис. 2.2. Окно задания параметров проверки данных

Встроенные функции Excel

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

Обращение к функции: ИМЯ_ФУНКЦИИ(аргумент1;аргумент2;…). Имя функции не содержит пробелов. Аргументы идут через ; и их количество зависит от функции.

В Excel 2007 существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры (при этом активируется средство Автозаполнение формул, позволяющее по первым введенным буквам выбрать нужную функцию (рис. 2.3)), а можно выбирать в

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

3

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Рис. 2.3. Автозаполнение формул

Формулы можно отредактировать так же, как и содержимое любой другой ячейки. Чтобы отредактировать содержимое формулы: дважды щелкните по ячейке с формулой, либо нажмите F2, либо отредактируйте содержимое в строке ввода формул.

Рис. 2.4. Знаки операций

Рис. 2.5. Типы ссылок

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

Рис. 2.6. Типы ссылок

4

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

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

5

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Сложить если…

Статистические функции

Счет, Среднее если…

6

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Ссылки и массивы

ПОИСКПОЗ и ИНДЕКС часто используют вместе, что позволяет по найденному значению в одном столбце найти соответствующее ему значение из другого столбца

Логические функции

7

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Дата и время

8

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Формулы массивов

Массив – набор значений, который воспринимается как единый объект.

Массивы могут быть одномерными (столбец или строка), – их часто называют векторами. Excel может работать и с двумерными массивами (несколько строк и столбцов), – их еще называют матрицами или таблицами.

Операции с массивами

Функция ТРАНСП(массив) – транспонирует массив (переписывает строки массива столбцами); в английской версии функция называется Transpose.

Ввод формулы массива.

Результат формулы – массив ответов. Для ввода формулы массива нужно выполнить следующее:

1.Выделить пустые ячейки (массив ответов);

2.Ввести формулу: =, затем выделить первый массив, ввести знак операции (+ - * /) и выделить второй массив.

3.Нажать одновременно: Ctrl+Shift+Enter

Вмассива ответов будет введена формула, одинаковая для всех ячеек. При просмотре в строке формул будет видно, что формула массива заключена в фигурные скобки {признак формулы массива}.

Вдиапазоне массива (массив ответов) нельзя изменять или удалять формулы в отдельных ячейках. Это можно сделать только для всего массива.

Изменение формулы массива

1. Выделить весь массив (вручную или F5 – Выделить – Текущий массив); 2. Изменить формулу в строке формул (или нажать F2, изменить в ячейке); 3. Нажать CTRL+SHIFT+ENTER.

9

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна

Лабораторный практикум по предмету "Основы компьютерных технологий", кафедра САПР

Текстовые функции

Присвоение и использование имен ячеек

В Excel 2007 имеется полезная возможность присваивания имен ячейкам или диапазонам. Это бывает особенно удобно при составлении формул. Например, задав для какой-либо ячейки имя Итого_за_год, можно во всех формулах вместо адреса ячейки указывать это имя.

10

Преподаватели: Хохлов Петр Владимирович, Хохлова Валентина Николаевна