Настройка Excel

• Название: Настройка Excel
• Дата: 23.12.2021
• Размер: 1.21 Mb.
• Имя файла: MS Excel.docx
• Тип: Практическая работа; #315484
• страница: 3 из 4

Тема. Построение и форматирование диаграмм в MS Excel.

Цель. Приобрести и закрепить практические навыки по применению Мастера диаграмм.

Задание 1. Создать и заполнить таблицу продаж, показанную на рисунке.

	A	B	C	D	E
1	Продажа автомобилей ВАЗ
2	Модель	Квартал 1	Квартал 2	Квартал 3	Квартал 4
3	ВАЗ 2101	3130	3020	2910	2800
4	ВАЗ 2102	2480	2100	1720	1340
5	ВАЗ 2103	1760	1760	1760	1760
6	ВАЗ 2104	1040	1040	1040	1040
7	ВАЗ 2105	320	320	320	320
8	ВАЗ 2106	4200	4150	4100	4050
9	ВАЗ 2107	6215	6150	6085	6020
10	ВАЗ 2108	8230	8150	8070	7990
11	ВАЗ 2109	10245	10150	10055	9960
12	ВАЗ 2110	12260	12150	12040	11930
13	ВАЗ 2111	14275	14150	14025	13900

Алгоритм выполнения задания.

1. 
   Записать исходные значения таблицы, указанные на рисунке.
   
2. 
   Заполнить графу Модель значениями ВАЗ2101÷2111, используя операцию Автозаполнение.
   
3. 
   Построить диаграмму по всем продажам всех автомобилей, для этого:
   
   1. 
      Выделить всю таблицу (диапазоеА1:Е13).
      
   2. 
      Щёлкнуть Кнопку Мастер диаграмм на панели инструментов Стандартная или выполнить команду Вставка/Диаграмма.
      
   3. 
      В диалоговом окне Тип диаграммы выбрать Тип Гистограммы и Вид 1, щёлкнуть кнопку Далее.
      
   4. 
      В диалоговом окне Мастер Диаграмм: Источник данных диаграммы посмотреть на образец диаграммы, щёлкнуть кнопку Далее.
      
   5. 
      В диалоговом окне Мастер Диаграмм: Параметры диаграммы ввести в поле Название диаграммы текст Продажа автомобилей, щёлкнуть кнопку Далее.
      
   6. 
      В диалоговом окне Мастер Диаграмм: Размещение диаграммы установить переключатель «отдельном», чтобы получить диаграмму большего размера на отдельном листе, щёлкнуть кнопку Готово.
      
4. 
   Изменить фон диаграммы:
   
   1. 
      Щёлкнуть правой кнопкой мыши по серому фону диаграммы (не попадая на сетку линий и на другие объекты диаграммы).
      
   2. 
      В появившемся контекстном меню выбрать пункт Формат области построения.
      
   3. 
      В диалоговом окне Формат области построения выбрать цвет фона, например, бледно-голубой, щёлкнув по соответствующему образцу цвета.
      
   4. 
      Щёлкнуть на кнопке Способы заливки.
      
   5. 
      В диалоговом окне Заливка установить переключатель «два цвета», выбрать из списка Цвет2 бледно-жёлтый цвет, проверить установку Типа штриховки «горизонтальная», щёлкнуть ОК, ОК.
      
   6. 
      Повторить пункты 4.1-4.5, выбирая другие сочетания цветов и способов заливки.
      
5. 
   Отформатировать Легенду диаграммы (надписи с пояснениями).
   
   1. 
      Щёлкнуть левой кнопкой мыши по области Легенды (внутри прямоугольника с надписями), на её рамке появятся маркеры выделения.
      
   2. 
      С нажатой левой кнопкой передвинуть область Легенды на свободное место на фоне диаграммы.
      
   3. 
      Увеличить размер шрифта Легенды, для этого:
      
      1. 
         Щёлкнуть правой кнопкой мыши внутри области Легенды.
         
      2. 
         Выбрать в контекстном меню пункт Формат легенды.
         
      3. 
         На вкладке Шрифт выбрать размер шрифта 16, на вкладке Вид выбрать желаемый цвет фона Легенды, ОК.
         
   4. 
      Увеличить размер области Легенды, для этого подвести указатель мыши к маркерам выделения области Легенды, указатель примет вид ↔ двунаправленной стрелки, с нажатой левой кнопкой раздвинуть область.
      
   5. 
      Увеличить размер шрифта и фон заголовка Продажа автомобилей аналогично п.5.3.
      
6. 
   Добавить подписи осей диаграммы.
   
   1. 
      Щёлкнуть правой кнопкой мыши по фону диаграммы, выбрать пункт Параметры диаграммы, вкладку Заголовки.
      
   2. 
      Щёлкнуть левой кнопкой мыши в поле Ось Х (категорий), набрать Тип автомобилей.
      
   3. 
      Щёлкнуть левой кнопкой мыши в поле Ось Y (значений), набрать Количество, шт.
      

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

Фильтрация данных может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.

Для использования автофильтра нужно:

1. 
   установить курсор внутри таблицы;
   
2. 
   выбрать команду Данные - Фильтр - Автофильтр;
   
3. 
   раскрыть список столбца, по которому будет производиться выборка;
   
4. 
   выбрать значение или условие и задать критерий выборки в диалоговом окне
   

Пользовательский автофильтр.

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

Для отмены режима фильтрации нужно установить курсор внутри таблицы и повторно выбрать команду меню Данные - Фильтр - Автофильтр (снять флажок).

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

Создайте таблицу в соответствие с образцом, приведенным на рисунке. Сохраните ее под именем Sort.xls.



Технология выполнения задания:

1. 
   Откройте документ Sort.xls
   
2. 
   Установите курсор-рамку внутри таблицы данных.
   
3. 
   Выполните команду меню Данные - Сортировка.
   
4. 
   Выберите первый ключ сортировки: в раскрывающемся списке "сортировать" выберите "Отдел" и установите переключатель в положение "По возрастанию" (Все отделы в таблице расположатся по алфавиту).
   
5. 
   Если же хотите, чтобы внутри отдела товары расположились по алфавиту, то выберите второй ключ сортировки в раскрывающемся списке "Затем" выберите "Наименование товара" и установите переключатель в положение "По возрастанию".
   

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

6. 
   Установите курсор-рамку внутри таблицы данных.
   
7. 
   Выполните команду меню Данные - Фильтр - Автофильтр.
   
8. 
   Снимите выделение в таблицы.
   

9. 
   У каждой ячейки заголовка таблицы появилась кнопка "Стрелка вниз", она не выводится на печать, позволяющая задать критерий фильтра. Мы хотим оставить все записи с ненулевым остатком.
   
10. 
    Щ елкните по кнопке со стрелкой, появившейся в столбце Количество остатка. Раскроется список, по которому будет производиться выборка. Выберите строку Условие. Задайте условие: > 0. Нажмите ОК. Данные в таблице будут отфильтрованы.
    

11. 
    Вместо полного списка товаров, мы получим список проданных на сегодняшний день товаров.
    

12. 
    Фильтр можно усилить. Если дополнительно выбрать какой-нибудь отдел, то можно получить список неподанных товаров по отделу.
    
13. 
    Для того, чтобы снова увидеть перечень всех непроданных товаров по всем отделам, нужно в списке "Отдел" выбрать критерий "Все".
    
14. 
    Можно временно скрыть остальные столбцы, для этого, выделите столбец "№", и в контекстном меню выберите Скрыть . Таким же образом скройте остальные столбцы, связанные с приходом, расходом и суммой остатка. Вместо команды контекстного меню можно воспользоваться командой Формат - Столбец - Скрыть.
    
15. 
    Чтобы не запутаться в своих отчетах, вставьте дату, которая будет автоматически меняться в соответствии с системным временем компьютера Вставка - Функция - Дата и время - Сегодня.
    

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

Практическая работа №9
«Моделирование в среде табличного процессора
MS Excel»


Задача. Моделирование биологических процессов (Биоритмов).
Цель моделирования: На основе анализа индивидуальных биоритмов прогнозировать неблагоприятные дни, выбирать благоприятные дни для разного рода деятельности.
Технология выполнения работы:

1. 
   Объединить первую строку в столбцах A, B, C, D и ввести текст: Моделирование биоритмов человека
   
2. 
   Объединить третью строку в столбцах A, B, C, D и ввести текст: Исходные данные. Объединить ячейки А4 и В4, ввести текст: Неуправляемые параметры (константы). Объединить ячейки С4, D4, ввести текст: Управляемые параметры.
   
3. 
   В ячейке А5 напечатать текст: Период физического цикла. В ячейке А6-текст: Период эмоционального цикла. В А7: Период интеллектуального цикла
   
4. 
   В ячейках В5, В6, В7 проставить соответственно числа: 23, 28, 33
   
5. 
   В ячейке C5 –текст: Дата рождения человека. В C6 – текст: Дата отсчета. В C7 – текст: Длительность прогноза
   
6. 
   Заполните ячейки D5, D6, D7 соответственно – свою дату рождения, дату отсчета - 1.10.04, длительность прогноза - 31
   
7. 
   Объединить ячейки А8, В8, C8, D8 и напечатать текст: Результаты
   
8. 
   В А9 – текст: Порядковый день. В В9 – текст: Физическое. В С9 – текст: Эмоциональное. В D9 – текст: Интеллектуальное.
   
9. 
   В ячейку А10 введите дату отсчета. Например: 1.10.04
   
10. 
    В ячейку В10 введите формулу: =SIN(2*ПИ()*(A10-$D$5)/23)
    
11. 
    В ячейку С10 введите формулу: = SIN(2*ПИ()*(A10-$D$5)/28)
    
12. 
    В ячейку D10 введите формулу: = SIN(2*ПИ()*(A10-$D$5)/33)
    
13. 
    Сохранить файл под именем Bio.xls
    

Задание для самостоятельной разработки: Построить модель физической, эмоциональной и интеллектуальной совместимости двух друзей.

Технология моделирования:

1. 
   Открыть созданный вами ранее файл bio.xls.
   
2. 
   Выделить ранее рассчитанные столбцы своих биоритмов, скопировать и вставить в столбцы E, F, G только значения.
   
3. 
   Ввести в ячейку D5 дату рождения друга. Модель пересчитается для новых данных. Это основное свойство электронной таблицы: изменение числового значения в ячейке приводит к мгновенному пересчету формул, содержащих имя этой ячейки.
   
4. 
   В столбцах H, I, J провести расчет суммарных биоритмов.
   

   	H	I	J
   9	Физическая сумма	Эмоциональная сумма	Интеллектуальная сумма
   10	=D10+E10	=C10+F10	=D1-+G10
   11	Заполнить вниз	Заполнить вниз	Заполнить вниз

5. 
   По столбцам H, I, J построить линейную диаграмму физической, эмоциональной и интеллектуальной совместимости. Максимальные значения по оси Y на диаграмме указывают на степень совместимости: если они превышают 1,5, то вы с другом в хорошем контакте.
   
6. 
   Открыть документ bio.doc.
   
7. 
   Перенести копию суммарной диаграммы в текстовый документ для дальнейшего оформления отчета.
   

Описать результаты анализа модели, ориентируясь на следующие вопросы:

1. 
   Что, на ваш взгляд, показывают суммарные графики одноименных биоритмов? Что можно по ним определить?
   
2. 
   Какая из трех кривых показывает наилучшую (наихудшую) совместимость с другом?
   
3. 
   Можно ли определить дни, когда вам с другом не стоит общаться? Что можно ожидать в эти дни?
   
4. 
   Выберите наиболее благоприятные дни для совместного участия с другом в командной игре. Ответ обоснуйте.
   

Практическая работа №10
«Сортировка данных в MS Excel»


Упражнение: Создание и заполнение бланка товарного счета (рис.1).

1-й этап. Создание таблицы бланка счета.
2-й этап. Заполнение таблицы.
3-й этап. Оформление бланка.
1-й этап.
Заключается в создании таблицы.
Основная задача - уместить таблицу по ширине листа:

1. 
   предварительно установите поля, размер и ориентацию бумаги Файл - Параметры страницы...;
   
2. 
   выполнив команду Сервис - Параметры..., во вкладке Вид в поле Параметры окна активизируйте переключатель Авторазбиение на страницы.
   

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

14. 
    
    

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

1. 
   Создайте таблицу по предлагаемому образцу с таким же числом строк и столбцов (рис. 2).
   
2. 
   Выровняйте и отформатируйте шрифт в ячейках-заголовках, подберите ширину столбцов, изменяя ее при помощи мыши.
   
3. 
   Введите нумерацию в первом столбце таблицы, воспользовавшись маркером заполнения.
   
4. 
   «Разлинуйте» таблицу, используя линии различной толщины. Обратите внимание на то, что в последней строке пять соседних ячеек не имеют внутреннего обрамления.
   
5. 
   На этом этапе желательно выполнить команду Файл - Предварительный просмотр, чтобы убедиться, что таблица целиком вмещается на листе по ширине и все линии обрамления на нужном месте.
   

15.