Офисные технологии обработки информации

Полный формат записи внешней ссылки

[Имя_книги]Имя_листа!Адрес_ячейки

С помощью внешних ссылок создаются связи между рабочими листами и рабочими книгами.

Ввод формул. Начинать ввод формул необходимо со знака равенства (=). Завершать ввод формулы можно двумя способами: нажатием на клавишу Enter или нажатием на пиктограмму Enter (зеленая галочка ), находящуюся в строке формул. Отличие между этими способами заключается в том, что при нажатии на пиктограмму адрес активной ячейки не меняется, в то время как при нажатии на клавишу Enter становится активной другая ячейка.

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

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

Задание 3

Откройте документ ПрактикумExcel и решите задачи на вычисления с использованием простых формул.

3.1. На листе Прибыль проведите расчет итоговых значений доходов, расходов и прибыли.

Прибыль = Приход- Расход

3.2. На листе Продажи проведите расчет суммы продаж.

3.3. На листе Депозит определите накопления на банковском счете в конце каждого года. Банк проводит начисления на вклады, используя сложные проценты.

Накопления для i -го года = Вклад*(1+Ставка)^ Год

2.4 Логические и математические функции

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

Для вставки функции используется Мастер функций (значок f_x в строке формул), который помогает выбрать функцию, выдает подробную справку и перечень аргументов данной функции. Мастер отображает результат вычисления каждого шага на экране и добавляет в формулу разделители аргументов (точки с запятой).

Функция ЕСЛИ производит вычисления в зависимости от условия. Если заданное условие истинно, вычисляется первая формула, если условие ложно, то вторая. Причем формула, в свою очередь может включать функцию ЕСЛИ. Возможно до 7 уровней вложенности функции ЕСЛИ. Условие представляет собой логическое выражение, которое в свою очередь может включать различные логические функции.

Формат функции:

ЕСЛИ (Условие; Формула 1; Формула 2)

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

Функция И принимает значение ИСТИНА, если выполняются все условия (т.е. истинны все её аргументы), в противном случае она принимает значение ЛОЖЬ.

Формат функции:

И (Условие1; Условие2; …; Условие30).

Функция ИЛИ принимает значение ИСТИНА, если выполняется хотя бы одно из условий, (т.е. хотя бы один ее аргумент имеет значение ИСТИНА), в противном случае она принимает значение ЛОЖЬ.

Формат функции:

ИЛИ (Условие1; Условие2; …; Условие30).

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

Наиболее распространенная операция, используемая при обработке данных, - суммирование. В Excel существует специальная пиктограмма ? (Автосумма) на вкладке Главная, ускоряющая ввод функции суммы. При нажатии на эту пиктограмму производится суммирование данных, находящихся в столбце, выше активной ячейки. Если в столбце нет числовых данных, то производится суммирование данных строки.

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

Формат функции:

СЧЕТЕСЛИ (Диапазон; условие).

Пример. Пусть в диапазоне В2:В101 находятся оценки, представленные в текстовом виде. Подсчитать сколько раз встречается оценка - Отлично.

=СЧЕТЕСЛИ(В2:В101;”Отлично ”)

Пусть в диапазоне С2:С101 находятся оценки, представленные в виде чисел. Подсчитать сколько раз встречается оценка - 4 и 5.

=СЧЕТЕСЛИ(С2:С101;”>=4”).

Задание 4

Откройте документ ПрактикумExcel и проведите анализ результатов экзаменов.

4.1.На листе Результаты определите общий балл (сумма всех баллов за экзамены).

4.2. Напечатайте в колонке Результат (прошел / не прошел) результаты вступительных экзаменов по поступлению в вуз. Абитуриент прошел в институт, если общий балл больше или равен проходному баллу. Используйте функцию ЕСЛИ.

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

4.4. Подсчитайте количество абитуриентов, прошедших в институт. Используйте функцию СЧЕТЕСЛИ.

4.5. Подсчитайте количество студентов, получивших общежитие. Используйте функцию СЧЕТЕСЛИ.

4. 6. На листе Экзамен по результатам сдачи экзамена подсчитайте количество хороших, отличных, удовлетворительных и неудовлетворительных оценок, а также число неявок.

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

Функции МАКС, МИН, СРЗНАЧ, подсчитывают: максимум, минимум и среднее значение чисел, находящихся в различных диапазонах данных. Функции могут иметь от 1 до 30 аргументов. В качестве аргументов можно указывать ячейки и диапазоны ячеек.

Форматы функций

СУММ (Диапазон1; Диапазон2; …. Диапазон30)

МАКС (Диапазон1; Диапазон2; …. Диапазон30)

МИН(Диапазон1; Диапазон2; …. Диапазон30)

СРЗНАЧ(Диапазон1; Диапазон2; …. Диапазон30)

Пример. Пусть в диапазоне С6:С14 находится информация об окладе сотрудников, тогда средний оклад = СРЗНАЧ(С6:С14), суммарный оклад = СУММ (С6:С14 ), минимальный оклад = МИН (С6:С14 ).

Функции СЧЕТ и СЧЕТЗ служат для определения количества значений в диапазонах данных. СЧЕТ подсчитывает количество числовых значений. СЧЕТЗ подсчитывает количество не пустых ячеек.

Форматы функций

СЧЕТ (Диапазон1; Диапазон2; …. Диапазон30)

СЧЕТЗ (Диапазон1; Диапазон2; …. Диапазон30)

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

Формат функции :

СУММЕСЛИ (Диапазон проверки условий; Условие; Диапазон суммирования).

Пример. Требуется определить фонд заработной платы, выплачиваемой сотрудникам первого отдела. Диапазон проверки условий находится в диапазоне А2:А5. Условие (название отдела - первый) находится в ячейке Е2. Диапазон суммирования (оклады сотрудников) находится в ячейках С2:С5. Результат вычисления функции СУММЕСЛИ находится в ячейке Е2 (см. рис.34).

Задание 5

Откройте документ Практикум Excel и изучите способы применения статистических функций.

5.1. На листе Работа проведите анализ количества рабочих часов для работников наемного труда за период с 1974 по 2003гг. Определите среднее, максимальное и минимальное количество рабочих часов, используя функции СРЗНАЧ, МАКС и МИН.

5. 2.На листе Сотрудники определите фонд заработной платы по каждому отделу, используя функцию СУММЕСЛИ.

2.6 Функции поиска

Функции ВПР и ГПР служат для поиска информации в таблице. Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение из найденной строки и указанного столбца таблицы. Функции ГПР ищет значение в верхней строке таблицы и возвращает значение из найденного столбца и заданной строки таблицы.

Форматы функций

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)

Аргумент интервальный_просмотр имеет два значения 0 (ЛОЖЬ) и 1 (ИСТИНА).

ЛОЖЬ - поиск по точному соответствию, если значение не найдено, результатом выполнения функции будет ошибка.

ИСТИНА - поиск по приближенному соответствию, если значение не найдено, то ищется первое значение, которое меньше искомого. Для поиска по приближенному соответствию таблица должна быть отсортирована в порядке возрастания по первому столбцу таблицы (ВПР) или по первой строке таблицы (ГПР). Пример. Имеется информация о рейтинге студентов по 100-бальной шкале. Требуется проставить оценки по 5- бальной шкале, используя таблицу, в которой приводится соответствие между 100-бальной и 5 -бальной шкалой. Аргументы функции ВПР: искомое_значение - рейтинг в первой таблице; таблица - диапазон Е1:Е5; номер_столбца 2; интервальный_просмотр - ИСТИНА.

Функция ВПР осуществляет поиск по приближенному соответствию и находит первое значение, которое меньше искомого (см. рис. 35).



Рис.35

Применение функции ВПР и ГПР позволяют заменить использование вложенных функций ЕСЛИ при решении задач, в которых вычисление выражения зависит от изменения одной переменной.

При поиске по точному соответствию (интервальный_просмотр = 0) в случае если в таблице отсутствуют искомые значения, вычисление функций ВПР и ГПР дают ошибку #Н/Д (недопустимые значения данных).

Задание 6

Откройте документ Практикум Excel и сравните применение функций ВПР и ЕСЛИ при решении задачи о назначении стипендии.

6.1. На листе Стипендия определите общий балл (сумма всех баллов за экзамены). Стипендия назначается по следующим правилам. Если общий балл больше или равен 24, то назначается повышенная стипендия, если балл больше или равен 20, то обычная стипендия, если балл меньше 20, то стипендия не назначается.

6.2. Напечатайте в колонке Стипендия результат назначения стипендии (да / нет / повыш.), используя вложенную функцию ЕСЛИ. (Колонка Н).

6.3. Напечатайте в колонке Стипендия результат назначения стипендии (да / нет / повыш.), используя вложенную функцию ВПР. (Колонка J).

2.7 Диаграммы

Excel предоставляет большое разнообразие стандартных типов диаграмм: это гистограммы, графики, круговые, точечные и т. д. (см. рис. 36)

Рис. 36

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

- таблица должна иметь заголовки строк и столбцов;

- в таблице не должно быть объединенных ячеек;

- необходимо всю числовую информацию располагать в правых колонках таблицы, а символьную в левых.

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

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

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

Технология построения круговых диаграмм

1. Проверьте правильность оформления ячеек таблицы. Разъедините объединенные ячейки.

2. Выделите диапазон данных. В диапазон включите названия столбцов или строк.

3. Перейдите на вкладку Вставка и выберите пиктограмму Круговая. Круговая диаграмма строится только для одного ряда данных, образующих строки или столбцы таблицы. Excel самостоятельно определяет откуда брать данные: из столбцов или строк. И иногда делает это неверно, тогда диаграмма принимает форму круга. Это означает, что данные находятся в столбце, а берутся из строки или наоборот данные находятся в строке, а берутся из столбца. Для того чтобы это исправить необходимо перейти на вкладку Конструктор (см. рис. 37) и выбрать пиктограмму Строка/столбец. Диаграмма будет построена.

Рис. 37

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

Рис. 38

В Excel 7.0 для редактирования диаграмм имеются три вкладки: Конструктор, Макет и Формат (см. рис. 37, 38, 39). Эти вкладки автоматически появляются на экране при активизации диаграммы.



Рис. 39

Типовыми операциями по редактированию круговых диаграмм являются: создание заголовка, поворот диаграммы, изменение подписей. Для всех этих операций имеются специальные пиктограммы на вкладке Макет:

- создание заголовка - Название диаграммы,

- поворот диаграммы - Поворот объемной фигуры,

- изменение подписей - Подписи данных.

При нажатии на пиктограмму Поворот объемной фигуры появляется диалоговое окно (см. рис. 40), в котором диаграмму можно повернуть по горизонтали (опция Х) и по вертикали (опция Y).

Рис.40

Рис. 41

Для того чтобы вызвать диалоговое окно Формат подписей данных (см. рис. 41), нужно нажать на пиктограмму Подписи данных и выбрать пункт меню Дополнительные параметры подписей. Опция Имена категорий проставит подписи для каждого сектора диаграммы. В случае если секторов на диаграмме много желательно установить опцию Линии выноски. Опция Доли покажет на диаграмме проценты Любой элемент структуры диаграммы можно форматировать. Для этого нужно его выделить, а затем использовать контекстное меню. Элементами структуры диаграммы являются: область диаграммы; область построения; оси диаграммы; ряды данных и отдельный элемент в любом ряду данных; легенда, ключ легенды; метка ряда в легенде; подписи данных; заголовки диаграммы и осей; линии сетки; таблица данных (см. рис. 42).

Рис. 42

Для каждого элемента структуры диаграммы имеется специальное диалоговое окно, в котором можно изменять параметры шрифта, заливку и форматы линий.

Задание 7

Откройте документ Практикум Excel и изучите технологию построения диаграмм.

7.1. На листе Показатели рассчитайте отклонение фактических показателей от плановых показателей и постройте гистограмму.

7.2. На листе Налоги постройте круговую диаграмму, отображающую структуру собираемости налогов в 2009г.

7.3. На листе Диаграммы объясните странный вид диаграмм.

2.8 Фильтры

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

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

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

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

Можно последовательно задавать условия к нескольким колонкам таблицы.

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



Рис. 43

Для отбора символьных значений имеются специальные функции (см. рис 43):

- начинается с (набор символов),

- заканчивается на (набор символов),

- содержит (набор символов).

Рис. 44

Дополнительные условия фильтрации задаются в диалоговом окне Пользовательский автофильтр (см. рис. 44) (выпадающее меню -Настраиваемый фильтр.).

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

Задание 8

Откройте документ Практикум Excel и изучите способы фильтрации данных.

8.1. На листе Рейтинг с помощью фильтра отберите отличников, и результат фильтрации поместите в таблицу Отличники на листе Успеваемость.

8.2. На листе Рейтинг с помощью фильтра отберите хорошистов по специальности экономика, и результат фильтрации поместите в таблицу Хорошисты на листе Успеваемость.

8.3. На листе БД помощью фильтра найдите виновника дорожно-транспортного происшествия (ДТП), используя показания свидетелей.

2.9 Промежуточные итоги

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

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

Технология проведения операции промежуточных итогов

1. Отсортировать список по полю группировки.

2. Активизировать одну ячейку в списке и вызвать диалоговое окно Промежуточные итоги. (Вкладка Данные - пиктограмма Структура - пиктограмма Промежуточные итоги.)

3. В диалоговом окне Промежуточные итоги (см. рис.45):

- в строке При каждом изменении в указать имя поля группировки,

- в строке Операция выбрать операцию обработки данных,

- в строке Добавить итоги по выбрать поля вычислений,

- включить флажок Заменить текущие итоги.



Рис. 45

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

1. Проводим сортировку по полю Специальность. Устанавливаем курсор в таблице Рейтинг и нажимаем на пиктограмму Сортировка (вкладка Данные).

2. В диалоговом окне Сортировка диапазона в строке Сортировать по выбираем поле Специальность.

3. Вызываем диалоговое окно Промежуточные итоги. (Вкладка Данные - пиктограмма Структура - пиктограмма Промежуточные итоги.)

4. В диалоговом окне Промежуточные итоги (см. рис. 45):

- в строке При каждом изменении в - выбираем Специальность,

- в строке Операция выбираем Среднее,

- в строке Добавить итоги по выбираем поля Информатика, Математика, История.

При подведении итогов Excel структурирует таблицу. На нижний уровень структуры попадают исходные данные каждой группы. С помощью символов управления структурой можно просматривать данные, промежуточные и общие итоги, только общие итоги.



Рис. 46

На рисунке 46 приведен вид таблицы Рейтинг после проведения операции Промежуточных итогов (см. пример).

Данные в таблице сгруппированы по специальностям. После каждой группы специальностей в таблицу добавлена строка с промежуточными итогами по полям Информатика, Математика, История.

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

Для удаления промежуточных итогов следует вызвать диалоговое окно Промежуточные итоги и нажать кнопку Убрать все.

Задание 9

Откройте документ Практикум Excel и проведите обработку данных с помощью Промежуточных итогов.

9.1. На листе Рейтинг требуется определить средний рейтинг студентов по каждой специальности и каждому предмету с помощью промежуточных итогов.

9.2. На листе Сотрудники требуется определить средний оклад по каждому отделу с помощью промежуточных итогов.

2.10 Сводные таблицы

Сводные таблицы - служат для обработки данных в группах.

Анализ данных исходной таблицы с помощью технологии сводных таблиц состоит из двух этапов:

- на первом этапе необходимо указать диапазон данных, в котором находится исходная таблица;

- на втором этапе необходимо спроектировать макет сводной таблицы.

Макет сводной таблицы состоит из двух областей (см. рис.47):

первая - область данных, куда помещаются названия колонок таблицы, которые необходимо обработать;

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

Рис. 47

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

Для обработки данных с помощью сводных таблиц исходная таблица должна быть определенным образом оформлена:

- все заголовки таблицы должны иметь различные имена;

- данные в колонках таблицы должны быть однородными;

- в таблице не должно быть объединенных ячеек;

- таблица должна быть окружена пустыми строками и колонками.

Технология создания сводных таблиц.

1. Установить курсор на любой клетке таблицы.

2. Вызвать мастер сводных таблиц. (Вкладка Вставка -пиктограмма Сводные таблицы.)

3. В диалоговом окне Создание сводной таблицы указать:

- Диапазон исходной таблицы. Если таблица правильно оформлена мастер сводных таблиц определяет диапазон автоматически (см. рис.48).

- Расположение первой ячейки сводной таблицы. Если таблица помещается на новый лист, то адрес первой ячейки не надо указывать (см. рис.48).

Рис.48

4. В диалоговом окне Список полей сводной таблицы выбрать колонки (см. рис.49), по которым будет производиться группировка данных и последовательно перетащить их мышью в область группировок макета (см. рис 47).

5. В диалоговом окне Список полей сводной таблицы выбрать колонки (см. рис.49), по которым будут производиться вычисления и последовательно перетащить их мышью в область данных (см. рис 47).

Рис.49

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

Рис. 50

Рис. 51

На рисунке 51 представлена сводная таблица, на которой представлено распределение выпуска брака по дням недели.

Задание 10

Откройте документ Практикум Excel и изучите способы автозаполнения данных.

10.1. На листе Сотрудники определить среднюю заработную плату по должностям, используя сводные таблицы.

10.2. На листе Сотрудники подсчитать в процентном отношении объем заработной платы, выплачиваемой по каждому отделу.

10. 3. На листе БД определить количество машин различных марок и различных цветов.

Размещено на Allbest.ru

...