МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ по организации и методики проведения практических занятий по теме: ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL 2007

ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ПРОФЕССИОНАЛЬНОЕ

ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

«МИНЕРАЛОВОДСКИЙ РЕГИОНАЛЬНЫЙ МНОГОПРОФИЛЬНЫЙ КОЛЛЕДЖ»

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ

по организации и методики проведения практических занятий

по теме:

ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL

Дисциплина ОУД п.07. Информатика

для специальности:   

         08.02.01 Строительство и эксплуатация зданий и сооружений

         09.02.03 Программирование в компьютерных системах,

         123.02.03 Техническое обслуживание и ремонт автомобильного транспорта,

         23.02.04 Техническая эксплуатация подъемно-транспортных, строительных, дорожных машин и оборудования (по отраслям)

         02.10 Технология продукции общественного питания

МИНЕРАЛЬНЫЕ ВОДЫ, 2017

Рассмотрено и одобрено

на заседании МО ЕНД

Протокол №____ от «____»__________2017 г.

Председатель _____________

Константинова О.В.

Рассмотрено

на заседании методического совета

Протокол №___ от «___»_____2016г.

Председатель методического совета

______________

Автор: Кривенко Нелля Евгеньевна, преподаватель информатики высшей квалификационной категории ГБПОУ МРМК

Рецензент: Селютина О.Н, преподаватель спецдисциплин высшей квалификационной категории

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

Методические указания разработаны на основе рабочей программы учебной дисциплины ОУД п.07 «Информатика» для специальности:   

08.02.01 Строительство и эксплуатация зданий и сооружений

09.02.03 Программирование в компьютерных системах,

123.02.03 Техническое обслуживание и ремонт автомобильного транспорта,

23.02.04 Техническая эксплуатация подъемно-транспортных, строительных, дорожных машин и оборудования (по отраслям)

02.10 Технология продукции общественного питания

Учебная дисциплина ОУД п.07 «Информатика» относится к циклу общеобразовательных дисциплин и нацелена на развитие у обучающихся практических навыков работы с табличным процессором MICROSOFT EXCEL, различные способы редактирования и форматирования отдельных элементов таблиц, работать с базами данных и диаграммами, а так же применять дополнительные средства обработки табличных форм.

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

ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL

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

Практическая работа №1

 MS Excel. Типы данных, абсолютная и относительная адресация. Стандартные функции Excel.

Краткие теоретические сведения.

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

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

Для вставки нового листа необходимо выделить лист, перед которым надо вставить новый лист и Выбрать Вставка + Лист.

Для удаления листа необходимо его выделить и выбрать Правка + Удалить либо щелкнуть правой кнопкой мыши на ярлыке листа и выбрать команду «Удалить».

Маркер заполнения - это небольшой черный квадрат в углу выделенного диапазона. Попав на маркер заполнения, указатель мыши принимает вид черного креста :.

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

.

Автозаполнение - это функция, которая помогает быстрее вводить данные. При перетаскивании маркера заполнения ячейки может происходить не только копирование одних и тех же значений. Например, введите в какую-либо ячейку число 1, а в соседнюю справа от нее - число 2. Затем отметьте обе ячейки и протяните маркер заполнения вдоль строки. Появится ряд значений: 1,2,3,4…

Ввод формул всегда начинается со знака равенства =. Формулы представляют собой выражения, по которым выполняются вычисления на рабочем листе. В качестве аргументов формулы обычно используются значения ячеек, например:        =A1+B1

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

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

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

	A	B	C
1	5	=A1+1	=B1+1
2	=A1+B1	7
3	=A2+B2

  Пример. В ячейку A1 внесем число 5, в ячейку B2 – число 7. Вычисление по формуле в ячейке B1 даст число 6, а в ячейке A2 – число 11. При копировании формулы из ячейки B1 в C1 изменяется заголовок столбца и в ячейке C1 будет значение 7. В ячейке A3 будет число 18.

  Абсолютная адресация используется в том случае, когда нужно использовать значение, которое не будет меняться в процессе вычислений. Тогда записывают например так: =$J$5. Соответственно, при копировании такой формулы в другие ячейки текущего рабочего листа, в них всегда будет значение, задаваемое формулой =$J$5. Если в приведенном ваше примере в ячейке B1 заменить A1 на $A$1, то значения в ячейках B1 и C1 будут одинаковыми, равными 6.

  Смешанная адресация представляет собой комбинацию относительной и абсолютной адресаций, когда одна из составляющих имени ячейки остается неизменной при копировании. Примеры такой адресации: $A3, B$1. В рассмотренном нами выше примере вместо адреса ячейки $A$1 может быть использован адрес вида A$1, поскольку копирование формулы происходит в пределах одной строки и, следовательно, номер строки изменяться не будет.

Ввод формул

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

1.     Щелкнуть ячейку, в которую необходимо ввести формулу

2.     Набрать знак равенства (=)

3.     Набрать формулу. Она появится в строке формул.

4.     Нажать Enter или щелкнуть «галочку» в строке формул. Excel вычислит результат.

Автосуммирование и автовычисления

Так как одной из наиболее часто употребляющихся функций является СУММ, в Excel предусмотрен быстрый способ ее ввода:

1.     Выделить ту ячейку, в которую необходимо вставить сумму. Лучше, если она расположена в конце строки или столбца данных - это поможет Excel «догадаться», какие ячейки необходимо просуммировать.

2.     Щелкнуть кнопку «Автосумма» на стандартной панели инструментов. В выделенную ячейку будет введена функция СУММ и адрес диапазона левее или выше ячейки.

3.     Если выбранный Excel диапазон будет неверным, то необходимо исправить формулу «вручную» в строке формул, либо перетащить курсор мыши через необходимый диапазон.

4.     Нажать клавишу Enter либо щелкнуть кнопку Enter в строке формул.

Можно просто дважды щелкнуть кнопку «Автосумма» и функция СУММ будет сразу вставлена в выделенную ячейку.

ЗАДАНИЕ

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

2.     Сохраните созданную рабочую книгу в папке с именем Вашей группы под именем «ПР№8_Ваша фамилия»

Упражнение №1. Автозаполнение .

1.1      Переименуйте первый лист Вашей рабочей книги, присвоив ему имя «Автозаполнение» (для этого щелкните правой кнопкой на ярлычке листа и вместо текста «Лист1» внесите текст «Автозаполнение»)

1.2      В ячейку А1 введите любое число, в ячейку В1 – любое слово, а в ячейку С1 введите 1995. Выделите блок А1:С1 и протащите его за маркер заполнения на 10 строк вниз. Проанализируйте результаты.

1.3      В ячейку К9 введите слово Среда. Выделите ячейку. Потяните ее за маркер заполнения на 6 строк вниз. Снова выделите ячейку К9 и протащите ее за маркер на 6 столбцов вправо.  Повторите операцию влево и вверх. Проанализируйте результаты.

1.4      В ячейку Е19 введите число 1. В ячейку Е20 – число 2. В ячейку F19 введите число 3. Выделите блок из двух ячеек Е19:Е20 и протащите его за маркер вниз. Снова выделите блок Е19:Е20 и протащите вверх. Выделите блок E19:F19 и протащите за маркер направо, затем налево. Проанализируйте результаты.

1.5      В ячейку А31 введите число 25. В ячейку В31 – число 50 и протащите блок ячеек А31:В31 за маркер на несколько столбцов вправо.

1.6      В ячейку G38 введите Январь. Выделите ее и протащите на 5 столбцов вправо и влево. Проанализируйте результаты.

Упражнение №2.Ввод формул. Мастер функций. Относительная и абсолютная адресация.

1        Дважды щелкните на ярлычке второго рабочего листа и дайте этому рабочему листу имя «Функции».

2        Сделайте текущей ячейку А1 и введите в неё заголовок «Результаты измерений».

3        Введите числа от 2 до 9 в последовательные ячейки столбца А (используя маркер автозаполнения), начиная с ячейки А2 по А9.

4        Введите в ячейку В1строчку «Удвоенное значение».

5        Введите в ячейку С1 строчку «Квадрат значения».

6        Введите в ячейку D1 строчку «Корень из числа».

7        Введите в ячейку А10 строчку «Сумма».

8        Введите в ячейку А11 строчку «Среднее значение».

9        Введите в ячейку В2 формулу =2*А2.

10   Введите в ячейку С2 формулу =А2*А2.

11   Сделайте активной ячейку D2 и нажмите кнопку «Вставка функции» . (или выберите в меню Вставка/Функция…). В появившемся диалоговом окне слева выберите категорию «Полный алфавитный перечень», а затем справа найдите и отметьте функцию КОРЕНЬ и затем нажмите Ok. Далее в поле «Число» необходимо указать ячейку А2. Это можно сделать 3-мя способами: во-первых, можно просто впечатать необходимое в это поле, во-вторых, можно отодвинуть окно мастера функций на свободное место и щелкнуть указателем мыши по необходимой ячейке и, в-третьих, можно нажать кнопку свертывания окна (),щелкнуть указателем мыши по необходимой ячейке и затем нажать кнопку развертывания(). Затем следует нажать Ok. Проверьте теперь, что в ячейке D2 у Вас действительно находится формула =КОРЕНЬ(A2).

12   Выделите протягиванием ячейки В2, С2 и D2.

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

14   Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значением ячейки в столбце А текущей строки.

15   Сделайте текущей ячейку В10 и нажмите кнопку «Автосумма»  на стандартной панели инструментов Excel, протащите указатель мыши по диапазону А2:А9 а затем нажмите Enter. В строке формул проверьте, какая формула (а точнее, функция) теперь находится в ячейке В10(Должно быть =СУММ(А2:А9)).

16   Сделайте активной ячейку В11 и нажмите кнопку «Вставка функции» . (или выберите в меню Вставка/Функция…). В появившемся диалоговом окне слева выберите категорию «Полный алфавитный перечень», а затем справа найдите и отметьте функцию СРЗНАЧ и затем нажмите Ok. Далее в поле «Число 1» необходимо указать диапазон А2:А9. Это можно сделать 3-мя способами: во-первых, можно просто впечатать необходимое в это поле, во-вторых, можно отодвинуть окно мастера функций на свободное место и протащить указатель мыши по необходимому диапазону и, в-третьих, можно нажать кнопку свертывания окна (), протащить мышь по диапазону и затем нажать кнопку развертывания(). Затем следует нажать Ok. Проверьте теперь, что в ячейке D2 у Вас действительно находится формула =СРЗНАЧ(A2:A9).

17   Изменить одно из значений в столбце А и убедитесь, что соответствующие значения в столбцах В, С и D, в этой же строке были автоматически пересчитаны.

18   Введите в ячейку Е1 строку Масштабный множитель.

19   Введите в ячейку Е2 число 5. (т.к. масштабный множитель равен 5)

20   Введите в ячейку F1 строку Масштабирование.

21   Введите в ячейку F2 формулу =А2*Е2.

22   Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.

23   Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес в формуле задан относительной ссылкой.

24   Щёлкните на ячейке F2,затем в строке формул. Установите текстовый курсор на ссылку Е2 и    нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$Е$2, и нажмите клавишу ENTER.

25   Повторите заполнение столбца F формулой из ячейки F2. Сверьте результаты.

Упражнение №3 (самостоятельное задание).  Перейдите на 3 лист Вашей рабочей книги и дайте ему название «Агентство по продаже недвижимости». Создайте нижеприведенную таблицу и выполните вычисления.

Уровень C

1) Стоимость квартиры находится путем произведения стоимости площади квартиры на коэффициент престижности: =B2*C2*D2

2) Налог на приватизацию вычисляется по формуле: =0,01*E2

Уровень B

1) Вычислите самостоятельно плату агентству по продаже недвижимости (столбец G) стоимость перевода и регистрации недвижимости (столбец H)/

2) Найдите общую стоимость квартиры и услуг в у.е. путем сложения соответствующих значений в столбцах E, F, G, H.

Уровень A

1) В ячейку A8 занесите курс доллара (1$ = 135 тенге)

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

Контрольные вопросы:

1.           Как запустить программу Microsoft Excel?

2.           Какие существуют способы заполнения и редактирования ячеек?

3.           Как выделить смежные и несмежные диапазоны?

4.           Как производится автосуммирование строк и столбцов?

5.           как перейти между листами в одной рабочей книге?

6.           Как в формулах обозначаются абсолютные и относительные ссылки?

Практическая работа №2

Формат ячейки. Границы и заливка. Числовой, процентный, денежный формат. Формат Дата и Время. Применение логических функций ЕСЛИ, И, ИЛИ, НЕ в расчетах.

Краткие теоретические сведения

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

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

Для установки любых параметров границ ячеек и необходимо выбрать Формат, Ячейки и щелкнуть вкладку «Граница». Затем с помощью соответствующих кнопок установить необходимые параметры границ, в том числе тип линии, цвет и с каких сторон ячеек будут установлены границы. При этом в средней части окна  можно увидеть, как будут выглядеть ячейки после изменения их границ.

Для установки любых параметров заливки надо выделить необходимые ячейки, а затем выбрать Формат, Ячейки и щелкнуть вкладку «Вид». На этой вкладке можно установить цвет фона и узор для выделенных ячеек. При этом в поле «Образец» можно увидеть, каким будет фон после изменения параметров.

Автоформат и кнопка «Формат по образцу»

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

1.     Выделить диапазон ячеек.

2.     Выбрать Формат, Автоформат. На экране появится окно «Автоформат», в левой части которого расположен список форматов, а в поле «Образец» виден внешний вид будущего формата.

3.     Необходимо отметить нужный формат в списке.

4.     Чтобы исключить из выбранного формата некоторые элементы, можно щелкнуть кнопку «Параметры».

5.     Нажать Ok.

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

Очень удобно для копирования форматов использовать кнопку «Формат по образцу» (в виде «кисточки») на панели стандартной инструментов:

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

2.     Щелкнуть кнопку «Формат по образцу». Указатель мыши примет вид «кисточки» с расположенным рядом с ней знаком плюс.

3.     Перетащить курсор мыши через ячейки, к которым необходимо применить скопированный формат.

Можно копировать формат одновременно в несколько мест. Для этого надо щелкнуть кнопку «Формат по образцу» дважды. После этого курсор мыши будет иметь вид кисточки до тех пор, пока не будет нажата клавиша ESC.

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

Если необходимо выделить на рабочем листе какие-либо данные, имеющие определенные значения, то можно использовать условное форматирование:

1.     Выделить ячейки, которые необходимо отформатировать.

2.     Выбрать Формат, Условное форматирование.

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

4.     Нажимая кнопку «А также», можно установить до 3-х условий и соответствующих им форматов. В условиях кроме значений, можно указывать формулы, возвращающие значение ИСТИНА либо ЛОЖЬ (см. лекцию 4). Для удаления условий следует использовать кнопку «Удалить».

5.     После сформирования всех необходимых условий и форматов к ним следует нажать кнопку Ok.

Условные форматы можно копировать при помощи кнопки «Формат по образцу».

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

Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение если условие истинно, и другое — если оно ложно.

К логическим функциям относятся ЕСЛИ, И, ИЛИ, НЕ.

Функция ЕСЛИ. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется для условной проверки значений и формул.

Синтаксис:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение   - это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина   - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь   - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

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

И(логическое_значение1; логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ...   - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

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

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

Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!.

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

ИЛИ(логическое_значение1;логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ...   - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

ЗАДАНИЕ:

1.                 Запустите программу Microsoft Excel.

2.     Сохраните этот файл с именем Урок4 Excel _ФИ в вашей папке.

3.                 На Листе 1 в ячейку А1 введите свою Фамилию и Имя, в ячейку А3 – текст «Дата рождения», в ячейку А4 – текст «Сегодня», в ячейку А5 – текст «Возраст», в ячейку А6 – текст «Количество прожитых дней».

4.                 Данные в ячейках А1, А3 и  А6 перенесите по словам.

5.                 В ячейку В3 введите дату своего рождения, а в ячейку В4 – функцию СЕГОДНЯ.

6.                 В ячейке В5 просчитайте свой возраст (от функции СЕГОДНЯ необходимо отнять дату рождения и полученное число разделить на 365 дней в году). На данную ячейку наложить пользовательский формат «лет».

7.                 В ячейке В6 просчитать количество прожитых дней за всю жизнь. На данную ячейку наложить пользовательский формат «дней».

8.       Переименуйте 4 листа в вашей рабочей книге, назовите их:

a.  Лист 1 —«Экономика»

b.  Лист 2 —«Философия»

c.  Лист 3 — «Культурология»

d.  Лист 4 — «Начисление стипендии»

9.    Листы Экономика, Философия, Культурология аналогичны друг другу и представляют собой экзаменационные ведомости, имеющие следующую структуру (Названия дисциплин соответствуют названиям листов): (см рисунок

Рисунок 7 Лист Экономика

10. Лист Начисление стипендии представляет  собой ведомость начисления стипендии в зависимости от среднего балла и имеет следующий вид:

Рисунок 8 Лист Начисление стипендии

11.  Рассчитайте начисление стипендии по следующему правилу:

e.  Если 4<= Ср.балл студента<=5, то студент получает минимальную стипендию и к минимальной стипендии добавляется еще 50% от минимальной стипендии;

f.   Если 3<Ср.балл студента <4, то студент получает минимальную стипендию и к минимальной стипендии добавляется еще 30% от минимальной стипендии;

g.  Если Ср.балл студента <= 3, то студент получает только минимальную стипендию.

12.       Примечание: В данной лабораторной работе следует использовать функции СРЗНАЧ для вычисления среднего балла по трем дисциплинам, а также логические функции ЕСЛИ и И — для вычисления стипендии.

13.       Сохраните книгу и закройте Microsoft Excel.

14.       Вставьте в книгу дополнительный лист и назовите его Графики математических функций.

15.       Построить график функции  при  с шагом 0,1. (Выполните пункты 11 -  14).

16.       Посредством автозаполнения введем в ячейки A1:A11 значения 0; 0,1; 0,2; 0,3 … 1.

17.       В ячейку B1 введите формулу = cos (A1)^2.

18.       Скопируйте эту формулу в ячейки B2:B11.

19.       При помощи мастера диаграмм постройте следующий график

20. Постройте график функции  на промежутке  с шагом 0,1. (Выполните пункты 16 - 19).

Рисунок 9 График функции

21.       В ячейки A20:A30 вводим значения 0; 0,1; 0,2; ….; 1.

22.       В ячейку B20 вводим формулу: =ЕСЛИ(A20<0,5;(1+ABS(0,2-A20))/(1+A20+A20^2);A20^(1/3))

23.       Скопируйте эту формулу в ячейки B21:B30.

24.       При помощи мастера диаграмм постройте следующий график.

Рисунок 10 График функции

25.       Построить в одной системе координат графики двух функций: Y=2sin(x) и z=3cos(2x)-sin(x) при xÎ[-3;0] Подсказка:

26.       В столбец A, начиная с ячейки A40, внесем числа: -3; -2,8; -2,6 … 0.
В ячейках A39, B39, C39 внесем подписи: X, Y,Z.В ячейку B40 введем формулу: =2*SIN(A40).

27.       В ячейку C40 введем формулу: =3*COS(2*A40)-SIN(A40). Построим график.

28.       Построить поверхность z=x²-y²  при xÎ[-1,1]. Подсказка: В ячейки B61:L61 введем числа –1; -0,8; …1. Те же значения введем в ячейки A62:A72. В ячейку B62 введем формулу: =$A62^2-B$61^2. Затем при помощи маркера автозаполнения скопируем формулу на весь диапазон A62:L72 и затем построим график поверхности.

29.       Сохраните книгу и закройте Microsoft Excel.

Контрольные вопросы:

1.           Какие существуют способы ввода формул в ячейку?

2.           Как в ячейку ввести функцию?

3.           Как задать автоформат выделенному диапазону ячеек?

4.           Какие существуют способы форматирования ячеек?

5.           Как наложить пользовательский формат на ячейку?

6.           Как задать условное форматирование для выделенного диапазона ячеек?

7.           Как в формуле сделать ссылку на другой лист?

Практическая работа №3

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

Цель работы -  Привить у студентов навыки создания и обработки баз данных в Microsoft Excel.

Краткие теоретические сведения

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

Использоване списка в качестве базы данных

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

Столбцы списков становятся полями базы данных.

Заголовки столбцов становятся именами полей базы данных.

Каждая строка списка преобразуется в запись данных.

Требования к базе данных (списку) на листе Excel.

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

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

Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.

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

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

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

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

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

Желательно также придерживаться следующих советов:

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

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

Сортировка данных в списке.

Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст "A100", то после сортировки она будет находиться после ячейки, содержащей "A1" и перед ячейкой, содержащей "A11."

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

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

Для сортировки списков в одном столбце следует использовать кнопки «По возрастанию» и «По убыванию».

Для сортировки списка по двум или более столбцам необходимо:

1       Указать любую ячейку в сортируемом списке.

2       Выбрать Данные, Сортировка.

3       Указать столбцы сортировки в полях Сортировать по и Затем по.

Чтобы отсортировать данные по более, чем трем столбцам одновременно, отсортируйте список сначала по трем наименее значимым столбцам. Например, если список содержит сведения о сотрудниках и его требуется отсортировать по полям «Отдел», «Должность», «Фамилия», «Имя» и «Отчество», выберите сначала «Имя» в поле Сортировать по, «Отчество» в поле Затем по и отсортируйте список. Затем выберите «Отдел» в поле Сортировать по, «Должность» в поле Затем по, «Фамилия» в поле В последнюю очередь, по и отсортируйте список.

4       Выбрать другие параметры сортировки и нажать кнопку OK.

5 Повторить шаги 2 - 4 для следующих более значимых столбцов.

Автофильтр. Для применения автофильтра необходимо:

Щелкнуть любую ячейку внутри списка.

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

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

Выбрать значение в списке.

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

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

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

В правом верхнем поле ввода ввести соответствующее значение.

Если необходимо отобрать строки, удовлетворяющие одновременно двум условиям отбора, то надо щелкнуть флажок «И», а затем сформировать второе условие в нижней части окна.

Если необходимо отобрать строки, удовлетворяющие одному из двух условий отбора, то надо щелкнуть флажок «ИЛИ», а затем сформировать второе условие в нижней части окна.

Расширенный фильтр.

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

1       Скопируйте из списка заголовки фильтруемых столбцов.

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

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

4       Укажите ячейку в списке.

5       Выберите пункт Фильтр в меню Данные, а затем — команду Расширенный фильтр.

6       Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте.

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

7       Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов.

Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, нажмите кнопку свертывания диалогового окна:  .

Совет.  Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон

Примеры условий отбора расширенного фильтра

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

Подведение промежуточных итогов в списке (базе данных).

1       Отсортировать список по столбцу, для которого необходимо подвести промежуточный итог. Например, чтобы просуммировать единицы продукции, проданные каждым лицом в списке продавцов, объемы продаж и количество проданного, необходимо отсортировать список по столбцу продавцов.

2       Указать ячейку в этом списке.

3       Выбрать команду Итоги в меню Данные.

4       Выбрать столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

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

6       Выбрать столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по.

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

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

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

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

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

ЗАДАНИЕ

Создайте в своей рабочей книге новый лист под названием П5. Заполните в нем базу данных с помощью Формы:

1)  Заполним базу данных под названием «Планеты Солнечной системы». Введите в 1-ю, 2-ю и 3-ю строки листа следующие данные:

Планеты Солнечной системы
Планета	Период	Расстояние	Диаметр	Масса	Спутники
Солнце	0	0	13929	2000000	0

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

Планеты Солнечной системы
Планета	Период	Расстояние	Диаметр	Масса	Спутники
Солнце	0	0	13929	2000000	0
Меркурий	0,241	58	4,9	0,32	0
Венера	0,615	108	12,1	4,86	0
Земля	1	150	12,8	6	1
Марс	1,881	288	6,8	0,61	2
Юпитер	11,86	778	142,6	1906,98	16
Сатурн	29,46	1426	120,2	570,9	17
Уран	84,01	2869	49	87,24	14
Нептун	164,8	4496	50,2	103,38	2
Плутон	247,7	5900	2,8	0,1	1

Ход выполнения работы:

1)  Внесите в ячейки B2:E2 следующие примечания (Вставка–Примечание): «Период обращения по орбите в земных годах»; «Среднее расстояние от Солнца, в млн.км»; «Экваториальный диаметр, тыс.км»; «Масса в 10^24кг».

Скопируйте таблицу на Лист2 – Лист4

      Область таблицы A2:F12 можно рассматривать как базу данных, т.к. она соответствует следующим ограничениям:

a)   Первый ряд базы данных содержит неповторяющиеся имена полей.

b)  Остальные ряды базы данных содержат записи, которые не являются пустыми рядами

c)   Информация по полям (столбцам) является однородной, т.е. только цифры или только текст.

2)  Лист 1. Щелкните любую ячейку внутри базы данных и выберите команду: Данные, Фильтр, Автофильтр. На именах полей должны появиться кнопки с изображением стрелок. Понажимайте эти кнопки и исследуйте их работу.

3)  Нажмите кнопку на поле «Планета». Выберите пункт «Условие». В появившемся окне установите параметры так, чтобы отобрать планеты, названия которых начинаются с букв «С» или «Ю».

4)  После этого нажмите кнопку на поле «Масса» и выбрав пункт «Условие», оставьте в списке только те планеты, масса которых <600.

5)  Лист 2. Условия фильтрации: найдите все планеты, диаметр которых меньше 50 тыс.км и масса которых меньше 4*10^24 кг.

6)  Лист 3. Найдите все планеты, находящиеся от Солнца на расстоянии не менее 100 млн. км., имеющих массу в диапазоне от 3*10^24 кг до 500*10^24 кг,  а также не более 2-х спутников.

7)  Лист 4. Теперь перейдем к созданию Расширенного фильтра. Для этого вначале скопируйте 1-й ряд базы данных (2-я строка) в строки 15 и 20 Вашего рабочего листа. В ячейке B16 (поле Период) укажите условие >10. В ячейке F16 (поле Спутники) укажите условие >=2. Щелкните любую ячейку внутри базы данных. Затем выполните команду Данные, Фильтр, Расширенный фильтр. В появившемся окне установите флажок “Скопировать результат в другое место”. Поле “Исходный диапазон” должно иметь значение $A$2:$F$12. В поле «Диапазон условий» установите $A$15:$F$16. В поле «Поместить результат в диапазон» укажите $A$20:$F$25. Нажмите кнопку Ok. В итоге Вы должны отобрать 4 планеты: Юпитер, Сатурн, Уран, Нептун.

8)  Скопируйте заголовки столбцов в строки Н15:М15 и Н20:М20. В ячейке Н16 (поле Период) укажите условие >10, в ячейке М17 (поле Спутники) укажите условие >=2. Затем выполните команду Данные, Фильтр, Расширенный фильтр с правильным указанием параметров «Диапазон условий» и «Поместить результат в диапазон».

     Сравните два результата фильтрации. В чем отличия.

9) С использованием расширенного фильтра найдите планеты:

а) «Диапазон условий»:

Планета	Период	Расстояние	Диаметр	Масса	Спутники
М*
С*
Земля

б)  имеющие период обращения более 2-х земных лет и экваториальный диаметр менее 50 тыс.км.,  или имеющих спутников более 15.

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

10)    Покажите работу преподавателю.

11.             Новый лист назовите Полный ассортимент, он должен выглядеть так:

12.       С помощью команды Данные —Фильтр — Автофильтр на листе Полный ассортимент отфильтруйте базу данных: оставьте только персональные ксероксы. Восстановите весь список.

13.       С помощью команды Данные —Фильтр — Автофильтр на листе Полный ассортимент отфильтруйте базу данных: оставьте только профессиональные и профессиональные плюс факсы.

14.       Восстановите весь список и отключите автофильтр.

15.       С помощью команды Данные —Сортировка отсортируйте базу данных на листе Полный ассортимент по виду и названию оргтехники.

a.            В меню Данные выберите команду Сортировка.

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

c.            В группе затем по щелкните на кнопке «вниз» и выделите ключ Название.

d.           ОК.

Теперь в списке группа Факс расположена перед группой Ксерокс, а внутри групп названия отсортированы в алфавитном порядке по возрастанию.

16. Составьте итоговый отчет на листе Ксероксы:

ü На листе ксероксы выделите ячейку С10

ü В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

ü Щелкните на стрелке текстового поля При каждом изменении в и в появившемся окне списка выделите строку Название. Так вы подведете итог по каждому из названий ксероксов.

ü Убедитесь, что в поле Операция находится слово Сумма.

ü Убедитесь, что в поле Добавить итоги по флажок установлен напротив строки Сумма. Для этого, если потребуется, прокрутите весь список.

ü Проследите, чтобы были установлены флажки напротив строк Заменить текущие итоги и Итоги под данными, и щелкните кнопку ОК.

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

17. Удалите промежуточные итоги:

ü Убедитесь, что одна из ячеек подытоженных данных выделена.

ü В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

ü Щелкните в нем на кнопке Убрать все.

Таблица данных вернется к прежнему виду.

18. Введите вложенные промежуточные итоги (Усредните стоимость и цену по видам товаров):

ü Сначала выполним сортировку, для этого перейдите на лист Полный ассортимент и выделите ячейку С10.

ü В меню Данные выберите команду Сортировка. Откроется диалоговое окно Сортировка диапазона.

ü Убедитесь, что в группе Сортировать по находится ключ Товар, и включите опцию По возрастанию.

ü В группе Затем по установите ключ Название и включите опцию По возрастанию.

ü Выделите ячейку С10 и из меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

ü В окне списка При каждом изменении в выделите Товар.

ü В окне списка Операция выделите функцию Среднее.

ü Проследите, чтобы в окне списка Добавить итоги по были установлены флажки напротив строк Стоимость и Цена.

ü Удалите флажок в поле Заменить текущие итоги и щелкните ОК.

ü В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.

ü Щелкните на стрелке поля При каждом изменении и в появившемся списке выделите Название.

ü В окне Операция выделите функцию Среднее.

ü Проследите, чтобы в окне Добавить итоги по флажки были установлены только напротив строк Стоимость и цена. ОК.

17.   Проведите структурирование рабочих листов:

Скройте, а затем покажите детали структуры

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

ü Перейдите к строке 44. в этой строке дублируется информация из предыдущей строки. Выделите заголовок строки и удалите все её содержимое.

ü Снова раскройте все структуры второго уровня. Для этого щелкните на кнопках структуры второго уровня (они приобрели вид +).

ü Щелкните на всех кнопках третьего уровня. Теперь на рабочем листе видны средние по всем названиям товаров и общее среднее.

Покажите, а затем скройте детали списка персональных ксероксов

ü Поместите указатель мыши на кнопку показа деталей, соответствующую названию ксерокса «Персональный» (кнопка со знаком + напротив строки Персональный Среднее), и щелкните левой кнопкой мыши.

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

ü Снова щелкните на этой же кнопке (на ней теперь изображен – и она работает как кнопка скрытия).

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

18. Сохраните книгу «ПР_№5_ФИО».

Контрольные вопросы:

1.     Какая таблица может быть использована в качестве базы данных?

2.                 Как задать допустимые значения для элементов базы данных?

3.                 Как отсортировать данные по нескольким полям?

4.                 Как вывести первые несколько элементов базы данных?

5.                 Как настроить расширенный фильтр?

6.                 Как показать промежуточные итоги для базы данных?

Практическая работа №4

Связывание рабочих листов, консолидация таблиц. Создание, редактирование, форматирование сводной таблицы.

Сводная таблица – это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных, отображая разные страницы. При помощи сводных таблиц можно также осуществить фильтрацию данных, а также отобразить детальные данные области.

Источниками данных для сводных таблиц являются списки или базы данных, созданные на листах Excel, или другие сводные таблицы а также внешние данные. (Например, базы данных, созданные в Microsoft Access)

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

Сводная таблица создается с помощью мастера сводных таблиц, используемого для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, надо выбрать команду «Сводная таблица» в меню «Данные». (Если сводная таблица создается на основе списка на листе Excel, то предварительно можно отметить любую ячейку внутри списка. Это позволит Excel автоматически определить область, которую занимает список)

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

ЗАДАНИЕ:

1.     Откройте книгу «ПР_№5_ФИО».

2.     Перейдите на лист «Полный ассортимент».

3.     На листе Полный ассортимент выделите диапазон B8:H28 и в меню Данные выберите команду Сводные таблицы, откроется диалоговое окно Мастер сводных таблиц.

На первом и втором шаге нажмите Далее

На третьем шаге перетащите мышью кнопку Товар в поле Столбец, кнопку Название в поле Строка, кнопку Сумма в поле Данные.

На четвертом шаге постройте сводную таблицу на новом листе Отчет в ячейке C8.

4.     Ваш рабочий лист должен выглядеть так:

5.     Добавьте количество проданных товаров каждого вида:

ü Щелкните мышью в любой из ячеек сводной таблицы и перетащите кнопку Количество в поле Данные;

ü щелкните правой кнопкой мыши в ячейке D10 и выберите команду Параметры поля, откроется диалоговое окно Вычисление поля сводной таблицы, в поле имя наберите Выручка вместо Сумма по полю Сумма и нажмите ОК;

ü аналогичным образом в ячейке D11 наберите Продано всего.

6.     Поместите поле на отдельной странице

ü сделайте активной сводную таблицу и перетащите кнопку Товар из ячейки E8 в поле страницы.

ü ваш рабочий лист выглядит так:

7.     Перенесите кнопку из области страниц в область строк

ü кнопка Товар перенесена из области страниц в область строк, и теперь все данные расположены на одной странице.

8.     Скройте, а затем покажите детали сводной таблицы:

-         в столбце Товар щелкните дважды на заголовке Ксерокс, данные по этому виду товара сжались;

-         повторным двойным щелчком в этой ячейке сводная таблица вернется к прежнему виду.

9.     Скройте данные:

ü Перетащите кнопку Товар в область страниц;

ü дважды щелкните на кнопке Товар, откроется окно Вычисление сводной таблицы;

ü в окне списка Скрыть элементы выделите строку Факс и ОК;

ü в списке кнопки Товар теперь находятся только названия страниц Все им Ксерокс;

ü аналогичным образом покажите скрытые данные.

10. Покажите все данные о продаже профессиональных ксероксов

ü дважды щелкните в ячейке E15, появится лист, на котором отображены все данные о продаже профессиональных ксероксов.

11. Измените функцию подведения итогов:

ü перетащите кнопку Товар в ячейку F8;

ü щелкните правой кнопкой мыши в ячейке D11 по словам Продано всего, в поле имя введите Продано Минимум, в поле Операция выберите Минимум, ОК

ü добавьте максимальные значения количества проданных товаров, после чего ваша сводная таблица должна выглядеть так:

12. Удалите общие итоги:

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

13. Отформатируйте сводную таблицу:

ü На панели инструментов Сводные таблицы нажмите кнопку Формат отчета и выберите автоформат Отчет 6;

ü отформатируйте числа в столбце Выручка, для этого выделите диапазон ячеек E9:E21, из контекстного меню выберите Формат ячеек и задайте формат — Тыс.тенге.

ü ваша сводная таблица должна выглядеть так:

14. Постройте диаграмму сводной таблицы:

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

ü выделите сводную таблицу через команду Сводная таблица—Выделить—Таблица целиком на панели инструментов Сводные таблицы.

ü нажмите кнопку Мастер диаграмм на панели инструментов Сводные таблицы;

ü измените тип диаграммы на объемный вариант обычной линейчатой диаграммы;

ü поместите диаграмму на отдельном листе Диаграмма 1.

Лабораторная работа №5

Внешний вид MS Excel. Ввод и редактирование данных. Вставка формул и редактирование данных.

Цель работы -  Привить у студентов навыки ввода и редактирования ячеек Microsoft Excel. Привить у студентов навыки ввода формул и функций, а так же форматирования данных в листах Microsoft Excel.

Краткие теоретические сведения

Для вставки нового листа необходимо выделить лист, перед которым надо вставить новый лист и Выбрать Вставка + Лист.

Для удаления листа необходимо его выделить и выбрать Правка + Удалить либо щелкнуть правой кнопкой мыши на ярлыке листа и выбрать команду «Удалить».

Маркер заполнения - это небольшой черный квадрат в углу выделенного диапазона. Попав на маркер заполнения, указатель мыши принимает вид черного креста :.

Автозаполнение - это функция, которая помогает быстрее вводить данные. При перетаскивании маркера заполнения ячейки может происходить не только копирование одних и тех же значений. Например, введите в какую-либо ячейку число 1, а в соседнюю справа от нее - число 2. Затем отметьте обе ячейки и протяните маркер заполнения вдоль строки. Появится ряд значений: 1,2,3,4…

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

Чтобы выделить диапазон по его имени, надо щелкнуть стрелку вниз в поле «Имя» в строке формул и выбрать имя диапазона из списка.

Обратите внимание, что в диалоговом окне «Имя» адреса ячеек или диапазонов появляются со знаком $. Это так называемые абсолютные ссылки, которые не изменяются в процессе  различных операций Excel.

Вставка строк и столбцов выбрать Вставка + Строки или Вставка + Столбцы.

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

Автосуммирование и автовычисления

Так как одной из наиболее часто употребляющихся функций является СУММ, в Excel предусмотрен быстрый способ ее ввода:

1.                 Выделить ту ячейку, в которую необходимо вставить сумму. Лучше, если она расположена в конце строки или столбца данных - это поможет Excel «догадаться», какие ячейки необходимо просуммировать.

2.                 Щелкнуть кнопку «Автосумма» на стандартной панели инструментов. В выделенную ячейку будет введена функция СУММ и адрес диапазона левее или выше ячейки.

3.                 Если выбранный Excel диапазон будет неверным, то необходимо исправить формулу «вручную» в строке формул, либо перетащить курсор мыши через необходимый диапазон.

4.                 Нажать клавишу Enter либо щелкнуть кнопку Enter в строке формул.

Можно просто дважды щелкнуть кнопку «Автосумма» и функция СУММ будет сразу вставлена в выделенную ячейку.

Работа с именами ячеек и диапазонов

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

Присваивание имени диапазону ячеек.

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

2.                 Установить указатель на поле имен в левой части строки формул и нажать кнопку мыши.

3.                 Ввести имя.

Создание имен из заголовков строк и столбцов.

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

2.                 Выбрать Вставка, Имя, Создать.

3.                 В появившемся окне в группе флажков «По тексту» указать расположение заголовков, из которых следует создать имена.

Вставка имени в  формулу

Чтобы вставить имя в формулу, его предварительно следует присвоить.

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

Правка имен ячеек, диапазонов, формул и ссылок.

1.                 Необходимо выбрать Вставка + Имя + Присвоить

2.                 Выбрать необходимый элемент из списка

3.                 Для правки ссылки исправить ее в поле «Формула»

4.                 Для правки имени надо ввести новое имя и нажать кнопку «Добавить». После этого старое имя можно удалить.

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

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

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

Для установки любых параметров границ ячеек и необходимо выбрать Формат, Ячейки и щелкнуть вкладку «Граница». Затем с помощью соответствующих кнопок установить необходимые параметры границ, в том числе тип линии, цвет и с каких сторон ячеек будут установлены границы. При этом в средней части окна  можно увидеть, как будут выглядеть ячейки после изменения их границ.

Для установки любых параметров заливки надо выделить необходимые ячейки, а затем выбрать Формат, Ячейки и щелкнуть вкладку «Вид». На этой вкладке можно установить цвет фона и узор для выделенных ячеек. При этом в поле «Образец» можно увидеть, каким будет фон после изменения параметров.

Автоформат и кнопка «Формат по образцу»

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

1.                 Выделить диапазон ячеек.

2.                 Выбрать Формат, Автоформат. На экране появится окно «Автоформат», в левой части которого расположен список форматов, а в поле «Образец» виден внешний вид будущего формата.

3.                 Необходимо отметить нужный формат в списке.

4.                 Чтобы исключить из выбранного формата некоторые элементы, можно щелкнуть кнопку «Параметры».

5.                 Нажать Ok.

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

Очень удобно для копирования форматов использовать кнопку «Формат по образцу» (в виде «кисточки») на панели стандартной инструментов:

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

2.                 Щелкнуть кнопку «Формат по образцу». Указатель мыши примет вид «кисточки» с расположенным рядом с ней знаком плюс.

3.                 Перетащить курсор мыши через ячейки, к которым необходимо применить скопированный формат.

Можно копировать формат одновременно в несколько мест. Для этого надо щелкнуть кнопку «Формат по образцу» дважды. После этого курсор мыши будет иметь вид кисточки до тех пор, пока не будет нажата клавиша ESC.

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

Если необходимо выделить на рабочем листе какие-либо данные, имеющие определенные значения, то можно использовать условное форматирование:

1.                 Выделить ячейки, которые необходимо отформатировать.

2.                 Выбрать Формат, Условное форматирование.

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

4.                 Нажимая кнопку «А также», можно установить до 3-х условий и соответствующих им форматов. В условиях кроме значений, можно указывать формулы, возвращающие значение ИСТИНА либо ЛОЖЬ (см. лекцию 4). Для удаления условий следует использовать кнопку «Удалить».

5.                 После сформирования всех необходимых условий и форматов к ним следует нажать кнопку Ok.

Условные форматы можно копировать при помощи кнопки «Формат по образцу».

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

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

Изменение ширины столбцов и высоты строк.

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

Для точной установки высоты строк следует выбрать Формат, Строка, Высота. При этом следует иметь в виду, что высота строки измеряется в пунктах (1/72 дюйма) в диапазоне от 0 до 409. (Если установить высоту строки равной 0, то она будет скрыта.)

Для точной установки ширины столбцов следует выбрать  Формат, Столбец, Ширина и в появившемся окне ввести число в диапазоне от 0 до 255 (Это число приблизительно равно количеству символов стандартного шрифта, которое поместиться в ячейке указанной ширины).  Если ввести ширину столбца раной 0, то столбец будет скрыт.

Ввод формул

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

1.                 Щелкнуть ячейку, в которую необходимо ввести формулу

2.                 Набрать знак равенства (=)

3.                 Набрать формулу. Она появится в строке формул.

4.                 Нажать Enter или щелкнуть «галочку» в строке формул. Excel вычислит результат.

Задание:

1.                 Запустите Microsoft Excel:

с помощью кнопки Пуск;

2.                 Рассмотрите внешний вид окна Microsoft Excel.

3.                 В ячейку С10 введите цифру «1», а в ячейку D10 цифру «2» Выделите диапазон С10:D10 и протяните маркер заполнения до ячейки Н10.

4.                 В ячейку С10 введите слово «Январь» и протяните маркер заполнения до ячейки Н10.

5.                 Используя команду Сервис + Параметры + Списки создайте новый список со следующими элементами:

Убийства

         Тяжкие телесные повреждения

         Разбой

         Грабеж

         Угон автотранспорта

         Мошенничество

         Хулиганство

         Кража

6.                 В ячейку В11 введите слово «Убийства» и протяните маркер заполнения вниз до ячейки В18.

7.                 В ячейку В19 введите «Итого», в ячейку В20 – «Из них раскрыто».

8.                 В ячейку В21 скопируйте текст из ячейки В11 и вновь протяните марке заполнения вниз до ячейки В28.

9.                 В ячейку В29 введите «Итого».

10.             Выделите диапазон В6:В8 и импортируйте его данные в список.

11.             В ячейку G6 введите «Темпы роста» и перетащите маркер заполнения в ячейку G8.

12.             В диапазоне С11:Н15 введите следующие данные:

10	11	9	7	5	14
18	10	15	17	21	15
12	18	14	16	17	21
30	44	26	28	20	19
25	21	19	12	14	9

13.             В ячейке С16 введите число «5», а в ячейке D16 число «7», выделите диапазон С16:D16 и протяните маркер заполнения до ячейки Н16.

14.             В ячейке С17 введите число «8», а в ячейке D17 число «10», выделите диапазон С17:D17 и протяните маркер заполнения до ячейки Н17.

15.             В ячейке С18 введите число «12», а в ячейке D18 число «14», выделите диапазон С18:D18 и протяните маркер заполнения до ячейки Н18.

16.             В ячейку I10 введите слово «Всего».

17.             Используя кнопку Автосумма на панели инструментов         заполните столбец «Всего» для заполненных строк таблицы.

18.             В стоке «Итого» просчитайте сумму по столбцам.

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

20.             Диапазону С11:С29 присвойте имя Правонарушения.

21.             В диапазоне С10:I19 каждой строке и столбцу присвоить имена по заголовкам верхней строки и левого столбца.

22.             Выделите блоки с помощью поля имени и с помощью функциональной клавиши F5.

Рисунок 3 Внешний вид окна документа

23. После Лист3 добавьте новый лист.

24. Переименуйте Лист1 и присвойте ему имя «Правонарушения  2004».

25. Переместите Лист2 после Лист4.

26. Удалите Лист3.

27. Сохраните файл под именем Урок1 Excel _ФИ.

28. В ячейку В4 введите функцию СЕГОДНЯ.

29. В ячейку К10 введите слово «Среднее», в ячейку L10 - слово «Максимальное», в ячейку М10 введите слово «Минимальное».

30. В столбце К рассчитайте среднее значение цифр соответствующей строки с января по июнь (функция СРЗНАЧ).

31. В столбцах L и M рассчитайте максимальное (функция МАКС), минимальное (функция МИН) значение цифр соответствующей строки с января по июнь, используя имена диапазона ячеек.

32. В ячейку D21 введите формулу = D11*D7 и не закрывая ячейки измените тип ссылки в ячейке D7 на абсолютную.

33. Скопируйте содержимое данной ячейки по вертикали до ячейки D28.

34. Каждую ячейку в полученном вертикальном столбце скопируйте по горизонтали до столбца I.

35. Рассмотрите, как в формулах изменяются относительные и абсолютные ссылки на ячейки при копировании.

36. Для полученных данных рассчитайте строку Итого, столбцы Всего, Среднее, Максимальное, Минимальное.

37. В ячейку С30 введите функцию расчета количества непустых ячеек в диапазоне С1:С29 (функция СЧЕТЗ).

38. В ячейку D30 введите функцию расчета количества ячеек с числами в диапазоне D1:D20 (функция СЧЕТ).

39. На Листе 2 выделите диапазон I7:K7  и поверните текст на 90⁰ и выровняйте его по центру по горизонтали и по вертикали.

40. На листе Правонарушения  2004 выделите диапазон D11:J18 и установите для чисел пользовательский формат ###0"штук". Если в ячейках появятся значки ####, то измените ширину столбцов.

41. Выделите диапазон D10:J10 и поверните текст в этих ячейках на 45°.

42. Для диапазонов C6:D8 и С10:М29 установите вокруг них толстые рамки.

43. Для диапазона J11:J29 установите условное форматирование; выделите красным цветом  и полужирным шрифтом те ячейки, значения которых больше или равно 90.

Контрольные вопросы:

1.     Как запустить программу Microsoft Excel?

2.     Какие существуют способы заполнения и редактирования ячеек?

3.     Как выделить смежные и несмежные диапазоны?

4.     Как создать и импортировать списки?

5.     Как производится автосуммирование строк и столбцов?

6.     как перейти между листами в одной рабочей книге?

7.     Какие существуют способы ввода формул в ячейку?

8.     Как в ячейку ввести функцию?

9.     Как задать автоформат выделенному диапазону ячеек?

10. Какие существуют способы форматирования ячеек?

11. Как наложить пользовательский формат на ячейку?

12. Как задать условное форматирование для выделенного диапазона ячеек?

13. Как в формуле сделать ссылку на другой лист?

14. Как в формулах обозначаются абсолютные и относительные ссылки?

Лабораторная работа №6

Графические возможности MS Excel. Построение диаграмм.

Цель работы -  Привить у студентов навыки построения и редактирования диаграмм в Microsoft Excel и печати рабочих листов.

Краткие теоретические сведения

Типы диаграмм (основные)

Круговая – используется для отображения относительного соотношения между частями целого.

Линейчатая – для сравнения значений между частями целого.

Гистограмма – похожа на линейчатую. Используется для показа соотношения между частями целого.

График – для отображения тенденций изменения данных за равные промежутки времени.

Точечная – для отображения различий между значениями в двух рядах.

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

Большинство из этих диаграмм могут быть объемными, что помогает подчеркнуть различия между разными наборами данных. Excel поддерживает много других типов диаграмм.

Термины, используемые при построении диаграмм:

Ряды данных – это графические элементы диаграмм: полосы, сектора, линии и т.п. Обычно ряды данных располагаются в  строках на рабочем листе.

Категории – отображают количество  элементов в ряду. (Например, 2 ряда для сравнения объемов продаж в 2-х разных магазинах и 4-х категориях по кварталам.) Обычно категории располагаются в солбцах на рабочем листе, а названия категорий - в заголовках столбцов.

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

На оси Y отображаются все значения полос, линий или точек графика. В объемных диаграммах ось Z направлена вертикально.

Легенда - описание отдельных элементов диаграммы.

Сетка - продолжение деления осей, улучшает восприятие и анализ данных.

Мастер диаграмм

Для построения диаграммы прежде всего необходимо выделить ячейки, содержащие данные, которые должны быть отражены на диаграмме.

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

После этого следует нажать кнопку «Мастер диаграмм» на стандартной панели инструментов. Мастер диаграмм работает в 4 шага:

На 1-м шаге необходимо выбрать тип диаграммы в списке слева. (Заметим, что типы диаграмм делятся еще на стандартные и нестандартные. Для выбора соответствующего списка надо щелкнуть вкладку в верхней части окна.) Внешний вид диаграммы мы выбираем, щелкнув соответствующий рисунок в правой части окна. Для перехода ко 2-му шагу следует нажать кнопку «Далее».

На 2-м шаге в средней части окна можно увидеть приблизительно внешний вид будущей диаграммы и, если он не будет соответствовать ожидаемому результату, то в этот момент можно изменить диапазон исходных данных для построения диаграммы. Для перехода ко 3-му шагу следует нажать кнопку «Далее».

На 3-м шаге, если необходимо, можно изменить или установить параметры целого ряда элементов диаграммы: заголовков, осей, линий сетки, легенды, подписей данных и т.п. Для этого надо щелкать соответствующие вкладки в верхней части окна. Для перехода ко 4-му шагу следует нажать кнопку «Далее».

На 4-м шаге мы определяем, где будет располагаться построенная нами диаграмма - на отдельном или на имеющемся листе рабочей книги. После этого следует нажать кнопку «Готово».

Заметим, что существует очень быстрый способ построения одного из типов диаграмм - гистограммы на отдельном листе. Необходимо только выделить нужный диапазон и нажать клавишу F11.

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

Задание:

1.           Запустите программу Microsoft Excel.

2.           Откройте файл Урок2 Excel _ФИ в папке с именем вашей группы.

3.           Сохраните этот файл с именем Урок3 Excel _ФИ в той же папке.

4.           На листе Правонарушения 2004 выделите диапазон C10:I18.

5.           Постройте диаграмму типа Гистограмма с рядами в строках. Название диаграммы: «Отчет по правонарушениям», название оси Оx «2004 год». Поместите диаграмму на этом же рабочем листе в диапазон О1:Х24.

6.           На диаграмме для оси Ох установите размер шрифта – 8 пт.

7.           Для названия диаграммы установите размер шрифта – 18 пт, цвет шрифта – красный, цвет заливки – голубой.

8.           Выделите диапазон ячеек С10:D18 постройте для этого диапазона круговую диаграмму с рядами в строках, название диаграммы «Правонарушения за январь», удалите легенду и установите в подписях значений категории и доли. Уберите линии выноски. Поместите диаграмму на отдельный лист.

9.           Из диаграммы на листе Правонарушения 2004 удалите все данные, относящиеся к Грабежам.

10.       На листе выведите диапазон ячеек C24:I24 и поместите эти данные в имеющуюся на этом листе диаграмму с помощью перетаскивания диапазона на область диаграммы.

11.       В диаграмме измените порядок рядов данных: Грабеж, Кража, Мошенничество, Разбой, Тяжкие телесные повреждения, Убийства, Угон автотранспорта, Хулиганство используя команду «Формат рядов данных» из контекстного меню любого ряда.

12.       На Листе2 выделите группу ячеек А8:Н15 и постройте комбинацию гистограммы и графика с общей осью значений (вкладка Нестандартные; тип График/Гистограмма). Поместите диаграмму на отдельный лист.

13.       На листе измените тип диаграммы на трехмерную гистограмму.

14.       Удалите в этой диаграмме легенду и увеличьте размер области построения диаграммы.

15.       Скопируйте диаграмму «Отчет по правонарушениям» на Лист 4.

16.       На Листе 4 измените тип диаграммы на «объемный вариант гистограммы с накоплением» и добавьте легенду, которую расположите под областью построения. Установите для оси Ох основные и промежуточные линии сетки. На диаграмме отразите значения данных.

17.       В диаграмме измените цвета рядов данных и цвет области построения. Установите тень к легенде.

18.       На листе Правонарушения 2004 измените ориентацию диаграммы в пространстве, перетаскивая углы области построения диаграммы.

19.       Просмотрите на предварительном просмотре содержимое листа Правонарушения 2004.

20.       На листе Правонарушения 2004 выделите диапазон ячеек A1:К30 и выведите на печать выделенный диапазон.

21.       Выделите ячейку А1 и установите параметры страницы следующим образом: ориентация листа - альбомная; масштаб - разместить не более, чем на 1 странице в ширину и на 1 странице в высоту. Установите поля: верхнее и нижнее 2,1; левое - 3,5; правое - 0,5. Просмотрите, используя кнопку «Просмотр».

22.       Установите на листе верхний и нижний колонтитул. В нижний колонтитул поместите информацию об имени файла и номере страницы. В верхний колонтитул поместите информацию об имени создателя документа, название «Данные Министерства внутренних дел 2004 год» и дату.

23.       Распечатайте рабочий лист с сеткой и без нее.

24.       Перейдите на лист Диаграмма.

25.       Распечатайте диаграмму, уместив ее на странице и используя черно - белую печать.

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

Социальная структура
населения Москвы на 1897г
Категория населения	%
Занятые в промышленности	38,6
Прислуга, поденщики	19,4
Занятые в торговле	14
Занятые на транспорте	6,5
рантье и пенсионеры	10,9
чиновники и военные	4,6
люди свободных профессий	6

Использование домашнего компьютера
(исследования фирмы Microsoft)
Вид работы	%
Игры	38,6
Обработка текстов	19,4
Ведение финансов	14
Домашняя работа	6,5
Образование	10,9
Домашний бизнес	4,6

Таблица 1
Численность	РОССИЯ	КАЗАХСТАН
Население	148310174	16721113
Мужчины	69562474	8115857
Женщины	78747700	8605256
Дети	33314753	5247906
Взрослые	98913416	10474265
Старики	16082005	998942

Таблица 2
Проц. Состав	РОССИЯ	КАЗАХСТАН
Мужчины %
Женщины %
Дети %
Взрослые %
Старики %

Республика Казахстан
Население	16721113
Мужчины	8115857
Женщины	8605256
Дети	5247906
Взрослые	10474265
Старики	998942
Мальчики	2659586
Мужчины	5158149
Дедушки	298122
Девочки	2588320
Женщины	5316116
Бабушки	700820
Горожане	9605740
Сельчане	7115373
Городские мужчины	4576954
Городские женщины	5028786
Сельские мужчины	3538903
Сельские женщины	3576470

 

 

 

Примечание: Таблицу 2 заполните сами и введите в нее необходимые формулы.  Затем по данным в таблице 2 сформируйте диаграмму. Постарайтесь сформировать точно такую же диаграмму.

27.       Сохраните книгу и закройте Microsoft Excel.

Контрольные вопросы:

1.           Сколько шагов содержит Мастер диаграмм?

2.           Как удалить из диаграммы столбец данных?

3.           Как в диаграмме изменить порядок рядов данных?

4.           Для какого количества данных можно построить круговую диаграмму?

5.           Как добавить данные в уже имеющуюся диаграмму?

Практическая работа № 7

Тема: Электронные таблицы. Microsoft Excel

Цели: Студенты должны знать: окно программы MS Excel, основные элементы MS Excel, операции с ячейками, расчетные операции, основные формулы и функции Excel.

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

Теоретическая часть

Что такое электронная таблица? Когда-то давным-давно, в 1979 году, двое студентов Ден Бриклин и Боб Френкстон, изучавших основы предпринимательства, решили, что выполнение математических расчетов на бумаге с помощью карандаша и калькулятора – пустая трата времени. И вместо того, чтобы в безисходности просиживать над выполнением заданий, как это делали другие студенты, друзья решили придумать нечто этакоеи создали на своем компьютере первую программу электронных таблиц (ЭТ). Они назвали свою программу VisiCalc, что означало Наглядный Калькулятор. Идея, лежащая в основе VisiCalc и всех других программ ЭТ, состоит в том, чтобы в одни ячейки ввести числа, а в других ячейках указать, как эти числа должны быть математически преобразованы, и предоставить компьютеру сделать всю сложную работу по вычислению результата, а себе оставить более легкую работу по вводу исходных данных.

Все современные программы ЭТ, ведут свое происхождение от VisiCalc, созданной Деном и Бобом.

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

Основные термины и понятия ЭТ

ЭТ состоит из (см. рис.1):

·  рабочего листа, разделенного на строки и столбцы;

·  ячеек, образющихся на пересечении строки и столбца;

·  чисел, служащих для ввода любых числовых значений

·  текстовых строк, предназначенных для ввода заголовков, описания продуктов, имен клиентов и т.д.;

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

Каждая ячейка ЭТ имеет уникальный адрес ячейки, состоящий из буквы столбца и номера строки. Ячейка, в которой находится табличный курсор, называется активной.

Когда Вы вводите в ячейку формулу, результат вычисления по

 Рисунок 1 Рабочий лист Excel

формуле появляется в ячейке рабочего листа, а сама формула отображается в строке формул.

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

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

·             + для выполнения сложения;

·             - для выполнения вычитания;

·             * для выполнения умножения;

·             / для выполнения деления;

·             ^ для возведения числа в степень.

Чаще всего в формулах, создаваемых в Excel, явные числовые значения (такие как числа 10 или 15) не используются. Вместо явных значений в формулах в формулах используются ссылки на ячейки, в которых содержатся эти значения (например ячейки А2 или В3). Причина довольно проста, если вы создадите формулу со ссылками на ячейки, в которых находятся нужные для вычислений значения, то результат вычислений по формуле автоматически изменится после изменения чисел в ячейках.

Самый простой способ для задания формул в ЭТ – это использование кнопки Автосумма å. Кнопка Автосумма å не только автоматически вставляет функцию =СУММ, но и выделяет блок ячеек, которые, по мнению программ, вы собираетесь суммировать. Если программа правильно выделила ячейки, то все что вам остается сделать для создания формулы, - это завершить ввод (нажать Enter).

В Excel отдельные рабочие листы организуются в рабочие книги. Каждая книга может содержать до 256 рабочих листов.

Рабочая книга – основной документ Excel.

Практическая часть

Запустите Excel: Пуск4Программы4Microsoft Excel

Если Вы все сделали правильно, то Excel  начнет загружаться, и Вы увидите на экране ее заставку. После завершения загрузки появится экран Excel c чистой рабочей книгой Книга 1.

УПРАЖНЕНИЕ 1. Вставка дополнительных листов в рабочую книгу

Для создания нашей рабочей книги нам потребуется 5 листов. Excel автоматически предлагает три листа. Нам нужно добавить два листа. Для этого:

·    Щелкните в строке меню пункт Вставка

·    В появившемся подменю щелкните Лист. Появится Лист 4

·    Вставьте Лист 5

УПРАЖНЕНИЕ 2: Потренируйтесь в выборе ячеек

·         Сделайте активной ячейку Е2, щелкнув мышью во 2 строке столбца Е.

·           Сделайте активной ячейку В6 с помощью клавиатуры управления курсором.

·           Сделайте активной ячейку Т30. Используйте для этого горизонтальные и вертикальные полосы прокрутки, чтобы эта ячейка была видна на экране, и щелкните мышью в этой ячейке.

·         Сделайте активной ячейку А1.

УПРАЖНЕНИЕ 3. Ввод заголовка таблицы

В ходе упражнения Вы начнете создавать первый лист рабочей книги - электронную таблицу, которая будет подсчитывать энергетическую ценность и сбалансированность завтрака. Итак: В ячейку F1 введитеЭнергетическая ценность завтрака. Завершить ввод можно 2 способами: нажать Enter или перейти в следующую ячейку (см. УПРАЖНЕНИЕ 1)

УПРАЖНЕНИЕ 4. Ввод текстовых строк и постоянных числовых значений в таблицу

·         Введите Наименование блюда в ячейку А2.

·         Введите Белки, на 100 г в ячейку В2.

·         Введите Жиры, на 100 г в ячейку С2.

·         Введите Углеводы, на 100 г в ячейку D2.

·         Введите Ккал, на 100 г в ячейку Е2.

·         Введите Вес, г в ячейку F2.

·         Введите  Белки, г в ячейку G2.

·         Введите Жиры, г в Н2

·         Введите Углеводы, г в ячейку I2.

·         Введите Энергетическая ценность, ккал в ячейку J2.

Отлично! Теперь Вы готовы к вводу строк в столбце А, в которых будут указаны названия готовых блюд, входящих в рацион, в столбце В указано количество ккал в 100 г продукта, в столбцах C, D, и Е указаны содержание белков, жиров и углеводов в 100 г продукта. Введите в таблицу соответствующие значения строк и столбцов.

Энергетическая ценность завтрака

      А                            B                      C             D            E

2.	Наименование продукта	Белки, на 100 г	Жиры, на100 г	Углеводы, на 100 г	Ккал, на100 г
3.	Завтрак
4.	Хлеб ржаной	5	1	42,5	204
5.	Хлеб пшеничный	6,7	0,7	50,3	240
6.	Капуста тушеная	2,1	5,2	10,4	100
7.	Каша гречневая	4,75	4,7	28,6	183
8.	Каша овсяная	3,85	5,65	19,7	149
9.	Сосиска	10,3	17,9	0,4	200
10.	Макароны с сыром	4,3	5,4	25	170
11.	Омлет	7,7	10,1	2,5	135
12.	Масло слив	0,4	78,5	0,5	734
13.	Сдоба	6,4	7,2	45,8	273
14.	Сахар	0	0	95,5	390
15.	Конфеты "Ассорти"	3,6	35,6	53,1	563

Форматирование ЭТ

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

УПРАЖНЕНИЕ 5. Форматирование заголовка таблицы

·  Щелкните в ячейке F1, чтобы сделать ее текущей.

·  Задайте полужирный шрифт, размер 12

·  Удерживая нажатой клавишу Shift, щелкните в ячейке J1, чтобы выделить диапазон ячеек F1:J1.

·  Щелкните по кнопке Объединить и поместить в центре. (Данная кнопка находится на панели инструментов форматирования).

Если вы все сделали правильно, заголовок таблицы разместится по центру между столбцами F и J.

УПРАЖНЕНИЕ 6. Регулировка ширины столбца с помощью команды Автоподбор ширины.

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

·  Выберите команду: Формат4Столбец4Автоподбор ширины.

Excel расширила столбец А настолько, чтобы полностью отобразить самый длинный текст в выделенной области ячеек.

УПРАЖНЕНИЕ 7. Переименование листа рабочей книги

·  Щелкните правой кнопкой по ярлычку текущего листа (Лист№)

·  В появившемся контекстном меню левой кнопкой щелкните Переименовать

·  Введите с клавиатуры Завтрак. Нажмите Enter

ПОВТОРИТЕ УПРАЖНЕНИЯ 3 -7 ДЛЯ 2, 3, 4 ЛИСТОВ РАБОЧЕЙ КНИГИ ДЛЯ РАСЧЕТА ЭНЕРГЕТИЧЕСКОЙ ЦЕННОСТИ ОБЕДА, ПОЛДНИКА И УЖИНА

Энергетическая ценность обеда

А                                 B             C                    D E

2.	Наименование продукта	Белки, на 100 г	Жиры, на 100 г	Углеводы, на 100 г	Ккал, на 100 г
3.	Обед
4.	Хлеб ржаной	5	1	42,5	204
5.	Хлеб пшеничный	6,7	0,7	50,3	240
6.	Бульон мясной	0,14	0	0,76	4
7.	Суп рисовый	0,64	0,07	6,4	30
8.	Борщ	0,7	3	4,1	47
9.	Суп молочный	2,61	2,97	10,8	82
10.	Котлеты	17,4	8,07	9,85	186
11.	Говядина тушеная	25,4	8,9	1,4	192
12.	Курица отварная	18,4	7,9	1,6	154
13.	Картофельное пюре	2	3,4	14,6	99
14.	Капуста тушеная	2,1	5,2	10,4	100
15.	Винегрет	1,2	4,7	13,1	102
16.	Салат из свеклы	0,96	4,7	12,1	97
17.	Салат из капусты	1,34	4,23	10,1	86
18.	Компот	0,06	0	17,4	71
19.	Масло слив	0,4	78,5	0,5	734
20.	Сдоба	6,4	7,2	45,8	273
21.	Сахар	0	0	95,5	390
22.	Конфеты "Ассорти"	3,6	35,6	53,1	563

                               Энергетическая ценность полдника

А                                 B             C                       D             E

2.	Наименование продукта	Белки, на 100 г	Жиры, на 100 г	Углеводы, на 100 г	Ккал, на100 г
3.	Полдник
4.	Хлеб ржаной	5	1	42,5	204
5.	Хлеб пшеничный	6,7	0,7	50,3	240
6.	Печенье	10,8	8,5	66,4	395
7.	Молоко	2,8	3,5	4,5	62
8.	Масло слив	0,4	78,5	0,5	734
9.	Сдоба	6,4	7,2	45,8	273
10.	Сахар	0	0	95,5	390
11.	Конфеты "Ассорти"	3,6	35,6	53,1	563

Энергетическая ценность ужина

А                                    B                    C                  D                       E

2.	Наименование продукта	Белки, на100 г	Жиры, на100 г	Углеводы, на 100 г	Ккал, на100 г
3.	Ужин
4.	Хлеб ржаной	5	1	42,5	204
5.	Хлеб пшеничный	6,7	0,7	50,3	240
6.	Печенье	10,8	8,5	66,4	395
7.	Курица отварная	18,4	7,9	1,6	154
8.	Рыба жареная	14	6,04	4,9	132
9.	Блинчики с творогом	10,2	11,5	34,5	285
10.	Картофельное пюре	2	3,4	14,6	99
11.	Капуста тушеная	2,1	5,2	10,4	100
12.	Винегрет	1,2	4,7	13,1	102
13.	Салат из свеклы	0,96	4,7	12,1	97
14.	Салат из капусты	1,34	4,23	10,1	86
15.	Каша гречневая	4,75	4,7	28,6	183
16.	Каша овсяная	3,85	5,65	19,7	149
17.	Макароны с сыром	4,3	5,4	25	170
18.	Вареники ленивые	11,2	11,8	21,1	238
19.	Омлет	7,7	10,1	2,5	135
20.	Масло слив	0,4	78,5	0,5	734
21.	Сдоба	6,4	7,2	45,8	273
22.	Сахар	0	0	95,5	390
23.	Конфеты "Ассорти"	3,6	35,6	53,1	563

УПРАЖНЕНИЕ 8. Ввод данных (переменных значений) в таблицу.

Как вы знаете, суточный калораж для студентов 17 - 18 лет должен составлять, примерно, »2600 ккал. Питание должно:

ü       покрывать суточные энергозатраты,

ü       быть полноценным (в организм должно поступать достаточное количество белков, жиров и углеводов) Для студентов 17 - 18 лет суточный рацион должен содержать белков »90 г, жиров »90 г, углеводов 360 г

ü       быть сбалансированным (соотношение белков, жиров и углеводов – 1 : 1 : 4 или по-другому белков должно быть »16,5%, жиров »16,5% и углеводов »67%), разнообразным.

ü       На завтрак взрослый человек должен потребить »20 - 25 % суточного рациона или »625 ккал, обед должен быть »40 – 45 % или 1000 - 1125 ккал, полдник - »5 - 10 % или » 125 - 250 ккал, ужин »20 - 25 % или »625 ккал.

Предлагаем пример суточного рациона питания для студента 17 - 18 лет. В столбце F, в соответствующих ячейках введите вес в граммах завтрака.

Завтрак ¨       Хлеб ржаной 50 г ¨       Хлеб пшеничный 50 г ¨       Каша гречневая 150 г ¨       Сосиска 80 г ¨       Сахар 10 г	Полдник ¨       Печенье 20 г ¨       Молоко 100 г
Обед ¨       Хлеб ржаной 100 г ¨       Хлеб пшеничный 50 г ¨       Борщ 200 г ¨       Говядина тушеная 100 г ¨       Капуста тушеная 250 г ¨       Компот 200 г	Ужин ¨       Хлеб ржаной 100 г ¨       Рыба жареная 150 г ¨       Картофельное пюре 200 г ¨       Салат из свеклы 100 г ¨       Конфеты «Ассорти» 10 г

 

УПРАЖНЕНИЕ 9. Ввод формул подсчета; белков, жиров, углеводов и энергетической ценности (калоража).

Необходимо рассчитать, сколько ккал содержит 50 г ржаного хлеба.

Для этого составляем пропорцию

100 г	-	204 ккал
50 г	-	Х ккал

Х=(50*204)/100=102

Следовательно, в 50 г ржаного хлеба содержится 102 ккал. Для того, чтобы не высчитывать вручную калораж каждого блюда заменим конкретные значения веса и ккал на 100 г на адреса соответствующих ячеек. Тогда наша формула будет иметь вид:

Х=(F4*E4)/100

Все формулы в электронных таблицах начинаются со знака =. Поэтому в ячейку J4 введите =(F4*E4)/100

Аналогично составляются формулы для подсчета белков, жиров и углеводов. Введите:

-          в ячейку G4 формулу =(F4*В4)/100

-          в ячейку Н4 формулу =(F4*С4)/100

-          в ячейку I4   формулу =(F4*D4)/100

УПРАЖНЕНИЕ 10. Копирование формул с помощью маркера заполнения.

Чтобы закончить создание формул подсчета белков, жиров, углеводов и калоража в нашей таблице, используем маркер заполнения для копирования формул из ячеек G4, H4, I4 и J4 в диапазон ячеек G4:J15.

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

·  Подведите и установите указатель мыши на крошечном квадратике в правом нижнем углу табличного курсора (указатель мыши превращается в небольшой черный крестик - Маркер заполнения).

·  Перетаскивайте его вниз до тех пор, пока рамка табличного курсора (имеющая светло-серый цвет) не охватит ячейки с G4 по J15 включительно. Когда вы отпустите кнопку мыши,Excel скопирует формулу из ячеекG4, Н4, I4, J4 во все ячейки столбцов G, H, I, J до 15-тых.

УПРАЖНЕНИЕ 11. Суммирование значений с помощью функции Автосумма

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

·  Щелкните на ячейке G3, чтобы сделать ее активной.

·  Щелкните на кнопке å, на панели инструментов. Excel вводит =СУММ().

·  Курсор находится в скобках. Введите G4:G15

·  Завершите ввод (Enter). В ячейке G3 отобразится результат.

·  Скопируйте, с помощью маркера заполнения формулу из ячейки G3 в диапазон ячеек G3:J3, для подсчета жиров, углеводов и энергетической ценности завтрака.

·  Завершите ввод (Enter).

УПРАЖНЕНИЕ 12. Графическое представление сбалансированности рациона с помощью мастера диаграмм.

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

·  Выделите диапазон ячеек G2:I3

·  Щелкните по кнопке Мастер диаграмм

·  В появившемся окне выберите тип диаграммы Круговая

·  Щелкните по кнопке Далее

·  Мастер диаграмм в текстовом поле «Диапазон» укажет выделенные вами ячейки, переключатель «Ряды в» установите в «строках»

·  Щелкните по кнопке Далее

·  На вкладке Заголовки в текстовом поле «Название диаграммы» введите «Сбалансированность завтрака»

·  На вкладке Подписи данных переключатель Подписи значений установите в позицию Доля

·  Щелкните по кнопке Далее

·  Поместим диаграмму на имеющемся листе

·  Щелкните по кнопке Готово

УПРАЖНЕНИЕ 13. Использование закрепленных областей в качестве заголовков рабочего листа

В этом упражнении вы примените команду Закрепить область для «замораживания» на экране заголовков строк и столбцов. Это называется созданием заголовков рабочего листа

·  Установите табличный курсор в ячейке, которая находится сразу под теми строками и справа от тех столбцов, которые должны постоянно отображаться на экране, независимо от того, куда вы прокручиваете рабочий лист. В нашем случае это ячейка - В4.

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

·  Щелкайте по кнопке прокрутки со стрелкой вправо на горизонтальной полосе прокрутки до тех пор, пока на экране не останутся столбцы F, G, H, I, J и диаграмма.

ПОВТОРИТЕ УПРАЖНЕНИЯ 8 - 13 ДЛЯ ЛИСТОВ ОБЕД, ПОЛДНИК И УЖИН РАБОЧЕЙ КНИГИ ДЛЯ  СОЗДАНИЯ ФОРМУЛ, ДИАГРАММ И ЗАГОЛОВКОВ РАБОЧЕГО ЛИСТА

УПРАЖНЕНИЕ 14. Создание итогового листа

·  Переименуйте последний лист рабочей книги Суточный рацион

·  На этом листе в ячейку А1 введите Энергетическая ценность суточного рациона

·  Введите  Белки, г в ячейку А2.

·  Введите Жиры, г в В2

·  Введите Углеводы, г в ячейку С2.

·  Введите Энергетическая ценность, ккал в ячейку D2.

УПРАЖНЕНИЕ 15 Суммирование значений из различных листов рабочей книги

·  В ячейку А3 введите =

·  Щелкните ярлычок листа Завтрак и щелкните в ячейке G3 (белки, г завтрака)

·  Введите +

·   Щелкните ярлычок листа Обед и щелкните в ячейке G3 (белки, г обеда)

·  Введите +

·   Щелкните ярлычок листа Полдник и щелкните в ячейке G3 (белки, г  полдника)

·  Введите +

·   Щелкните ярлычок листа Ужин и щелкните в ячейке G3 (белки, г ужина)

·  Нажмите Enter

·  Скопируйте с помощью маркера заполнения формулу из ячейки А3 в диапазон А3:D3

С ПОМОЩЬЮ МАСТЕРА ДИАГРАММ СОЗДАЙТЕ ДИАГРАММУ СБАЛАНСИРОВАННОСТИ СУТОЧНОГО РАЦИОНА

УПРАЖНЕНИЕ 16. Сохранение рабочей книги

·  Щелкните по Файл.

·  Щелкните по Сохранить.

·  Откройте свою папку: (Папка преподавателя на сетевом диске®Папка группы).

·  В поле Имя файла задайте имя.

·  Щелкните по кнопке Сохранить или нажмите Enter.

УПРАЖНЕНИЕ 17. Изучение работы таблицы. Подбор рациона питания для различных групп труда

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

УПРАЖНЕНИЕ 18. Сохранение рабочей книги под другим именем.

·  Щелкните по Файл.

·  Щелкните по Сохранить как.

·  Откройте свою папку (Папка преподавателя на сетевом диске ® Папка группы).

·  В поле Имя файла задайте имя

·  Щелкните по кнопке Сохранить или нажмите Enter.

УПРАЖНЕНИЕ 19. Выход из Excel

·  Выполните выход любым известным вам способом.

Вопросы для самоподготовки и контроля:

1.        Основные термины и понятия ЭТ

2.        Запуск Excel

3.        Способы перемещения табличного курсора

4.        Ввод данных в ячейку

5.        Функция Автосумма

6.        Копирование с помощью маркера заполнения

7.        Автоподбор ширины

8.        Форматирование заголовка таблицы

9.        Создание диаграмм

10.     Вставка листов в книгу

11.     Переименование рабочего листа

12.     Закрепление областей

13.     Суммирование значений из различных рабочих листов

14.     Сохранение и сохранение под другим именем рабочей книги

15.     Выход из Excel

Практическая работа №8

Тема: «Создание и редактирование таблиц в MS Excel».

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

Ход работы:

Запустите MS Excel: Пуск/Программы/Microsoft Excel.

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

Солнечная система.

Планета	Период обращения (в земных годах)	Расстояние (в млн.км.)	Диаметр (в ,тыс.км.)	Спутники
Меркурий	0,241	58	4,9	0
Венера	0,615	108	12,1	0
Земля	1	150	12,8	1
Марс	1,881	288	6,8	2
Юпитер	11,86	778	142,6	16
Сатурн	29,46	1426	120,2	17

Указания:

1)    В ячейке А1 напечатайте заголовок: Солнечная система.

2)    Расположите заголовок по центру относительно таблицы:

v Выделите диапазон ячеек А1 : Е1

v Щелкните по кнопке Объединить и поместить в центре на панели инструментов.

3)    В ячейку А2 внесите текст: Планета

4)    В диапазон А3 : А8 введите название планет.

5)    В ячейку В2 внесите текст: Период обращения ( в земных годах).

6)    В ячейку С2 внесите текст: Расстояние ( в млн. км.).

7)    В ячейку D2 внесите текст: Диаметр ( в тыс. км.).

8)    В ячейку Е2 внесите текст: Спутники.

9)    Выделите диапазон ячеек В2 : D2, выполните команду Формат/Ячейки на вкладке Выравнивание активизируйте флажок Переносить по словам, нажмите ОК.

10)           Заполните диапазон В3 : Е3 числами.

11)           Отформатируйте текст в таблице

v Шрифт в заголовке – Arial Cyr, размер 14, синий цвет, полужирное начертание.

v Шрифт в таблице – Times New Roman Cyr, размер 12, красный цвет, начертание полужирный курсив

12)           Текстовые данные выровняйте по центру.

13)           Задайте рамку для таблицы:

v Выделите таблицу ( без заголовка), выполните команду Формат/Ячейки, вкладка Граница. Установите цвет – синий, Тип линии – двойной и щелкните по кнопке Внешние, затем выберите Тип линии – пунктир и щелкните по кнопке Внутренние, нажмите ОК.

v Выделите диапазон ячеек А2 : Е2, выполните команду Формат/Ячейки вкладка Граница, щелкните оп кнопке с нижней границей в группе Отдельные.

14)           Задайте заливку для второй строки таблицы: Выполните команду Формат/Ячейки, вкладка Вид.

Задание №2. Создайте копию своей таблицы на 2 листе и для её оформления используйте готовый стиль.

Указание:

1) Выделите таблицу и выполните команду Правка/Копировать.

2) Перейдите на 2 лист, установите курсор в ячейку А1 и выполните команду Правка/Вставить.

3) Измените высоту строк и ширину столбцов, чтобы данные помещались в ячейку.

4) Выделите таблицу ( без заголовка) и выполните команду Формат/Автоформат. В группе Список форматов выберите Классический 3, нажмите ОК.

5) Сохраните документ.

Ввод и редактирование данных с помощью формы в MS Excel.

Задание №3. В первую таблицу добавьте новые данные.

Указания:

1) Перейдите на лист 1.

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

v Вставьте столбец Спутники и выполните команду Вставка/Столбцы.

               3) Добавьте информацию ещё о трёх планетах, используя форму:

v Установите курсор в любую ячейку таблицы.

v Выполните команду Данные/Форма (появится диалоговое окно, состоящие из трёх частей: слева располагаются названия столбцов, в середине – данные, справа – управляющие кнопки)

v Щёлкните по кнопке Добавить, заполните пустые поля новыми сведениями о планете Уран (см. таблицу). Перемещаться между полями можно с помощью клавишей TAB или щёлкать мышью. Затем нажмите Добавить.

v Аналогично введите сведения о планетах Нептун и Плутон.

v Нажмите кнопку Закрыть. В результате в таблице появятся новые данные.

           4) Используя форму, измените данные в таблице:

v Откройте форму ( Данные/Форма).

v Щёлкая по клавишам Далее и Назад найдите запись о планете Юпитер и исправьте количество спутников на 2, расстояние на 300, нажмите кнопку Добавить.

v Аналогично измените Период, Диаметр и число спутников у Меркурия.

v Закройте форму.

           5) Сохраните документ.

 (таблица)                                         Солнечная система.

Планета	Период обращения (в земных годах)	Расстояние (в млн.км.)	Диаметр (в тыс.км.)	Масса (*1024 кг)	Спутники
Меркурий	0,511	58	11,9	0,32	14
Венера	0,615	108	12,1	4,86	0
Земля	1	150	12,8	6	1
Марс	1,881	288	6,8	0,61	2
Юпитер	11,86	300	142,6	1906,98	2
Сатурн	29,46	1426	120,2	570,9	17
Уран	84,01	28,69	49	87,24	14
Нептун	164,8	4496	50,2	103,38	2
Плутон	247,7	5900	2,8	0,1	1

Сортировка данных в таблице (по одному столбцу).

Задание №4. Расположите планеты в порядке увеличения их массы:                    

Указания:

1)   Установите курсор в любую ячейку столбца Масса                                                                     

2)   Щелкните по кнопке на панели инструментов Сортировка по возрастанию.                      

Задание №5. Отсортируйте записи в таблице по расстоянию, начиная с самого большого.

Сортировка данных в таблице (по двум столбцам).

Задание №6.    Расположите названия планет в алфавитном порядке в зависимости от количества спутников:

Указания:

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

2)   Выполните команду Данные/Сортировка

3)   В раскрывающемся списке Сортировать по выберите Спутники, по возрастанию.

4)   В раскрывающемся списке Затем по выберите Планета, по возрастанию.

Фильтрация данных.

Автофильтр.

Задание №7.  Используя  автофильтр, осуществите  поиск планет, период  обращения которых не менее 10 и не более 100 земных лет.

Указания:

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

2)     Выполните команду Данные/Фильтр/Автофильтр (появятся маленькие кнопки в шапке таблицы)

3)          Щелкните по кнопке в столбце Период и выберите Условие.

4)    В раскрывающемся списке задайте условие: больше или равно   10   И   меньше или равно 100 (в результате останутся только 3 записи о планетах Юпитер, Уран, Сатурн)

Задание №8. Выведите информацию о планетах, названия которых начинаются на М.

Задание №9.   Осуществите поиск планет, масса которых не превышает 5 *10²⁴кг   и диаметр которых не менее 7 тыс. км. (должны остаться Меркурий и Венера).

Задание №10. Осуществите поиск планет, диаметр которых не меньше 50 тыс. км   и период обращения находится в диапазоне от 1 до 100 земных лет.

Задание №11. Найти планеты, которые находятся на расстоянии от Солнца не дальше, чем 100 млн. км, масса находится в интервале от 3*10²⁴до 500*10²⁴ , а количество спутников не менее 2.

Расширенный фильтр.

Планета

Марс

Юпитер

Сатурн

Плутон

Задание №12. Вывести информацию о планетах Марс, Сатурн, Плутон, Юпитер.

Указания:

1)  Введите условие с клавиатуры или скопируйте соответствующие ячейки из таблицы в диапазон А15:А19

2)     Установите курсор в таблицу, выполните команду Данные/Фильтр/Расширенный фильтр.

3)          В поле Исходный диапазон проверьте правильность указания диапазона таблицы $А$2:$F$11 при необходимости укажите его заново.

4)          В поле Диапазон условий укажите блок А15:А19 ( можно выделить мышкой).

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

Задание №13.   Используя встроенные функции   Ехсеl, найти минимальное расстояние, максимальный диаметр и общее число спутников.

Указания:

1)  Перейдите на 2 лист.

2)          В ячейку С10 нужно ввести формулу, позволяющую определять минимальное расстояние автоматически. Для этого можно использовать функцию МИН. В Ехсеl имеется множество различных функций, которые существенно упрощают расчеты.

v Установите курсор в ячейку С10, выполните команду Вставка/Функция или щелкните по кнопке Вставка функций на панели инструментов.

v В диалоговом окне Мастера функций в группе Категория выберите Статические, в группе Функция выберите МИН (используйте линейку прокрутки для нахождения данной функции), нажмите ОК.

v В следующем диалоговом окне в строке Число 1 проверьте правильность указания диапазона ячеек СЗ:С8, при необходимости исправьте его, нажмите ОК.

v В результате в ячейке С10 окажется число 58, а в строке формул: =МИН(СЗ:С8).

3)  Аналогично в ячейку E10 вставьте формулу, позволяющую найти максимальный диаметр.

•   Используйте функцию МАКС из категории Статистические.

4)  В ячейке Е10 подсчитайте общее число спутников:

•   Для вставки формулы можно пользоваться предыдущим способом, для этого используйте функцию СУММ из категории Математические. Но удобнее воспользоваться кнопкой Автосумма на панели инструментов Стандартная, выделите мышью диапазон ячеек ЕЗ:Е8 и нажмите ЕNТЕR.

5)           В ячейках С11, D11, Е11 сделайте соответствующие подписи: Минимальное расстояние, Максимальный диаметр, Количество спутников.

6)          Сохраните документ.

Практическая работа № 9

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

Цель занятия: в MS Excel  научиться применять возможность графического представления данных в виде диаграмм.

Ход работы:

Задание №1:   Создайте таблицу для начисления заработной платы работникам фирмы «ЗАО Товарищ». Сохраните созданный документ.

Расчет заработной платы работников ЗАО «Товарищ»

		Доходы			Налоги
№	ФИО	Оклад	Коэффициент	Полярная надбавка	Подоходный налог	Медицинское страхование	Пенсионный фонд	Всего начислено	Всего удержано	К выдаче
1	Петров А. В.	510
2	Сидоров Н.З.	595
3	Артемьева М.С.	640
4	СапожковаВ.В.	380
5	Ратынская С. Л.	490
6	Урусов П. Р.	700
7	Витальев Н.Н.	830
8	Русокров Г.Л.	1000
9	Девяткин А.А.	740
10	Ильина У.Ю.	640

Указания:

1)  В ячейку А2 введите заголовок: Расчет заработной платы работников ЗАО «Товарищ»

2)     Шапку таблицы расположите в диапазоне А5:К6

3)          Расположите текст в ячейках E6:J6 в две строки: выделите указанные ячейки, выполните команду Формат/Ячейки, вкладка Выравнивание. Активизируйте флажок Переносить по словам.

4)     Задайте вертикальное расположение текста в шапке таблицы:

v Выделите ячейки D6:J6, выполните команду Формат/Ячейки, вкладка Выравнивание.

v В группе Ориентация щелкните по верхнему ромбику над словом Надпись.

5)          Выровняйте заголовок по центру относительно таблицы.

6)          Заполните столбцы с №, ФИО, Окладом, а остальные столбцы рассчитайте по формулам:

v Коэффициент составляет 50% от оклада: установите курсор в ячейку D7, введите формулу на английском языке =С7*50% или =С7*0,5

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

v Аналогично рассчитайте Полярную надбавку, которая составляет 80% от оклада.

v В столбце Всего начислено подсчитайте доходы сотрудника (сумма всех доходов)

v Подоходный налог рассчитывается по формуле: 12% от начисленной суммы.

v В фонд медицинского страхования производится выплата в размере 3% от начисленной суммы.

v В пенсионный фонд производится выплата в размере 1% от начисленной суммы

v В столбце Всего удержано подсчитываются расходы сотрудника (сумма всех налогов)

v В столбце К выдаче рассчитывается денежная  сумма, выдаваемая работнику на руки (доходы-расходы)

7)          Отформатируйте таблицу: меню Формат/Ячейки (задайте параметры шрифта на вкладке Шрифт, выравнивание текста на вкладке Выравнивание, рамки на вкладке Граница,заливку на вкладке Вид)

8)          Переименуйте Лист1 в Расчет, меню Формат/Лист/Переименовать.

9)          Сохраните документ.

Задание №2: Постройте диаграмму, отражающую заработную плату всех сотрудников.

Указания:

1)         Выполните команду Вставка/Диаграмма или щелкните по кнопке Мастер диаграмм на панели инструментов Стандартная. На экране появится первое диалоговое окно мастера диаграмм, в котором необходимо указать тип диаграммы. В группе Тип выберите Гистограмма, в группе Вид -Объемный вариант обычной гистограммы (4-я по счету картинка), нажмите кнопку Далее.

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

v Нажмите клавишу СТRL и не отпуская ее выделите два диапазона ячеек: В7:В16  и К7:К16

v В результате в строке Диапазон появится ссылка на  лист  Расчет:
=Расчет!$В$7:$В$16;Расчет!$К$7:$К$16

v Активизируйте переключатель Ряды в: столбцах

v Если вы ошиблись при указании диапазона, очистите строку Диапазон и выделите его заново. Затем нажмите кнопку Далее.

3)  В третьем диалоговом окне необходимо указать параметры диаграммы:

v На вкладке заголовки в поле Название диаграммы напишите: Сумма к выдаче, в поле Ось X (категорий): ФИО сотрудников, в поле Ось Z (значений): в руб.

v На вкладке Легенда отключите флажок Добавить легенду

v На вкладке Подписи данных в группе Подписи значений активизируйте переключатель Значение, нажмите кнопку Далее.

4)  В четвертом диалоговом окне необходимо указать место для размещения диаграммы:

v Активизируйте переключатель Отдельном, нажмите кнопку Готово.

v В результате в рабочую книгу добавится один лист под названием Диаграмма 1 с построенной на нем диаграммой. Готовую диаграмму можно отредактировать.

5)  Сохраните документ.

Задание №3: Отредактируйте иаграмму.

Указания:

1)  Скопируйте лист Диаграмма1:

v Выполните правый щелчок на ярлыке листа Диаграмма1, на экране появится контекстное меню.

v Выберите пункт Переместить/Скопировать

v В появившемся диалоговом окне активизируйте флажок Создать копию, появится лист Диаграмма 1(2)

v Переименуйте его в Диаграмма 2.

2)  Перейдите на лист Диаграмма 2. Измените заголовок диаграммы на: Расчет заработной
платы сотрудников ЗАО «Товарищ»:

v Наведите указатель мыши на заголовок (появится всплывающая подсказка Заголовок диаграммы}

v Щелкните мышью на заголовке (вокруг названия появится рамка)

v Щелкните мышью внутри  рамки (появится текстовый курсор)

v Исправьте заголовок, щелкните мышью вне заголовка.

3)  Отформатируйте заголовок:

v Выполните двойной щелчок в области заголовка (появится диалоговое окно Формат названия диаграммы), установите шрифт Courier New Cyr, размер 20, цвет красный, начертание - полужирный курсив, нажмите ОК.

4)  Отформатируйте заголовок оси значений:

v Установите шрифт, размер, начертание заголовка по своему усмотрению.

v Разверните подпись по вертикали: в диалоговом окне Формат названия оси выберите вкладку Выравнивание, в группе Ориентация установите 90 градусов.

5)           Удалите название оси категорий (ФИО сотрудников): выполните правый щелчок по названию, из контекстного меню выберите команду Очистить.

6)          Отформатируйте ось значений:

v Установите указатель мыши на ось значений (появится всплывающая подсказка), выполните на ней двойной щелчок, появится диалоговое окно Формат оси

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

vНа вкладке Шкала в поле Цена основных делений напишите 500

vНа вкладке Шрифт установите параметры шрифта по своему усмотрению, нажмите Ок.

7)                     Отформатируйте ось категорий аналогично предыдущему пункту, устанавливая параметры на вкладках Вид и Шрифт.

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

9)         Отформатируйте подписи данных:

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

v Передвиньте подписи данных: установите указатель мыши на любую подпись, выполните два последовательных медленных щелчка (вокруг значения появится рамка), перетащите рамку с числом при помощи мыши.

10)Отформатируйте ряды данных:

v Выполните двойной щелчок на любом столбце (появится диалоговое окно Формат ряда данных)

v На вкладке Фигура выберите любую фигуру.

v На вкладке Вид в группах Граница и Заливка установите параметры по своему усмотрению (интересную заливку можно получить, если нажать на кнопку Способы заливки)

11)Сохраните документ.

Задание №4: Создайте таблицу учета выручки от продажи мороженого фирмы «ПИНГВИН», если известно, что фирма торгует по пяти округам города только в летние месяцы.

Продажа  мороженого фирмой ПИНГВИН в летний период.

Район/месяц	Июнь	Июль	Август	Всего по округу	% выручки по округу
Центральный	2500,65р.	1200,35р.	2400,50р.
Западный	2100,10р.	1001,00р.	2345,00р.
Южный	4000,68р.	3500,00р.	3409,00р.
Восточный	3100,00р.	2100,00р.	1090,30р.
Северный	980,00р.	700,00р.	1300,20р.

Всего за месяц			Общая выручка

                    
Указания;

1)          Перейдите на Лист 2.

2)     В ячейку АЗ введите текст: Продажа мороженого фирмой Пингвин

3)     В ячейку А4 введите: летний период.

4)  Расположите шапку таблицы начиная с ячейки А6 (вся таблица будет располагаться в диапазоне А6:F11)

5)       Заполните исходными данными столбцы: район/месяц, июнь, июль, август.

6)       Для диапазонов ячеек В7:Е11 и В13:Е13 задайте денежный формат: выделите указанные диапазоны, меню Формат/Ячейки, вкладка Число, в списке Числовые форматы выберите Денежный, в поле Число десятичных знаков введите 2.

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

8)        В   ячейку  В13   введите   формулу,   подсчитывающую   выручку   всех   округов   за   июнь, скопируйте данную формулу в ячейки С13:Е13.

9)        В ячейку В14 введите текст: Всего за месяц, в ячейку Е14: Общая выручка.

10)Заполните  столбец  %  выручки  по  округу,  используя  абсолютную  ссылку  на ячейку, содержащую общую выручку:

v В ячейку F7 введите формулу: =Е7/$Е$13 (знак $ указывает на абсолютную адресацию)

v Используя автозаполнение, скопируйте формулу в остальные ячейки.

11)  Задайте процентный формат: выполните команду Формат/Ячейки, вкладка Число, в группе Числовые форматы выберите Процентный, в поле Число десятичных знаков введите 2.

12)  Отформатируйте  таблицу  (задайте   параметры   шрифта,   выравнивание  текста,  рамки  и заливку таблицы)

13) Заголовки расположите по центру относительно таблицы.

14) Вставьте любой рисунок: меню Вставка/Рисунок/Автофигуры.

15) Сохраните документ.

Задание №5:    Постройте диаграмму, отображающую вклад каждого округа в общую выручку.

Указания:

1)    Вызовите мастера диаграмм.

2)    Выберите объемный вариант круговой диаграммы.

3)     В источнике данных укажите 2 диапазона: зажмите клавишу СТRL и не отпуская ее выделите два диапазона ячеек А 7:А11 и F7:F11

4)         На третьем шаге диаграммы никаких данных не указывайте

5)          Для размещения диаграммы выберите имеющийся лист. В итоге на экране появится круговая диаграмма с легендой.

6)         Переместите диаграмму ниже таблицы и измените ее размер с помощью маркеров.

7)         Отредактируйте готовую диаграмму следующим образом:

v Вставьте заголовок диаграммы:  выделите диаграмму щелчком мыши и выполните команду Диаграмма/Параметры диаграммы.

v На вкладке Заголовки в строке Название диаграммы введите текст: Доля выручки каждого района.

v На вкладке Легенда в группе Размещение активизируйте переключатель Внизу.

v На вкладке Подписи данных в группе Подписи значений активизируйте переключатель Доля,нажмите ОК.

v Выведите сотые доли для подписей данных.

8)  Отформатируйте элементы диаграммы:

v Задайте параметры шрифта для заголовка диаграммы, подписей данных, легенды.

v Измените заливку и границу секторов диаграммы.

9)  Сохраните документ.                     

Задание №6: Подготовьте таблицу и диаграмму к печати.

Указания:

1)           Выполните команду Файл/Предварительный просмотр, щелкните по кнопке Страница (появится диалоговое окно Параметры страницы)

2)          На вкладке Поля задайте Верхнее и Нижнее поля по 2 см, Левое и Правое - по 1 см, в группе Центрировать на странице активизируйте флажок Горизонтально.

3)          На вкладке Колонтитулы щелкните по кнопке Создать верхний колонтитул, перейдите в окно Справа и напишите Отчет 1, нажмите ОК. В списке Нижний колонтитул выберите Страница 1.

4)          На вкладке Лист в группе Печать отключите флажок Сетка, нажмите ОК.

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

Задание №7:    Создайте самостоятельно таблицу «Производство бумаги» и постройте линейчатую диаграмму по данным таблицы.

                            Производство бумаги

                                        На душу населения, кг.

Страна	1970 г	1980 г.	1989 г.
Швеция	415	515	653
Канада	453	459	534
Норвегия	343	320	410
Австрия	118	176	308
США	112	126	145
Япония	69	90	127
Франция	71	86	113
Испания	27	61	80

Практическая работа №10

Тема: «Связь таблиц в MS Excel».

Цель занятия: научиться осуществлять связывание листов электронной книги.

Ход работы:

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

Задание №1: На листе 1 создайте и оформите таблицу по образцу (см. таблицу, диапазон А1:L1)

Указания:

1)  Для чисел содержащих даты проведения занятий задайте формат Дата:

v Выделите диапазон С2:К2, выполните команду Формат/Ячейки, вкладка  Число, в списке  Числовые форматы выберите Дата, установите любой тип.

2)  Оценки за 1-ю четверть вычислите по формуле, как среднее арифметическое текущих оценок:

v Вставьте в ячейку L3 формулу с использованием встроенной функции СРЗНАЧ (выполните команду Вставка/Функция,   категория   Статистические,   выберите   СРЗНАЧ  и  укажите   диапазон   ячеек   с
текущими оценками Арбузова Артема).

v Скопируйте формулу в остальные ячейки.

v Выполните округление полученных значений (выделите диапазон ячеек с оценками за 1-ю четверть, выполните команду Формат/Ячейки, вкладка Число, формат Числовой, число десятичных знаков 0)

3)  Данные  в  таблице  отцентрируйте.   Оформите  таблицу,   задав  ей  границы  и  заливку.
Сохраните таблицу.

Литература
Фамилия	2.09	9.09	16.09	23.09	30.09	7.10	14.10	21.10	28.10	1 четверть
Арбузов Артем		3	4			о		4		4
Иванов Игорь	5		3		5			5		5
Орлова Оля		4				4	4			4
Попов Олег		5		5	5			4	4	5
Русланова Галя			4			4				4

Задание №2: Создайте аналогичные листы для алгебры и биологии.

Указания:

1)  Скопируйте Лист 1:

v Выполните команду Правка - Переместить/Скопировать лист, в группе Перед листом выберите Лист2, активируйте флажок Создать копию, нажмите Ок. После выполнения команды появится Лист 1(2).

2)  Выполните аналогичные действия, чтобы появился Лист 1(3).

Задание №3: Переименуйте листы: Лист1 в Литература, Лист 1(2) в Алгебра, Лист 1(3) в Биология:

Указания:

1)  Сделать текущим Лист 1 (щелкнуть по ярлыку Лист 1)

2)         Выполнить команду Формат /Лист/Переименовать (ярлык листа выделится цветом, ввести слово Литература, нажать ЕNТЕR)

3)         Аналогично переименуйте другие листы.

Задание №4: На листах Алгебра и Биология измените заголовки, даты и текущие оценки.

Связь рабочих листов

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

Задание №5: На листе 2 создайте ведомость итоговых оценок за первую четверть:

Указания:

1) Переименуйте Лист 2 в 1 четверть.

  2) Заполните таблицу ссылками на соответствующие ячейки других листов. В ячейку А2 занесите формулу = Литература!А2, для этого

v В ячейке А2 поставьте знак =

v Перейдите на Лист Литература, щелкнув по ярлыку

v Щелкните  в  ячейке А2  и   нажмите     ЕNTЕR.   (Обратите  внимание,   что  формула автоматически появляется в строке формул. Литература! - ссылка на другой лист,  символ  ! обязателен. А2 -относительный адрес ячейки на листе Литература)

v Скопируйте формулу на последующие пять ячеек столбца А.

v Скопируйте   формулу   из   ячейки  А2   в   ячейку   В2   (в   строке   формул   появится   формула  =Литература! В 2)

v Скопируйте формулу из ячейки В2 в последующие ячейки столбца В.

3)         В ячейки С2:Е2 впишите названия предметов.

4)          В ячейку СЗ внесите формулу =Литература!L3 (аналогично предыдущему заданию) и скопируйте   формулу   на  последующие   четыре  ячейки   столбца.   В   результате   столбец заполнится оценками за первую четверть по литературе.

Таким образом, установлена связь между листом 1четверть илистом Литература.

5)          Аналогично заполните остальные столбцы таблицы.

Ведомость итоговых оценок за 1-ю четверть.
Фамилия	Литература	Алгебра	Биология
Арбузов Артем	4	3	4
Иванов Игорь	5	4	5
Орлова Оля	4	4	4
Попов Олег	5	5	5
Русланова Галя	4	4	3

Задание №6: Удалите листы, которые не будут использоваться в рабочей книге.

Указания:

v Сделайте текущим Лист 3, щелкнув по его ярлыку

v Выполните команду Правка/Удалить лист, прочитайте появившееся предупреждение.

v Удалите все ненужные листы.

Работа с несколькими окнами.

Для удобства работы с книгой, состоящей из нескольких листов можно открыть несколько окон и одновременно отслеживать на экране разные области документа.

Задание№7: Проверьте работоспособность связанных листов в книге.

Указания:

1)    Откройте для просмотра еще одно окно: выполните команду Окно/Новое.

2)    Выполните команду Окно/Расположить, активируйте переключатель Сверху вниз.

3)    В  верхнем окне установите текущим лист Литература,  а в нижнем - 1  четверть. Активным всегда является только одно из окон. Для активизации другого окна нужно щелкнуть по нему мышью.

4)    На листе Литература Руслановой Галине исправьте или добавьте текущие оценки, чтобы четвертная оценка изменилась. (Обратите внимание! Изменилась итоговая оценка Руслановой Галины за 1-ю четверть как на листе Литература, так и на листе 1 четверть)

5)    Аналогично исправьте оценки Арбузову.

6)    Раскройте еще два окна и разместите в них листы Алгебра и Биология, расположите их рядом.

Задание №8:   Закройте все окна, кроме одного. Разверните это окно на весь экран. Сделайте в нем текущий лист Литература.

Задание №9:   Подготовьте таблицу к печати, установив наиболее удобный вариант вывода таблицы на печать. Сохраните документ.