Лаба№2 excel

Обработка данных средствами электронных таблиц

Содержание отчета

Введение (цель + задача + вариант);

Цель работы: изучить основные принципы работы с электронными таблицами при использовании редактора MS Excel.

Выводы и ответы на все вопросы;

Заключение (Ответ на вопрос, почему программа EXCEL называется электронной таблице).

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

1.Запустите программу Excel (Пуск > Программы > Microsoft Excel).

2.Создайте (Файл > Создать) новую рабочую книгу. Сохраните ее в вашей папке (Файл > Сохранить как) под именем knigaZ.xls. (где Z – номер варианта)

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

4.Сделайте текущей ячейку А1 и введите в нее текст: Месяцы.

5.Сделайте текущей ячейку В1 и введите в нее текст: Расходы.

6.Сделайте текущей ячейку А2. Введите в нее текст Январь 2001. Нажмите клавишу ENTER. Убедитесь, что текст был автоматически распознан как дата (изменился формат, а данные выровнялись по правому краю ячейки). Нажмите клавишу ВВЕРХ. При желании, дайте команду Формат > Ячейки и укажите иной формат записи даты. Какие существуют форматы ?

7.Установите указатель мыши на маркер заполнения в правом нижнем углу рамки текущей ячейки. Нажмите правую кнопку мыши и протяните рамку так, чтобы она охватила все ячейки от А2 до А25.

8.Отпустите кнопку мыши. В открывшемся меню выберите пункт Заполнить по месяцам. Убедитесь, что в столбце А появились обозначения для всех месяцев по декабрь 2002 года включительно.

9.В данном примере будем считать, что в первый месяц расходы составляли 10 рублей, а в каждом последующем возрастали на ± t*10%. Сделайте текущей ячейку В2. Введите в нее число

10.Нажмите клавишу ENTER.

10.Щелкните на ячейке В2 правой кнопкой мыши и выберите в контекстном меню пункт Формат ячеек. На вкладке Число выберите вариант Денежный и щелкните на кнопке ОК. Убедитесь, что число теперь записано как денежная сумма.

11.Щелкните правой кнопкой мыши на маркере заполнения и протяните рамку так, чтобы она охватила ячейки с В2 по В25. Отпустите кнопку мыши. В открывшемся меню выберите пункт Прогрессия. Что происходит если будем нажимать на левую кнопку мыши ?

12.На панели Тип установите переключатель Геометрическая, в поле Шаг задайте значение - число p. Щелкните на кнопке ОК.

13.В ячейку С1 введите текст расходы №2. Первые расходы увеличиваются с января 2001 по февраля 2002 на S рубл. и с марта 2002 до конца на D рубл. Определите расходы №2. для этого, смотрите следующий пункт.

14.Сделайте текущей ячейку С2. Введите в нее текст =В2+S (где S – значение из вашего варианта) и нажмите Enter. Это формула, которая означает, что значение ячейки 2 равно сумме значению ячейки В2 и цифры S. Щелкните на ячейке С2 и нажмите на пиктограмму “копировать” или через меню (Правка - копировать). Потом выделить ячейки с С3 по M и нажать на “Вставить”. Эти ячейки автоматически заполняются значениями. Введите формулу =N+D (например =B16+10) в ячейке R (например С16); после этого нажмите Enter. С помощью

Лабораторная работа №2 по информатике стр.2/6 ВлГУ-ИЗИ

метода “копировать – вставить ” определите значение ячейки W (например C17 – С25. (С25= 99,54р.)). Посмотрите, какая формула в каждой ячейке записана.

15.Какая разница существует между первым (пункт 11) и вторым методом (пункт 14)?

16.Сохраните рабочую книгу knigaZ.xls.

17.Сделайте выводы.

18.Сделайте текущей ячейку D1. Введите в нее текст: Общие расходы. Это содержит результат суммы расхода №1 и расхода №2.

19.Сделайте текущей ячейку D2. Введите в нее текст =B2+C2 и нажмите Enter. Таким образом получаем в ячейке D2 суммы (25,00р.). С помощью метода “копировать – вставить ” определите значение ячейки D3-D25.

20.Для отмены действия, нажмите клавишу Esc.

21.Можно также с другими методами получить это результат. Например: Сделайте текущей ячейку E2. Нажать на пиктограмму (кнопку) “Автосумма” на стандартной панели инструментов или через меню (Вставка – Функция), найти эту пиктограмму. Потом, убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Если нет, то укажите диапазон (B2 и C2). Диапазон можно указать с помощью клавиши Shift+(стрелки) или с помощью мышки (методом протягивания). Нажмите клавишу ENTER после этого действия. Таким образом, получаем тоже в ячейке E2 суммы (25,00р.). С помощью метода “копировать – вставить ” определите значение ячейки E3-E25. В ячейку E1 введите текст: копия D.

22.Какой вывод вы можете сделать?

23.Сделайте текущей ячейку A27. Введите в нее текст: “Итого:” и нажмите Enter.

24.Сделайте текущей ячейку B27. Щелкните на кнопке Автосумма на стандартной панели инструментов. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек (B2-B25) для суммирования. Нажмите клавишу ENTER.

25.Используйте метод “копировать – вставить ” для определения значения ячейки С27, D27 и E27. Каким образом еще можно определить их значения?

26.Сделайте текущей ячейку A29. Введите в нее текст: “Среднее значение:” и нажмите Enter. Сделайте текущей ячейку B29. Щелкните на кнопке Вставка функции на стандартной панели инструментов. В списке Категория выберите пункт Статистические. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК. После этого вы получите, например значение: (70,80р.). Обратите внимание на то, что для каждой функции можно получить краткое описание. Определите также среднее значение С29, D29 и E29.

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

28.Используя порядок действий, описанный выше, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ). Напишите результат в ячейки: B31, C32 и E33 соответственно; и в A31, A32 и A33 их надписи (минимальное число, максимальное число и количество элементов в наборе).

29.Выделите весь наборы данных (A1 до E32). Выберите пиктограмм “…границы…” все границы, … и нарисуете все границы вокруг данных так чтобы выгладил как на Рис 1.

30.Сохраните рабочую книгу knigaZ.xls в отчете.

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

31. Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте рабочую книгу knigaZ.xls.

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

33.В ячейку А1 введите текст Прейскурант и нажмите клавишу ENTER.

34.В ячейку А2 введите текст Курс пересчета и нажмите клавишу ENTER. В ячейку В2 введите текст 1 у.е.= и нажмите клавишу ENTER. В ячейку C2 введите текущий курс пересчета (у преподавателя) и нажмите клавишу ENTER.

35.В ячейку A3 введите текст Наименование товара и нажмите клавишу ENTER. В ячейку ВЗ введите текст Цена (у. е.) и нажмите клавишу ENTER. В ячейку СЗ введите текст Цена (руб.) и нажмите клавишу ENTER.

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

37.В ячейки столбца В введите цены товаров в условных единицах.

38.В ячейку С4 введите формулу: =B4*$C$2, которая используется для пересчета цены из условных единиц в рубли. Чем отличается “ =B4*$C$2 ” от “ =B4*C2 ” или от этого “=B4*$C2 ”?

39.Методом автозаполнения скопируйте формулы во все ячейки столбца C, которым соответствуют заполненные ячейки столбцов А и В. Почему при таком копировании получаются верные формулы?

40.Измените курс пересчета в ячейке С2 (текущий курс + 1). Обратите внимание, что все цены в рублях при этом обновляются автоматически.

41.Выделите методом протягивания диапазон А1:С1 и дайте команду «Формат» Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру и установите флажок Объединение ячеек.

42.На вкладке Шрифт задайте размер шрифта в 14 пунктов и в списке Начертание выберите вариант Полужирный. Щелкните на кнопке ОК.

43.Щелкните правой кнопкой мыши на ячейке В2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По правому краю и щелкните на кнопке ОК.

44.Щелкните правой кнопкой мыши на ячейке С2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому краю и щелкните на кнопке ОК.

45.Выделите методом протягивания диапазон В2:С2. Щелкните на раскрывающей кнопке рядом с кнопкой Границы на панели инструментов Форматирование и задайте для этих ячеек широкую внешнюю рамку (кнопка в правом нижнем углу открывшейся палитры).

46.Дважды щелкните на границе между заголовками столбцов А и В, В и С, С и D. Обратите внимание, как при этом изменяется ширина столбцов А, В и С.

47.Сохраните рабочую книгу knigaZ.xls.

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

48.Выберите рабочий лист Прейскурант, созданный в предыдущем упражнении.

49.Убедитесь, что внешний вид документа вас устраивает, и щелкните на кнопке Предварительный просмотр на стандартной панели инструментов, чтобы увидеть, как документ будет выглядеть при печати.

50.Щелкните на кнопке Масштаб, чтобы увидеть изображение страницы в натуральную величину.

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

52.Щелкните на кнопке Страница, чтобы выбрать параметры страницы. В диалоговом окне Параметры страницы выберите вкладку Колонтитулы.

53.В списке Нижний колонтитул выберите вариант: Страница 1 из ?

54.Щелкните на кнопке Создать верхний колонтитул. В открывшемся диалоговом окне сформируйте верхний колонтитул по своему усмотрению.

55.Измените шрифт, воспользовавшись кнопкой Шрифт. Включите в колонтитул имя рабочего листа, щелкнув на кнопке Имя листа. Щелкните на кнопке ОК.

56.Посмотрите, как выглядит страница с настроенными колонтитулами.

57.Щелкните на кнопке Разметка страницы, чтобы вернуться к обычному режиму просмотра рабочего листа, но с разбиением на страницы.

58.Воспользуйтесь командой Вставка > Разрыв страницы, чтобы задать принудительное разделение рабочего листа на страницы печати.

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

60.Щелкните на кнопке Печать, чтобы распечатать рабочий лист, если есть принтер. Что необходимо знать для того, что выводить на принтере текст ? Если ли разница с программой MSWord?

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

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

61.Откройте рабочую книгу knigaZ.xls, созданную ранее.

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

63.В первую строку рабочего листа, начиная с ячейки В1, введите названия экзаменов. (См. ваш вариант)

64.В первый столбец рабочего листа, начиная с ячейки А2, введите фамилии студентов (12 студентов).

65.Заполните таблицу экзаменационными оценками по своему усмотрению. Оценки должны изменяться от 2 до 5 баллов.

66.Выделите ячейку А2 и дайте команду Формат» Условное форматирование.

67.В раскрывающемся списке на панели Условие 1 выберите вариант формула.

68.В поле для формулы введите следующую формулу: =МИН($В2:$Е2)>3. Обратите внимание на способ использования абсолютных и относительных ссылок в формуле, так как ее планируется распространить на всю таблицу. Эта формула рассчитана на четыре экзамена, при другом их числе выбранный диапазон несколько изменится.

69.Щелкните на кнопке Формат. В открывшемся диалоговом окне Формат ячеек выберите вкладку Вид и щелкните на светло-зеленом цвете для его использования в качестве фона ячеек. Щелкните на кнопке ОК.

70.Щелкните на кнопке А также, чтобы задать второе условие форматирования. Далее действуйте аналогично тому, как указано в п. 68. Нужная формула должна иметь вид:

=МИН($В2:$Е2)<3.

71.Подобно тому, как указано в п. 69, задайте светло-розовый фон для ячеек.

72.Щелкните на кнопке ОК. Фон ячейки А2 должен измениться, если соответствующий студент не имеет троек или, напротив, имеет задолженность.

73.Выделите весь диапазон ячеек ведомости и дайте команду Формат > Условное форматирование. Диалоговое окно Условное форматирование должно содержать настройки, подготовленные для ячейки А2.

74.Щелкните на кнопке ОК. Условное форматирование распространится на всю выделенную область с автоматической коррекцией относительных ссылок. Убедитесь, что формат ведомости соответствует тому, что требовалось.

75.Сохраните рабочую книгу knigaZ.xls.

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

76.Откройте рабочую книгу knigaZ.xls, созданную ранее.

77.Откройте рабочий лист Дополнительные расходы по месяцам.

78.Методом протягивания выделите диапазон ячеек A2:D25.

79.Щелкните на значке Мастер диаграмм на стандартной панели инструментов.

80.В списке Тип выберите пункт Гистограмма (для отображения данных в виде столбчатой диаграммы). В палитре Вид выберите нижний пункт в первом столбце (трехмерная гистограмма). Щелкните на кнопке Далее.

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

82.На вкладке Ряд выберите пункт Ряд1, щелкните в поле Имя, а затем на ячейке B1. Аналогично, выберите пункт Ряд2 и щелкните сначала в поле Имя, а затем на ячейке С1 и патом Ряд3 D1. Можно также проверить если значения соответствуют того диапазон, который мы хотели бы. Например для первого ряда диапазон - =Лист2!$B$2:$B$25 т.е. (B2-B25). Щелкните на кнопке Далее.

83.Выберите вкладку Заголовки. Задайте заголовок диаграммы, введя в поле Название диаграммы текст Диаграмма расходов. Не забудете также проверить значения оси X. Для всех ряд они должны быть те же самые. Щелкните на кнопке Далее если все нормально.

84.Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово.

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

86.Щелкните на одном из элементов ряда Нарастающий итог. Убедитесь, что весь ряд выделен.

87.Дайте команду Формат > Выделенный ряд. Откройте вкладку Вид.

88.Щелкните на кнопке Способы заливки. Установите переключатель Заготовка, в раскрывающемся списке выберите пункт Океан, задайте тип штриховки диагональная 1. Щелкните на кнопке ОК и еще раз на кнопке ОК. Посмотрите, как изменился вид ряда данных.

89.По своему усмотрению измените оформление ряда данных Расходы и других элементов диаграммы (обязательно пункт).

90.Сохраните рабочую книгу knigaZ.xls.

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

91.Откройте рабочую книгу knigaZ.xls, созданную ранее.

92.Дважды щелкните на ярлычке лист 3 и присвойте ему имя Сведения о поставках.

93.Предполагается, что предприятие получает, пять видов материалов: бумагу, фанеру, картон, полиэтилен и ткань — от пяти поставщиков, находящихся в Братске, Казани, Курске, Мурманске и Череповце. Каждый из поставщиков может поставлять любой вид материалов. Поставки производятся не чаще раза в месяц, единица измерения — тонна.

94.В ячейки A1-D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.

95.Введите несколько десятков записей, имеющих описанную выше структуру. Реальные «объемы поставки» значения не имеют.

96.Примечание. Преподаватель может заранее подготовить стандартную базу данных, содержащую «сведения о поставках». В этом случае ее можно скопировать из заранее подготовленной рабочей книги. Чтобы скопировать рабочий лист из одной книги в другую, надо открыть обе эти книги, щелкнуть на ярлычке копируемого листа правой кнопкой мыши и выбрать в контекстном меню пункт Переместить/скопировать. В открывшемся диалоговом окне книга-приемник выбирается в раскрывающемся списке, далее надо указать место нового листа среди уже существующих, установить флажок Создавать копию и щелкнуть на кнопке ОК.

97.Общая сортировка базы данных. Сделайте текущей любую ячейку базы данных и дайте команду Данные > Сортировка. Убедитесь, что при этом выделяется вся (кроме заголовков полей) база данных.

98.В списке Сортировать по выберите пункт Месяц и режим по возрастанию. В списке Затем по (вторичная сортировка) выберите пункт Поставщик и режим по возрастанию.

99.В списке В последнюю очередь, по выберите пункт Товар и режим по возрастанию. Щелкните на кнопке ОК.

100.Убедитесь, что база данных отсортирована по указанным критериям.

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

102.Выберите любую ячейку в столбце Товар и щелкните на кнопке Сортировка по возрастанию на панели инструментов.

103.Выберите любую ячейку в столбце Поставщик и щелкните на кнопке Сортировка по возрастанию на панели инструментов.

104.Выберите любую ячейку в столбце Месяц и щелкните на кнопке Сортировка по возрастанию на панели инструментов. Убедитесь, что итоговый порядок сортировки тот же, что и в предыдущем случае. Обратите внимание, что в этом случае мы сначала провели третичную сортировку, затем вторичную и на последнем этапе первичную.

105.Фильтрация данных. Чтобы включить режим фильтрации, дайте команду Данные > Фильтр > Автофильтр. Обратите внимание на появление раскрывающих кнопок у заголовков полей базы данных.

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

107.Чтобы отобрать наиболее крупные разовые поставки, щелкните на раскрывающей стрелке у поля Объем и выберите в списке вариант Первые 10.

108.Выберите с помощью счетчика число 20 и далее пункт наибольших и вариант % от количества элементов. Щелкните на кнопке ОК. В результате будет отобрано 20% записей, содержащих наибольшие значения объема поставок.

109.Чтобы отменить режим фильтрации записей, еще раз дайте команду Данные > Фильтр > Автофильтр.

110.Защищаете проделанную работу у преподавателя.