Лабораторная работа №1 Редактирование рабочей книги. Построение диаграмм

Лабораторная работа №1 Редактирование рабочей книги. Построение диаграмм

Цель работы: Изучение способов работы с данными в ячейке. Изучение возможностей автозаполнения. Построение диаграмм.

 

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

Методика выполнения работы

1. На рабочем столе найти ярлык Microsoft Excel и открыть окно двойным щелчком левой клавиши мыши или ПускÞПрограммыÞMicrosoft OfficeÞ Microsoft Excel.

2. Переименуйте текущий лист Главная ÞФормат Þ Переименовать лист, введите название листа «Ведомость».

3. Добавьте еще один лист в рабочую книгу (щелкните правой кнопкой мыши на ярлыке листа и в контекстном меню выберите команду Вставить).

4. Сохраните созданный вами файл под именем book.xls в своем каталоге (Кнопка Office Þ Сохранить ).

5. Создайте таблицу по предложенному образцу (табл. 5.1). Для этого нужно выполнить следующие действия:

Таблица 5.1

Экзаменационная ведомость

№п/п	Фамилия, имя, отчество	№ зачетной книжки	Оценка	Фамилия экзаменатора
	Смоляков А.П.			Беденко А.Ф.
	Ливанов А.В.
	Белоусова Е.В.
	Ревтова И.С.
	Кравцов А.Ю.
	Немов Л.М.

 

6. В ячейку А1 ввести заголовок «Экзаменационная ведомость», выделить мышкой ячейки А1:E1 протащив по ним указатель, щелкните на ленте Главная на кнопке Объединить и поместить в центре ;

в ячейку A3 ввести «№ п/п»;

в ячейку ВЗ ввести «Фамилия, имя, отчество»;

в ячейку СЗ ввести «№ зачетной книжки»;

в ячейку D3 ввести «Оценка»;

в ячейку ЕЗ ввести «Фамилия экзаменатора».

 

Рисунок 5.44 – Группа инструментов выравнивания

 

7. Отформатируйте ячейки шапки таблицы:

- выделите блок ячеек (АЗ:ЕЗ);

- лента Главная перейдите к группе Выравнивание;

- на панели Выравнивание (рис. 5.44 ) щелкните на кнопках Выровнять по середине, По центру и Перенос текста, а на вкладке Шрифт измените начертание букв и размер шрифта.

8. Измените ширину столбцов, в которые не поместились введенные данные. Для этого можно перетащить границы между строками и столбцами или навести указатель мыши на границу между заголовками столбцов, дважды щелкнуть основной кнопкой мыши. Для более точной настройки надо выполнить команду Формат Þ Высота строки (Ширина столбец) из группы Ячейки.

9. Выполните обрамление таблицы: выделите таблицу, выполните команду Шрифт ÞСписок Границы и с помощью соответствующих кнопок установите границы.

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

- сделайте текущей первую ячейку столбца «№ п\п» и введите в нее цифру 1;

- затем заполните цифрой 2 следующую ячейку этого столбца;

- выделите блок, состоящий из двух заполненных ячеек;

- установите указатель мыши на правый нижний угол выделенного блока. Указатель мыши станет черным крестиком – это маркер заполнения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз;

- или выберите команду Главная Þ Редактировать ÞЗаполнить.

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

11. Скопируйте таблицу на другой рабочий лист при помощи буфера обмена. Для этого следует:

- выделить таблицу или диапазон ячеек;

- правой клавишей мыши вызвать контекстное меню;

- выполнить команду Копировать;

- затем перейти на другой лист;

- установить курсор в первую ячейку предполагаемой таблицы;

- выполнить команду Вставить из контекстного меню.

12. Добавьте в новую таблицу одну строку и один столбец. Для этого нужно:

- выделить столбец;

- щелкнуть правой кнопкой мыши на выделенном диапазоне и в открывшемся контекстном меню выбрать команду Вставить; то же самое повторить для строки.

13. Внесите в таблицу ряд изменений:

- озаглавьте последнюю строку – «Средний балл»;

- озаглавьте последнюю колонку - «Подпись экзаменатора».

14. Вычислите под столбцом «Оценка» средний балл, набрав там формулу =.

 

Задание 2. На основе данных, приведенных в табл. 5.2, постройте несколько типов диаграмм, наглядно показывающих итоги сессии.

 

Таблица 5.2

Средний балл по группе
Группа	Информатика	Математический анализ	История	Экономика
Э101	4,2	3,8	4,5	4,3
Э102	4,0	4,4	4,4	4,2
Э103	3,9	4,0	4,0	3,9
Б101	4,3	4,4	4,4	4,1
Б102	3,8	4,0	4,0	3,6
Б103	3,3	3,9	3,9	3,6
Б104	4,5	4,8	4,8	3,9

Методика выполнения работы

1. На листе 3 создайте таблицу «Сведения о результатах сдачи сессии на факультете», внесите в нее данные.

2. Постройте диаграмму типа Гистограмма или График для всех групп и всех предметов на отдельном листе. Для этого следует:

- выделить всю таблицу;

- выполнить команду меню Вставка ÞДиаграмма и выбрать вид диаграммы, после этого откроется полотно для построения диаграммы и три ленты (конструктор, макет и формат) с инструментами для редактирования диаграммы (рис. 5.45).

 

а)

б)

в)

Рисунок 5.45 – Ленты с инструментами: а – Конструктор диаграмм, б– Макет диаграммы, в – Формат диаграммы

 

3. На шаге 2 можно изменить диапазон ячеек, по которым строится диаграмма, задать другие имена для рядов данных, добавить или удалить ряды данных, изменить диапазон подписей по оси Х или Y (рис. 5.45). Команда Данные Þ Выбрать данные, выделите таблицу полностью(рис. 5.46 ).

Рисунок 5.46 – Диалоговое окно определения рядов данных

4. На третьем шаге построения диаграммы внесите название диаграммы, обозначения осей, добавьте легенду (рис. 5.47). Команда Макет Þ Подписи.

Рисунок 5.47

 

5. Постройте диаграммы других типов и сравните результаты сдачи по предметам: информатика, математический анализ и экономика.

Для этого выделите столбцы «Группа», «Информатика», «Математический анализ» и, удерживая клавишу Ctrl, выделите столбец «Экономика»; выберите тип диаграммы График.

6. Измените, результаты сдачи сессии и проверьте, как это отразилось на построенных диаграммах.

7. На листе 4 постройте таблицу следующего вида (рис. 5.48):

Рисунок 5.48 – Макет таблицы

8. Команда Формулы Þ Библиотека функций. В ячейку В3 введите формулу =МАКС(Лист3!B3:B9); в ячейку В4 формулу =МАКС(Лист3!C3:C9) и т.д. по всем предметам.

9. В ячейку С3 введите формулу =МИН(Лист3!B3:B9) и т.д. по всем предметам. Постройте отдельно графики по максимальным средним баллам и по минимальным.

10. Отчет о работе представьте в виде диаграмм на отдельных листах рабочей книги.

11. Распечатайте созданный документ.

 

Самостоятельное задание

 

1. Дан список сотрудников. Известны фамилии, должность, оклад и коэффициент трудового участия каждого. Начислить всем сотрудникам премию в размере 20% от оклада и вычислить итоговые суммы. Провести графическую интерпретацию данных (построить график и диаграмму).

 

№ п/п	Исполнители	Должность	Оклад	Коэффициент трудового участия	Премия
	Алехина	Ген.директор		1,0
	Коннова	Зам.ген.директора		0,9
	Борисова	Гл.бухгалтер		0,8
	Овчинникова	Экономист		0,9
	Медведев	Менеджер		0,7
	Алдобаев	Вед.специалист		0,6
	Петраков	Инженер		0,5
	Цуканов	Гл.специалист		0,4
	Сорокин	Инженер		0,6
	Кузьменко	Инженер		0,75

 

2. Построить и рассчитать в Excel таблицу следующего вида.

 

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

 

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

 

 

Методика выполнения работы.

1. Откройте новую рабочую книгу и сохраните ее в своей папке.

2. Создайте таблицу, внесите в нее исходные данные задачи (рис. 5.49).

Рисунок 5.49 – Макет таблицы

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

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

5. Подсчитайте сумму выручки от продажи всех видов товаров. Выделить столбец и нажать кнопку Автосумма на ленте Формулы..

Задание №2.

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

2. Сопоставьте доходность акции по уровню дивидендов за 2010 г. по отдельным эмитентам. Исходные данные задачи представлены в таблице 5.3.

Методика выполнения работы.

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

Дивиденды, руб. = Номинал акции, руб. * Дивиденды, %

Доходность к номиналу, % = Дивиденды, %

Доходность фактическая, руб. = Цена продажи, руб. - Номинал акции, руб. + Дивиденды, руб.

Таблица 5.3

Эмитент	Номинал акции, руб.	Цена продажи, руб.	Дивиденды, объявленные в расчете на год	Доходность по дивидендам
%	руб.	К номиналу, %	Фактическая, руб.
Юниаструмбанк			400%
Инкомбанк			400%
Россельхозбанк			320%
Импэксбанк			653%
Сбербанк России			736%
МДМ банк			325%
Промстройбанк			153%

3. Визуально проанализируйте полученные результаты.

4. Добавьте внизу таблицы строку «Среднее значение» и введите формулы по всем столбцам.

5. На основании исходного документа «Доходность акций по отдельным дивидендам» рассчитайте следующие значения:

- средняя цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией СРЗНАЧ);

- максимальная цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией МАКС);

- минимальная цена продажи акций (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией МИН);

- максимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, воспользоваться функцией МАКС);

- минимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, воспользоваться функцией МАКС).

- Результаты расчетов оформите в виде табл. 5.4.

Таблица 5.4

Расчетная величина	Значение
Средняя цена продажи акций
Максимальная цена продажи акций
Минимальная цена продажи акций
Максимальная фактическая доходность акций
Минимальная фактическая доходность акций

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

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

- выделить данные таблицы с прилегающей одной строкой заголовка;

- выполнить команду ДанныеÞ Фильтр;

- в заголовке столбца «Фактическая доходность» нажать кнопку раскрывающегося списка и выбрать Числовые фильтры;

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

8. Вернитесь к исходному виду с помощью команды Данные ÞОчистить.

9. Постройте на отдельном рабочем листе Excel круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделить столбцы «Эмитент» и «Фактическая доходность», выполнить команду Вставка ÞДиаграмма ). На графике показать значения доходности, вывести легенду и название графика «Анализ фактической доходности акций по уровню дивидендов».

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

- выделить столбцы «Эмитент», «Номинал акции» и «Цена продажи»;

- выполнить команду меню Вставка Þ Диаграмма - тип диаграммы Гистограмма - в ведите название диаграммы и по осям - Готово;

- для добавления линейного графика «Фактическая доходность по дивидендам правой клавишей мыши на пустом поле диаграммы Выбрать данные Þ Добавить, в поле Имя ввести название ряда «Доходность», в поле Значения ввести числовой интервал, соответствующий фактической доходности по дивидендам;

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

11. Подготовьте результаты расчетов и диаграммы к выводу на печать.

Самостоятельное задание

 

На предприятии работники имеют следующие оклады: начальник отдела – 1000 руб., инженер 1кат. – 860 руб., инженер – 687 руб., техник – 315 руб., лаборант – 224 руб. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% от оклада ежемесячной премии. Со всех работников удерживают 20% подоходный налог, 3% профсоюзный взнос и 1% в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15% районного коэффициента, работники филиала, расположенного в районе крайнего севера, имеют 70% районный коэффициент и 50% северной надбавки от начислений.

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

 

Методика выполнения работы

1. Открыть новую рабочую книгу. Листу 1 присвоить имя «Продажа служащими ». Заполнить этот лист, как показано ниже на рисунке 5.50.

Рисунок 5.50 – Макет таблицы

2. Для того, чтобы отсортировать данные выполните команду Данные ÞСортировка. Откроется окно «Сортировка» (рис. 5.51). В поле «Сортировать по» установите имя столбца «Имя», щелкнуть Добавить уровень, в поле «Затем по» - имя столбца «Дата». Нажать ОК.

Рисунок 5.51

3. Промежуточные итоги. Выполнить команду ДанныеÞСтруктура ÞПромежуточные итоги. Откроется диалоговое окно (рис. 5.52). Установить в поле При каждом изменении в столбец «Имя», в поле Операция - сумма, в поле Добавить итоги по - столбец «Цена», нажать ОК. Повторить команду ДанныеÞСтруктура ÞПромежуточные итоги. В поле Операция выбрать «среднее», отменить флажок на опции «Заменить текущие итоги» и нажать ОК.

Рисунок 5.52 – Окно параметров промежуточных итогов

4. Сделать самостоятельно сортировку и подсчитать промежуточные итоги по Категории и Магазину.

5. Щелкнув левой клавишей мыши на имени столбца, выделить столбец «Магазин», захватить его мышкой и перетащить на столбец А.

6. Добавим столбец, содержащий количество проданных фильмов: щелкнуть правой клавишей мыши на имени столбца «Категория», выбрать команду Вставить. Ввести название нового столбца – «Кол-во». Заполнить этот столбец числами в пределах 20. В столбце Н ввести название «Продажа». Этот столбец заполнить формулами, выполняющими действие Кол-во*Цену. Примерный вид новой таблицы показан на рисунке 5.53.

Рисунок 5.53 – Данные для добавления

 

7. Ввод данных и просмотр с помощью формы. Достать на панель быстрого доступа кнопку Форма (Кнопка Office Þ Параметры Þ Настройка Þ Все команды). Отсортировать таблицу по категории «Магазин». Открыть с помощью соответствующей кнопки окно работы с формой (рис.5.54).

 

Рисунок 5.54 - Окно Форма

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

Перемещать курсор между полями мышкой или клавишей Tab. После ввода закрыть окно формы и просмотреть, добавились ли ваши записи в таблицу.

8. Выборка по критериям.Выполнить команду Данные Þ Фильтр. Обратите внимание, что справа от названий столбцов вашей таблицы появилась кнопка для раскрытия списка. Откройте список в столбце «Имя» и установите выборку по «Ольге», выбрав ее из списка. Выборка должна иметь следующий вид (рис. 5.55):

 

Рисунок 5.55 – Таблица с автофильтром

Выведите выборку на печать. Отменить выборку, можно командой ДанныеÞОчистить.

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

Выборка по условию: из списка критериев столбца «Кол-во» выберите «Числовые фильтры» и введите условие >5. Выведите на печать. Из списка критериев столбца «Дата» выберите критерий «Фильтры по дате» и введите условие >=10.янв.06 и <=12.янв.06. Выведите на печать. Очистите выборку.

 

Самостоятельное задание

 

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

Задание 1.

Выполнить несколько раз сортировку данных таблицы 5.5 в соответствии со следующими признаками - в алфавитном порядке фамилий покупателей, по убыванию суммы сделки, по возрастанию даты сделки, по совокупности признаков (фамилия, дата, сумма).

Методика выполнения работы

1. Откройте новую рабочую книгу и сохраните ее под именем «Сортировка» в своей рабочей папке.

2. Создайте таблицу представленную на рисунке 5.56.

 

Рисунок 5.56 – Начальная таблица с данными

3. Задайте параметры форматирования для таблицы.

Шрифт Times New Roman, размер шрифта 12 пт., для заголовков начертание полужирное и выравнивание по центру, перенос по словам, заливка серым цветом; для основной части. Напоминаем, что команды форматирования доступны на ленте Главная Þ Ячейки.

4. Для выполнения сортировки по полю фамилия покупателя поставьте курсор в любое место этого столбца и выполните команду Данные Þ Сортировка (рис. 5.51).

В открывшемся диалоговом окне в поле Сортировать по выберите «Фамилия покупателя». По возрастанию.

5. Повторите все шаги пункта 4 и задайте сортировку по «Сумме сделки», по убыванию.

6. Выполните повторно сортировку по полю «Дата сделки», по возрастанию.

7. Скопируйте таблицу на новый лист и на нем выполните сортировку по совокупности признаков. Для этого вызовите команду Данные Þ Сортировка. Установите Сортировать по фамилии в порядке возрастания, Затем по дате в порядке возрастания, В последнюю очередь, по сумме в порядке убывания.

8. С помощью команды Переименовать присвойте имена этим двум листам.

Задание 2. Осуществите выборку информации из списка на основе команды Автофильтр.

Методика выполнения работы.

1. На листе 4 создайте таблицу и заполните ее сведениями из таблицы 5.5.

2. Переименуйте Лист4, присвоив ему имя «Автофильтр №1».

3. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные ÞФильтр. Рядом с названиями граф таблицы появятся стрелки направленные вниз, которые раскрывают список возможных значений. В графе «Пол» выберите «М» Скопируйте таблицу на лист 5 и переименуйте его в «Автофильтр №2».

4. На листе «Автофильтр №1» в графе «Пол» откройте список фильтрования и выберите «Все». Затем в графе «Дата рождения» выберите в списке фильтрования «Условие» и задайте условие (рис.5.57):

 

 

Таблица 5.5

Фамилия	Имя	Дата приема на работу	Дата рождения	Пол	Оклад	Возраст
Пашков	Игорь	16.05.74	15.03.49	М
Андреева	Анна	16.01.93	19.10.66	Ж
Ерохин	Владимир	23.10.81	24.04.51	М
Попов	Алексей	02.05.84	07.10.56	М
Тюньков	Владимир	03.11.88	19.07.41	М
Ноткин	Евгений	27.08.85	17.08.60	М
Кубрина	Марина	20.04.93	26.06.61	Ж
Гудков	Никита	18.03.98	05.04.58	М
Горбатов	Михаил	09.08.99	15.09.52	М
Быстров	Алексей	06.12.00	08.10.47	М
Крылова	Татьяна	28.12.93	22.03.68	Ж
Бершева	Ольга	14.12.01	22.12.74	Ж
Русанова	Надежда	24.05.87	22.01.54	Ж

 

Рисунок 5.57 – Задание условий фильтрации

 

1. Скопируйте отфильтрованную таблицу на лист 6 и переименуйте его в «Автофильтр №3. На листе «Автофильтр №1» отмените выборку.

2. В столбце «Фамилия» выберите в списке фильтрации «Условие» и задайте условие на отбор всех сотрудников, чья фамилия начинается на «Б» (рис.5.58).

 

Рисунок 5.58 – Пользовательский фильтр

 

3. Скопируйте отфильтрованный список на лист 7 переименуйте его в «Автофильтр №4».

4. На листе «Автофильтр №1» для графы «Фамилия» задайте «Все», а в графе «Оклад» задайте «Первые 10…» где в диалоговом окне введите «Показать 5 наибольших элементов списка».

5. Сохраните файл.

Задание 3. Выполните отбор записей, из списка используя команду Расширенный фильтр.

 

Методика выполнения работы.

1. Перейдите на лист 8 и переименуйте его в «Расширенный фильтр».

2. Скопируйте на этот лист таблицу из предыдущего задания (табл. 5.5), вставьте ее, начиная со строки 7. Первые 6 строк отводятся для задания условий.

3. Создадим диапазон условий. Предположим, нам требуется отобрать фамилии сотрудников, которые получают больше 5000 руб. Или чей возраст превышает 50 лет. Заполните условия, как показано на рисунке 5.59.

Рисунок 5.59 – Условия для расширенного фильтра

 

4. Выполните команду Данные Þ Дополнительно. Заполните диалоговое окно следующим образом (рис. 5.60):

Рисунок 5.60 – Окно параметров расширенного фильтра

 

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

5. Предположим нам требуется вывести только тех сотрудников, фамилии которых начинаются с букв А, Г или Н. Заполните диапазон условий (рисунок 5.61).

Рисунок 5.61 – Условия для расширенного фильтра

 

6. Выполните команду ДанныеÞДополнительно и заполните диалоговое окно (рисунок 5.62).

Рисунок 5.62 – Окно параметров расширенного фильтра

 

Просмотрите результаты отбора записей.

1. Выведите список всех сотрудников, заработная плата которых больше среднего уровня. Перед созданием этого фильтра введите в ячейку H2 формулу =СРЗНАЧ(F8:F20) для вычисления среднего оклада.

2. Затем в ячейку А2 вводим вычисляемое условие =F8>$H$2, ссылающееся на ячейку H2 (рисунки 5.63 и 5.64).

Рисунок 5.63 – Условия для расширенного фильтра

Рисунок 5.64 – Параметры расширенного фильтра

 

Просмотрите результаты.

Самостоятельное задание

Выполнить самостоятельно следующие задания:

1. вывести всех мужчин, чей возраст не превышает 50 лет; а оклад менее 5000 руб.

2. вывести список сотрудников, в который включить всех женщин старше 50 лет и всех мужчин старше 60 лет;

3. вывести список сотрудников, которые проработали не более 10 лет.

Задание 1

1. Для студентов планируется поездка во Францию, но поедут только студенты, у которых средний балл более или равен «4», нет неудовлетворительных оценок и по иностранному языку «5». Создать функцию автоматического определения претендентов на поездку.

2. Подсчитать в каждой группе количество «5», «4», «3» и «2».

3. Определить «Худшую группу» по максимальному количеству «2» и «Лучшую группу» по максимальному количеству «5». При выполнении вычислений применять операцию «Присвоение имени блоку ячеек».

Методика выполнения работы

1. На новом листе рабочей книги создайте таблицу по образцу таблицы 5.6.

Таблица 5.6

2. В столбец «Ср. балл» введите формулу, для этого воспользуйтесь кнопкой Вставить функцию в строке ввода и редактирования формул или командой Формулы Þ Библиотека функций.

Выберите категорию функций Статистическая и функцию СРЗНАЧ.

В открывшемся диалоговом окне введите диапазон ячеек С5:Е5. Щелкните ОК и скопируйте формулу вниз до ячейки F24.

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

=ЕСЛИ(И(F5>=4;E5=5;C5<>2;D5<>2;E5<>2);"Едет";"Не подходит")

Скопируйте формулу до ячейки G24.

4. Для подсчета количества человек, которые едут во Францию, а также количества различных оценок создайте на листе таблицу в соответствии с таблицей 5.7.

Функция СЧЕТЕСЛИ находится в категории Статистические.

1. В ячейку В27 введите формулу =СЧЁТЕСЛИ(G6:G25;"Не подходит")

Количество полученных оценок определенного вида будем подсчитывать в ячейках С30:F33, используя уже знакомую нам функцию СЧЕТЕСЛИ. Введем в С30 формулу =СЧЁТЕСЛИ($C$5:$E$8;B30). Абсолютные ссылки (знак $) здесь применяются для удобного последующего копирования. Блок ячеек охватывает все оценки группы Б124, а ячейка В30 указывает на то, что подсчитываются оценки «отлично».

Таблица 5.7

2. Введите формулу подсчета количества студентов подходящих для поездки в ячейку В26

=СЧЁТЕСЛИ(G5:G24;"Едет")

3. Аналогично введите формулы подсчета «5» для других групп в ячейках D30:F30. Затем скопируйте ячейки С30:F30 вниз до 33 строки. Формула должна автоматически настроиться под другие диапазоны ячеек.

4. Присвойте имена блокам ячеек С33:F33 и С30:F30, содержащим количество двоек и пятерок по группам соответственно, как «Двойки» и «Пятерки». Для этого блок ячеек предварительно выделяется затем выполняется команда Формулы Þ Определенные имена Þ Присвоить имя.

5. В ячейку D35 введите формулу

=ПРОСМОТР(МАКС(Двойки);Двойки;C29:F29)

Функция МАКС находится в категории Статистические, а функция ПРОСМОТР в категории Ссылки и массивы.

 

С помощью справки изучите принцип работы функции

ПРОСМОТР! Ответь на вопрос: для чего в функции используется блок ячеек С29:F29?

 

6. В ячейку D36 аналогично пункту 9 введите формулу для определения лучшей группы по количеству пятерок.

Задание 2

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

Методика выполнения задания

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

2. На новом листе создайте таблицу начиная с ячейки А1 (табл. 5.8).

3. В шапке таблицы для надписей установите выравнивание по горизонтали и вертикали «по центру», а для 5-8 столбцов измените направление текста. Все эти действия можно сделать с помощью команды ГлавнаяÞ Выравнивание.

4. Создайте таблицу интервалов начиная с ячейки В12 (табл. 5.9). Обратите внимание, что ячейки В12 и С12 объединенные.

5. В ячейку D2 введем формулу, которая будет устанавливать в ней 1, если премия попадает в Интервал 1, в противном случае 0. Выберите категорию функций Логические, функция ЕСЛИ.

 

Таблица 5.8

№ п/п	ФИО	Премия	Интервал 1	Интервал 2	Интервал 3	Интервал 4	Интервал 5
	Пашков
	Андреев
	Ерохин
	Попов
	Тюньков
	Ноткин
	Кубрина
	Гудков
ИТОГО:

Таблица 5.9

	Интервалы премии
1ин
2ин
3ин
4ин
5ин

 

6. Не выходя из диалогового окна функции, щелкните на меню Имя в строки редактирования и ввода формул.

 

 

7. В открывшемся списке выберите Другие функции, а затем Логические, функция И (рис. 5.65).

 

Рисунок 5.65 – Создание вложенных функций

 

 

 

Рисунок 5.66 – Создание вложенной логической функции И

 

8. В диалоговом окне в поле Логическое_значение1 сделайте проверку нижней границы интервала 1. Обратите внимание на использование абсолютных ссылкой для более удобного последующего копирования, для ввода знака $ можно воспользоваться клавишей F4. Наберите С2>=$B$13. В поле Логическое_значение2 проверьте верхнюю границу С2<$C$13. Не выходя из диалогового окна функции, установите курсор в строку ввода и редактирования формул (рис. 5.66) между двумя крайними правыми закрывающими скобками для продолжения функции ЕСЛИ.

 

Рисунок 5.67 – Итоговая таблица

 

9. Введите с клавиатуры продолжение формулы и нажмите клавишу Enter.

10. Формулу из ячейки D2 операцией автозаполнения скопировать по ст