Главная » Интернет » Лабораторные работы по excel. Лабораторные работы по EXCEL Лабораторные работы по теме excel

Лабораторные работы по excel. Лабораторные работы по EXCEL Лабораторные работы по теме excel

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

Информатика, кибернетика и программирование

Заполните диапазон А1:F10 данными по образцу приведенному на рис. Рис.а Рис. После преобразования в таблицу диапазон представлен на рис.

Лабораторные работы в MS Excel 2007

(часть 2 основная самостоятельная)

Задание № 1. Таблицы MS Excel 2007. 2

Задание № 2. Условное форматирование. 3

Задание № 3. Организация таблиц. 5

Задание № 4. Функции. 7

Задание № 5. Диаграммы. 11

Задание № 1. Таблицы MS Excel 2007.

Цель : Знакомство с возможностями таблиц - списков MS Excel

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

1 . Заполните диапазон А1: F 10 данными по образцу, приведенному на рис.2.2.а, или воспользуйтесь результатами предыдущего занятия и сохраните созданный файл.

1.1. Озаглавьте столбцы.

1.2. Заполните диапазон A 2: D 10.

1.3. Формулы в диапазон E 2: F 10 вводить не надо.

1.4. Одну из строк диапазона сделайте дублирующей любую другую строку диапазона.


Рис.2.2.а	Рис.2.2.б

2 . Преобразуйте диапазон в таблицу.

2.1. Установите курсор внутрь диапазона.

2.2. Выполните команду Вставка Таблицы Таблица и в диалоговом окне Создание таблицы проверьте расположение данных таблицы и нажмите ОК.

После преобразования в таблицу диапазон представлен на рис.2.2.б.

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

3.1. Убедитесь в возможности прокрутки строк таблицы при сохранении на экране заголовков столбцов таблицы.

3.2. Воспользуйтесь командой Сервис Удалить дубликаты и проследите за результатом.

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

3.4. Воспользуйтесь командой Стили таблиц Экспресс-стили и примените один из них.

3.5. Удалите из таблицы одну из строк.

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

4 . Познакомьтесь с особенностями ввода формул в таблицу.

4.1. Добавьте в таблицу еще один столбец справа от столбца Стоимость и озаглавьте его Стоимость 1 .

4.2. В произвольную ячейку столбца Стоимость введите вручную формулу, обеспечивающую умножение количества продукции на ее цену, например, в ячейку Е6 может быть введена формула = C 6* D 6. Обратите внимание на то, что формула распространилась на все остальные ячейки столбца таблицы.

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

Убедитесь в том, что в результате во всех ячейках столбца Стоимость 1 будет записана одинаковая формула =[Количество]*[Цена].

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

4.4. Дайте имя ячейке А15, в которой находится коэффициент, влияющий на комиссионный сбор, например, komiss . Для этого выберите команду Формулы Определенные имена Присвоить имя, предварительно активизируйте ячейку А15 . Заполните формулами столбец Комисс. сбор, используя Автозаполнение формул.

Познакомьтесь с управлением именами с помощью Диспетчера имен . Активизируйте его командой Формулы Определенные имена Диспетчер имен.

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

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

6.1. Отсортируйте таблицу по наименованию продукции (в алфавитном порядке).

6.2. Отсортируйте таблицу в порядке убывания цены на продукцию.

6.3. С помощью фильтрации найдите данные таблицы для бетона и дверей.

6.4. Рассмотрите возможности Текстовых , Числовых фильтров и Фильтров по дате (добавьте в конец таблицы столбец с датами поступления товаров на склад).

Задание № 2. Условное форматирование.

Цель : Знакомство с возможностями условного форматирования таблиц.

Темы: Создание и использование правил условного форматирования.

1. Создайте таблицу, приведенную на рис.4.5.

1.1. Примените к диапазону В3:В14 условное форматирование с помощью набора значков «три сигнала светофора без обрамления», а к диапазону С3:С14 - «пять четвертей».

1.1.1. Активизируйте команду Главная Стили Условное форматирование Наборы значков .

1.1.2. Выберите команду Управление правилами и перейдите в диалоговое окно Диспетчер правил условного форматирования . Ознакомьтесь с возможностями данного окна.

1.2. Создайте правило условного форматирования на основе формулы . Отформатируйте только те значения диапазона В3:В14, которые больше 40%, выделив их красной заливкой. Для этого активизируйте команду Главная Стили Условное форматирование Создать правило . В диалоговом окне Создание правила форматирования выберите Использовать формулу и введите формулу =В3>$А$16. Перейдя в диалоговое окно Формат ячеек , установите нужный формат. Повторите указанные действия для диапазона С3:С14 и порога, записанного в ячейке А17.

Рис.4.5

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

2.1. С помощью условного форматирования определите повторяющиеся значения в диапазоне с фамилиями.

2.2. Для диапазона В2:В14 выделите значения, превышающие два заказа и значения, равные одному заказу.

2.3. Для диапазона С2:С14 выделите суммы заказов, выше среднего значения и ниже среднего , а также выделите четыре наибольших сумм заказов.

2.4. Вставьте новый столбец справа от столбца С и скопируйте в него столбец сумм заказов, выровняйте значения по правому краю и увеличьте ширину столбца. Примените условное форматирование Гистограммы .

2.5. К диапазону Курьер примените условное форматирование Текст содержит и выделите значение Гермес.

Рис.4.6

3. Предъявите результаты преподавателю.

Задание № 3. Организация таблиц.

Цель : Знакомство с организацией вычислений в таблицах.

Темы: Работа с группами листов. Использование «формулы массива». «Автовычисление», «Автоформатирование». Влияющие и зависимые ячейки.

1 . Пользуясь методом группового заполнения листов, создайте на трех листах нового документа таблицу, приведенную на рис.5.1, введя данные в диапазон В4: F 8. Дайте листам имена "Таб1", "Таб2", "Таб3".

2 . Научитесь использовать различные приемы заполнения ячеек формулами.

2.1. В диапазоне G 4: G 8 запишите формулы для вычисления суммарной нагрузки по группам , пользуясь формулой массива .

2.2. В диапазоне В10: F 10 запишите формулы для вычисления суммарной нагрузки по видам нагрузки, пользуясь буфером обмена (ввести формулу, вычисляющую суммарную нагрузку по лекциям в ячейку B 10, затем воспользоваться командами Главная Буфер обмена Копировать и Главная Буфер обмена Вставить , предварительно выделив диапазон вставки).

Рис.5.1

2.3. Запишите формулу для суммирования нагрузки по строкам в ячейку G 9.

2.4. Запишите формулу для суммирования нагрузки по столбцам в ячейку G 10.

2.5. Запишите формулу для вычисления процентного содержания нагрузки для группы ЕС61-63 в общей сумме часов (ячейка H 4).

2.6. Скопируйте данную формулу в диапазон H 5: H 8, пользуясь автозаполнением .

2.8. Запишите формулу для вычисления процентного содержания лекционной нагрузки в общей сумме часов (ячейка В11).

2.9. Заполните аналогичными формулами диапазон C 11: F 11, пользуясь командой Главная Редактирование Заполнить вправо .

3 . Пользуясь автовычислением , определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксируйте результаты.

4 . Активизируйте режим ручного пересчета формул (Office Параметры Excel ).

4.1. Несколько раз измените значения в таблице и выполните ручной пересчет.

5 . Отформатируйте таблицу на листе "Таб2" по образцу, представленному на рис.5.2, обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках (H 4: H 8 и В11: F 11).

5.1. Заголовки столбцов оформите с использованием непосредственного форматирования.

5.2. Для форматирования ячеек А10:А11 используйте копирование формата, созданного в п.5.1.

5.3. Отформатируйте таблицу на листе "Таб3", пользуясь функцией автоформатирования .

Рис.5.2

6 . Пользуясь командой Формулы Зависимости формул , выявите влияющие и зависимые ячейки для ячейки G 9 .

7 . Пользуясь "объемной" формулой =СУММ(Таб1:Таб3! G 9), вычислите сумму значений в клетках G 9 трех листов и зафиксируйте полученный результат в клетке G 15 листа "Таб1".

8 . Пользуясь командой Главная Буфер обмена Вставить Специальная вставка , уменьшите значения в диапазоне B 10: F 10 в четыре раза.

9 . Реализуйте подсчет суммы значений с последовательным накоплением сумм в столбце Накопленные суммы таблицы, приведенной на рис.5.3. Сумма с накоплением для ячейки С2 это продажи за январь, для С3 продажи за январь и февраль, для С4 продажи за январь, февраль и март и т.д. Для осуществления этого алгоритма примените необходимую адресацию в формуле =сумм(В2:В2) , помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.

Рис.5.3

Задание № 4. Функции.

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

Темы: Математические, статистические и логические функции. Функции даты и времени. Функции ссылки и массива. Текстовые функции. Функции для финансовых расчетов.

1 . Научитесь пользоваться математическими и статистическими функциями.

1.1.Создайте таблицу, приведенную на рис.6.1.

Рис.6.1

1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.

1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.

2 . Научитесь пользоваться логическими функциями.

2.1. Активизируйте второй лист созданной книги.

2.2. Введите таблицу, приведенную на рис.6.2.

2.3. В клетку С2 введите формулу, по которой будет вычислена скидк а и скопируйте ее в диапазон С3:С6:

если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,
в противном случае - 10%.

2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:

если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,
в противном случае - 2%.

Рис.6.2

2.5. Повторите п.2.3 для следующих условий:

если стоимость товара <2000, то скидка составляет 5% от стоимости товара,
если стоимость товара >5000, то скидка составляет 15% от стоимости товара,
в противном случае - 10%.

2.6. В клетку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.

2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.

Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:

Проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:

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

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

3.1. Активизируйте третий лист книги Имя_6_1.

3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату.

3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.

3.4. В клетку С5 запишите функцию ВЫБОР, позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...).

3.5. В клетку С6 запишите аналогичную функцию для даты, введенной в клетку С3.

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

РАЗНДАТ(С10;СЕГОДНЯ();"y")

3.7. Представьте текущее время , используя функции ТДАТА() и СЕГОДНЯ().

3.8. Поместите в соседние ячейки текущую дату и время и дату и время, отстоящую от текущей на трое суток. Найдите количество часов и минут между этими датами, пользуясь форматом [ч]:мм:сс и Общим форматом, а также форматом 13:30 . Зафиксируйте результаты и объясните различие.

3.9. Определите номер текущей недели и выведите сообщение:

"Сейчас идет № недели неделя".

3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.

3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.

3.10.2. В клетку В8 запишите функцию, дающую ответ на вопрос: "Какую стипендию в n -м семестре получил m -й студент?" Значения n -го семестра и фамилия m -го студента должны быть введены в клетки А8 и А9. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.

Рис.6.3

4 . Научитесь пользоваться статистическими функциями
РАНГ и ПРЕДСКАЗАНИЕ.

4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.

4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов.

4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.

Рис.6.4

5. Научитесь использовать текстовые функции.

5.1. Используйте формулу

="Сегодня "&ТЕКСТ(СЕГОДНЯ();"ДДДД ДД ММММ ГГГГ \г\.")

Проанализируйте полученный результат и измените аргумент функции ТЕКСТ, применяющий формат.

5.2. Для данных таблицы, приведенной на рис.6.5, используйте функцию ТЕКСТ для получения информации, идентичной записи в ячейке В6. В ячейке В5 текст «Доход равен» и число из ячейки В3 объедините с помощью конкатенации: «Доход равен » & В3. (Обратите внимание, что число при этом не форматируется ).

Рис.6.5

6. Научитесь пользоваться функциями для финансовых расчетов.

6 . 1. Вычислите объем ежемесячных выплат по ссуде, взятой на на срок 4 года, размер ссуды 70 000 руб., процентная ставка составляет 6% годовых. Для вычислений используйте функцию ПЛТ.

6 . 2. Вычислите общее количество выплат по ссуде размером 70 000 руб. Ссуда взята под 6% годовых. Объем ежемесячных выплат по ссуде 1 643,95 руб. Для вычислений используйте функцию КПЕР.

6.3. Вычислите объем ссуды, которую можно получить на 4 года под 6% годовых, если объем выплат не превышает 1 643,95 руб. Для вычислений используйте функцию ПС.

6.4. Вычислите основную часть выплат по ссуде за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ОСПЛТ.

6.5. Вычислите часть выплат по ссуде, которая идет на выплату процентов за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ПРПЛТ. Просуммируйте результаты вычислений функций ОСПЛТ и ПРПЛТ за соответствующие периоды и сделайте выводы.

7 . Предъявите результаты работы преподавателю.

Задание № 5. Диаграммы.

Цель : Знакомство с графическим представлением табличных данных в MS Excel.

Темы: Работа с диаграммами. Использование основных типов диаграмм. Создание и редактирование диаграмм.

1 . Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.

Рис.7.1

2 . Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе.

2.1 Выделите рабочий диапазон таблицы А4: G 6, и нажмите клавишу F 11 для быстрого построения гистограммы на отдельном листе.

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

2.3. Используя команду Работа с диаграммами Конструктор Данные Строка/столбец , измените ориентацию рядов диаграммы, затем верните диаграмму к прежнему виду.

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

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

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

2.7. Постройте на рабочем поле первого листа аналогичную гистограмму. Обратите внимание на команду Работа с диаграммами Конструктор Расположение , которая позволит расположить диаграмму на отдельном листе или непосредственно в текущем.

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

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

3.1.Постройте диаграмму с областями (Area ).

3.2.Постройте линейчатую диаграмму (Bar).

3.3.Постройте диаграмму типа график (Line).

3.4.Постройте круговую диаграмму для фактических показателей (Pie).

3.5.Постройте кольцевую диаграмму (Doughnut ).

3.6.Постройте лепестковую диаграмму - "Радар" (Radar).

3.7.Постройте точечную диаграмму (XY).

3.8.Постройте объемную круговую диаграмму плановых показателей (3-D_Pie).

3.9.Постройте объемную гистограмму (3-D_Column).

3.10.Постройте объемную диаграмму с областями (3-D_Area).

4 . Научитесь редактировать диаграммы 2 .

4.1. В диаграмме "График" замените тип диаграммы для данных, обозначающих "План", на круговую и назовите лист "Line_Pie".

4.2. Отредактируйте круговую диаграмму, созданную на листе "Pie", так, как показано на рис.7.2.

4.3. Отредактируйте линейные графики так, как показано на рис.7.3.

Рис.7.2 Рис.7.3

4.4. Научитесь редактировать объемные диаграммы.

4.4.1. Установите "поворот" диаграммы вокруг оси Z для просмотра:

фронтально расположенных рядов (угол 0 о );

под углом в 30 о ;

под углом в 180 о ;

4.4.2. Измените перспективу, сужая и расширяя поле зрения.

4.4.3. Измените порядок рядов, представленных в диаграмме.

5 . Предъявите результаты преподавателю.

2 Оформление надписи "показатели производства" на рис.7.2 производится факультативно.

А также другие работы, которые могут Вас заинтересовать
85288.		Лицарський турнір	763.5 KB
	6 грудня у календарі позначено як День Збройних сил України. І вже стало традицією вітати у цей день усіх чоловіків, хлопчиків. Напевне, цим жінки хочуть зайвий раз підкреслити у чоловіків риси, як мужність, сміливість. Щиросердя, шляхетність.
85289.		Турнір Веселих інформатиків	220 KB
	Мета: розвиток стійкого інтересу до інформатики; формування творчої особистості; формування комунікаційної компетенції; виховання поваги до суперника, стійкості, волі до перемоги, спритності; повторення й закріплення основного матеріалу в нестандартній формі...
85290.		Різноманітність тварин у природі	62.5 KB
	Формувати елементарні поняття риби земноводні плазуни; уявлення про істотні ознаки різних груп тварин. Виховувати пізнавальний інтерес до вивчення тварин прагнення до самоствердження у поєднанні з толерантним ставленням до інших потребу у збереженні природи.
85291.		У царстві рослин. Дерева, кущі, трави. Зовнішня будова рослин	69.5 KB
	Ознайомити з функціональним призначенням органів рослин, показати пристосування рослин для поширення плодів і насіння; розвивати спостережливість, увагу; виховувати бережливе ставлення до природи, любов до рідного краю, почуття прекрасного в природі.
85292.		У царстві рослин. Я і Україна	135 KB
	Мета: формування ключових компетентностей: вміння вчитися – самоорганізовуватися до навчальної діяльності у взаємодії; загальнокультурної – дотримуватися норм мовленнєвої культури, зв’язно висловлюватися в контексті змісту; соціальної – проектувати стратегії своєї поведінки з урахуванням потреб...
85294.		Свято в королівстві Ввічливості (лицарський турнір)	82 KB
	Запрошуємо Вас на наше свято. Відбудеться воно в незвичайній країні..., країні – добрих і ввічливих людей. Є в тій країні Королівство гарних манер або королівство Ввічливості. Правлять королівством їхні величності Король та Королева. А зрештою – побачите самі!
85295.		Руководство по защите от пыли при добыче и переработке полезных ископаемых	12.46 MB
	Руководство было написано группой специалистов по технике безопасности, охране труда, профессиональным заболеваниям, и инженерами (перечислены ниже) для того, чтобы собрать и представить проверенные технологии и методы снижения воздействия пыли на людей, используемые на всех стадиях добычи и переработки минеральных полезных ископаемых.
85296.		Фольклорная арт-терапия	39.8 KB
	Несомненную привлекательность арттерапии в глазах современного человека пользующегося в основном вербальным каналом коммуникации составляет то что она использует язык визуальной и пластической экспрессии. Это делает ее незаменимым инструментом для исследования и гармонизации тех сторон внутреннего мира человека для выражения которых слова малопригодны. С развитием арттерапии связываются надежды на создание такой гуманной синтетической методологии которая в равной мере учитывала бы достижения научной мысли и опыт искусства интеллект...

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

Упражнение 1

Введение основных понятий, связанных с работой электронных таблиц Excel.

1. Запустите программу Microsoft Excel, любым, известным вам способом. Внимательно рассмотрите окно программы Microsoft Excel. Первый взгляд на горизонтальное меню и панели инструментов несколько успокаивает, так как многие пункта горизонтального меню и кнопки панелей инструментов совпадают с пунктами меню и кнопками окна редактора Word.

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

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

2. Для того, чтобы ввести текст в одну из ячеек таблицы, необходимо ее выделить и сразу же (не дожидаясь появления столь необходимого нам в процессоре Word текстового курсора) “писать”.

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

Зафиксировать данные молено одним из способов:

нажать клавишу (Enter};

щелкнуть мышью по другой ячейке,

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

Зафиксируйте введенные вами данные.

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

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

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

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

Каков заголовок этого столбца?

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

Какой заголовок имеет эта строка?

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

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

Выделите ячейку D5; F2; А16.

Какой адрес имеет ячейка, содержащая день недели?

5. Давайте представим, что в ячейку, содержащую день недели нужно дописать еще и часть суток. Выделите ячейку, содержащую день недели, введите с клавиатуры название текущей части суток, например, "утро" и зафиксируйте данные, нажав клавишу { Enter }.

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

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

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

рис.1. 1.

вторник, утро

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

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

Теперь видна только та часть ваших данных, которая помещается в ячейке (рис. 1.2). Как просмотреть всю запись? И опять к вам на помощь придет Строка Формул. Именно в ней можно увидеть все содержимое выделенной ячейки.

рис. 1. 2.

вторник, ут

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

внести изменения в содержимое выделенной ячейки;

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

6. Как увеличить ширину столбца для того, чтобы в ячейке одновременно были видны и день недели, и часть суток?

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

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

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

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

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

Выделите блок ячеек, начав с ячейки А1 и закончив ячейкой, содержащей "сегодня".

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

Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке.

8. Каким образом удалить содержимое ячейки? Для этого достаточно выделить ячейку (или блок ячеек) и нажать клавишу {Delete} или воспользоваться командой горизонтального меню Правка  Очистить.

Удалите все свои записи.

Упражнение 2

Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних

индексов.

Составим таблицу, вычисляющую n-й член и сумму арифметической прогрессии.

Для начала напомним формулу n-го члена арифметической прогрессии:

a n =a 1 +d(n-l)

и формулу суммы п первых членов арифметической прогрессии:

S n =(a 1 + a n )* n /2, где a 1 - первый член прогрессии, a d - разность арифметической прогрессии.

На рис. 1.3 представлена таблица для вычисления n-го члена и суммы арифметической прогрессии, первый член которой равен -2, а разность равна 0,725.

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

Вычисление n -го члена и суммы арифметической прогрессии
	a n	S n

Рис. 1.3.

Выполнение упражнения можно разложить по следующим этапам.

Выделите ячейку А1 и введите в нее заголовок таблицы "Вычисление n-го члена и суммы арифметической прогрессии". Заголовок будет размещен в одну строчку и займет несколько ячеек правее А1.

Сформатируйте строку заголовков таблицы. В ячейку A3 введите "d", в ячейку ВЗ - "n", в СЗ - "a n ". в D3 - "S n ".

Для набора нижних индексов воспользуйтесь командой Формат  Ячейки..., выберите вкладку Шрифт и активизируйте переключатель Подстрочный в группе переключателей Эффекты .

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

Строка-заголовок вашей таблицы оформлена. Можете приступить к заполнению.

В ячейку А4 введите величину разности арифметической прогрессии (в нашем примере это 0,725).

Далее нужно заполнить ряд нижних ячеек таким же числом. Набирать в каждой ячейке одно и то же число неинтересно и нерационально. В редакторах Paintbrush и Word мы пользовались приемом копировать-вставить. Excel позволяет еще больше упростить процедуру заполнения ячеек одинаковыми данными.

Выделите ячейку А4, в которой размещена разность арифметической прогрессии. Выделенная ячейка окаймлена рамкой, в правом нижнем углу которой есть маленький черный квадрат -маркер заполнения.

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

Заполните таким образом значением разности арифметической прогрессии еще девять ячеек ниже ячейки А4.

В следующем столбце размещена последовательность чисел от 1 до 10.

И опять нам поможет заполнить ряд маркер заполнения. Введите в ячейку В4 число 1, в ячейку В5 число 2, выделите обе эти ячейки и, ухватившись за маркер заполнения, протяните его вниз.

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

Маркер заполнения можно "протаскивать" не только вниз, но и вверх, влево или вправо, в этих же направлениях распространится и заполнение. Элементом заполнения может быть не только формула или число, но и текст.

Можно ввести в ячейку "январь" и, заполнив ряд дальше вправо получить "февраль", "март", а "протянув" маркер заполнения от ячейки "январь" влево, соответственно получить "декабрь", "ноябрь" и т. д. Попробуйте.

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

В третьем столбце размещаются n-е члены прогрессии. Введите в ячейку С4 значение первого члена арифметической прогрессии.

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

Все формулы начинаются со знака равенства.

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

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

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

Полностью введя формулу, зафиксируйте ее нажатием {Enter}, в ячейке окажется результат вычисления по формуле, а в Строке формул сама формула.

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

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

Выделите ячейку С5 и, аналогично заполнению ячеек разностью прогрессии, заполните формулой, "протащив" маркер заполнения вниз, ряд ячеек, ниже С5.

Выделите ячейку С8 и посмотрите в Строке формул, как выглядит формула, она приняла вид =С7+А7. Заметно, что ссылки в формуле изменились относительно смещению самой формулы.

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

Выделите все ячейки таблицы, содержащие данные (не столбцы целиком, а только блок заполненных ячеек без заголовка "Вычисление n-го члена и суммы арифметической прогрессии") и выполните команду Формат  Столбец  Подгон ширины

Рис. 1. 5.

Рис. 1.6 .

ришла пора заняться заголовком таблицы "Вычисление n-го члена и суммы арифметической прогрессии".

Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки. Заголовок довольно неэстетично "вылезает" вправо за пределы нашей маленькой таблички.

В
ыделите четыре ячейки от А1 до D1 и выполните команду Формат  Ячейки..., выберите закладку Выравнивание и установите переключатели в положение "Центрировать по выделению" (Горизонтальное выравнивание) и "Переносить по словам" (рис. 1.5). Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.

Таблицу почти привели к виду образца. Если а этот момент выполнить просмотр Файл  Предварительный просмотр, то окажется, что остается выполнить обрамление таблицы.

Для этого выделите таблицу (без заголовка) и выполните команду Формат-Ячейки..., выберите вкладку Граница, определите стиль линии и активизируйте переключатели Сверху, Снизу, Слева, Справа (рис. 1.6.). Данная процедура распространяется на каждую из ячеек.

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

Выполните просмотр.

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

«Первое знакомство с процессором электронных таблиц

Microsoft Excel»

Цели работы

Познакомиться с рабочим окном Microsoft Excel.
Познакомиться с основными понятиями электронных таблиц.
Освоить основные приемы заполнения таблиц.

Задание 1. Запустите процессор электронных таблиц Microsoft Excel .

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

необходимо дважды щелкнуть кнопкой мыши на пиктограмме Microsoft Excel, которая обычно располагается в одном из групповых окон Windows (например, Microsoft Office);
или щелкнуть кнопкой мыши по кнопке «Пуск» и в появившемся главном меню Windows в пункте «Программы» щелкнуть по пункту подменю Microsoft Excel;
или дважды щелкнуть кнопкой мыши по выделенному ярлыку Microsoft Excel на Рабочем столе.

Задание 2. Разверните окно Excel на весь экран и внимательно рассмотрите его.

Первая строка окна – строка заголовка программы Microsoft Excel.

Вторая строка - меню Excel.

Третья строка - панель инструментов Стандартная

Четвертая строка - панель инструментов Форматирование

2.1. Прочитайте назначение кнопок панели инструментов Стандартная, медленно перемещая курсор мыши по кнопкам.

Пятая строка - строка формул.

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

Нижняя строка - строка состояния.

В крайней левой позиции нижней строки отображается индикатор режима работы Excel. Например, когда Excel ожидает ввода данных, то находится в режиме «готов» и индикатор режима показывает «Готов».

Задание 3. Освойте работу с меню Excel .

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

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

3.1. В меню Сервис выберите команду Параметры и раскройте вкладку Правка.
3.2. Проверьте, установлен ли флажок [  ]. Разрешить перетаскивание ячеек. Если нет, то установите его и нажмите кнопку ОК .

Щелчок мыши вне меню приводит к выходу из него и закрытию подменю.

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

Строки, столбцы, ячейки

Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк равно 65536, столбцов - 256. Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулы).

Номер строки - определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля.

Буква столбца - определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются в следующем порядке: A-Z, затем AA-AZ, затем BA-BZ и т.д. до IV.

Ячейка - первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес В3 определят ячейку на пересечении столбца В и строки номер 3.

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

Текущая ячейка выделяется серой рамкой. По умолчанию ввод данных и некоторые другие действия относятся к текущей ячейке.

4.1. Сделайте текущей ячейку D4 при помощи мыши.
4.2. Вернитесь в ячейку А1 при помощи клавиш перемещения курсора.

Диапазон ячеек (область, фрагмент)

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

Адрес диапазона состоит из координат противоположных углов, разделенных двоеточием. Например: В13:С19, А12:D27 или D:F.

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

Рабочий лист, книга

Электронная таблица в Excel имеет трехмерную структуру. Она состоит из листов, как книга. На экране виден только один лист - верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая кнопкой мыши на ярлычках листов, можно перейти к другому листу.

4.3. Сделайте текущим лист 6.

4.4. Вернитесь к листу 1.

Выделение столбцов, строк, блоков, таблицы

Для выделения с помощью мыши:

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

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

Для выделения нескольких несмежных блоков необходимо:

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

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

4.5. Выделите строку 3.
4.6. Отмените выделение.
4.7. Выделите столбец D.
4.8. Выделите блок А2: Е13 при помощи мыши.
4.9. Выделите столбцы A, B, C, D.
4.10. Отмените выделение.
4.11. Выделите блок C4: F10 при помощи клавиатуры.
4.12. Выделите рабочий лист.
4.13. Отмените выделение.
4.14. Выделите одновременно следующие блоки: F5:G10, H15:I15, C18:F20, H20.

Задание 5. Познакомьтесь с основными приемами заполнение таблиц .

Содержимое ячеек

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

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

Excel определяет, являются вводимые данные текстом, числом или формулой, по первому символу. Если первый символ - буква или знак «’», то Еxcel считает, что вводится текст. Если первый символ цифра или знак «=», то Еxcel считает, что вводится число или формула.

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

Ввод текста

Текст - это набор любых символов. Если текст начинается с числа, то начать ввод необходимо с символа " " ".

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

5. 1 . В ячейку А1 занесите текст "Век живи – век учись!"

Обратите внимание, что текст прижат к левому краю.

Внимание! Не бойтесь ошибок. Почти все действия могут быть отменены. Если в процессе работы произошло что - то, что вы не планировали, воспользуйтесь отменой: используя команды меню Правка, Отменить... , либо, что еще проще, кнопкой Отмена панели инструментов Стандартная . При каждом щелчке мыши по кнопке Отмена последовательно отменяется одна операция.

Ввод чисел

Числа в ячейку можно вводить со знаками =, +,- или без них. Если ширина введенного числа больше, чем ширина ячейки на экране, то Excel отображает его в экспоненциальной форме или вместо числа ставит символы # # # # (при этом число в памяти будет сохранено полностью).

Экспоненциальная форма используется для представления очень маленьких и очень больших чисел. Число 501000000 будет записано как 5,01Е+08, что означает 5,01*10 8 . Число 0,000000005 будет переставлено как 5Е- 9

Ввод формул

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

Формула должна начинаться со знака «=». Она может включать до 240 символов и не должна содержать пробелов.

Для ввода в ячейку формулы C1+F5 ее надо записать как = C1+F5. Это означает, что к содержимому ячейки C1 будет прибавлено содержимое ячейки F5. Результат будет получен в той ячейке, в которую занесена формула.

5.4. В ячейку D1 занесите формулу = C1-B1

Подведите итоги

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

Проверьте

Знаете ли вы, что такое : элементы окна Excel; строка; столбец; ячейка; лист; книга?

Умеете ли вы работать с меню, вводить текст, числа, формулы.

Предъявите преподавателю краткий конспект работы.

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

«Основные приемы редактирования таблиц в Microsoft Excel и сохранение их в файле на диске»

Цели работы:

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

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

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

Эти действия можно выполнить двумя способами.

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

При использовании меню необходимо выделить строки или столбцы и выполнить команды Формат, Строка, Размер или Формат, Столбец, Размер.

1.1. При помощи мыши измените ширину столбца А так, чтобы текст был виден полностью, а ширину столбцов В, С, D сделайте минимальной .
1.2. При помощи меню измените высоту строки номер 1 и сделайте ее равной 30.
1.3. Сделайте высоту строки номер 1 первоначальной (12,75)

Редактирование содержимого ячейки

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

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

Чтобы отредактировать данные после завершения ввода (после нажатия клавиши Enter), необходимо переместить указатель к нужной ячейке и нажать клавишу F2 для перехода в режим редактирования или щелкнуть кнопкой мыши на данных в строке формул. Далее необходимо отредактировать данные и для завершения редактирования нажать Enter или клавишу перемещения курсора.

1.4. Ведите в ячейку С1 число 5, в ячейку D1 формулу =100+C1
1.5. Замените текущее значение в ячейке С1 на 2000. В ячейке D1 появилось новое значение ячейки 2100.

Внимание! При вводе новых данных пересчет в таблице произошел автоматически. Это важнейшее свойство электронной таблицы.

1.6. Введите в ячейку А1 текст «Волга – российская река»
1.7. Измените содержимое ячейки А1 на «Енисей –крупная река Сибири»

Операции со строками, столбцами, диапазонами.

Эти действия могут быть выполнены различными способами:

через пункт меню Правка;
через промежуточный буфер обмена (вырезать, скопировать, вставить)
с помощью мыши.

Перемещение данных между ячейками таблицы

Вначале необходимо конкретно определить, что перемещается и куда .

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

1.8. Выделите диапазон А1:D1 и переместить его на строку ниже.
1.9. Верните диапазон на прежнее место.

Копирование данных

При копировании оригинал остается на прежнем месте, а в другом месте появляется копия. Копирование выполняется аналогично перемещению, но при нажатой клавише Ctrl.

1.10. Скопируй те диапазон А1:D1 в строки 2, 6, 8 .

Заполнение данными

При заполнении исходная ячейка или диапазон повторяется несколько раз за одно действие. Заполнение возможно вправо или вниз.

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

1.11. Выделите строку под номером 8 и заполните выделенными данными строки по 12-ю включительно.
1.12. Скопируйте столбец C в столбцы E, F, G.

Экран примет вид рис. 2. 1 .

Рис. 2. 1.

Удаление, очистка

Если надо очистить только данные (числа, текст, формулы), то достаточно нажать клавишу Del на выделенной ячейке или диапазоне (блоке).

1.13. Выделите диапазон (блок) А10:G13 и очистите его.

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

1. 14. Очистите содержимое ячейки G9, используя команды меню.

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

1.15. Удалите столбец Е.

Обратите внимание на смещение столбцов.

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

1.16. Удалите столбец Е с сохранением пустого места.

Для удаления всей рабочей таблицы используется команда Файл, Закрыть; на запрос ответьте «нет».

Задание 2. Научитесь использовать функцию автозаполнения.

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

2.1. В ячейку G10 занесите текст «январь».
2.2. В ячейку H10 занесите текст «февраль».
2.3. Выделите диапазон ячеек G10:H10.
2.4. Укажите в маленький квадратик в правом нижнем углу ячейки H10 (экранный курсор превращается в маркер заполнения).
2.5. Нажмите левую кнопку мыши и, не отпуская ее, двигайте мышь вправо, пока рамка не охватит ячейки G10:M10.

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

2.6. Введите в ячейки G11:M11 дни недели, начиная с понедельника.
2.7. Введите в ячейки G12:M12 года, начиная с 1990-го.

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

2.8. Внесите следующие данные в таблицу: в ячейки G16:M16 – века; в ячейку – G15 – заголовок “Население Москвы (в тыс. чел.)”; в ячейки G17:M17 – данные о населении Москвы по векам.

Вид экрана после выполнения работы представлен на рис. 2. 2.

Рис. 2. 2.

Задание 3. Освойте действия с таблицей в целом: Сохранить, Закрыть, Создать, Открыть.

Для действия с рабочей книгой в целом используется команда из меню Файл :

Закрыть – убирает документ с экрана;

Создать – создает новую рабочую книгу (пустую или на основе указанного шаблона);

Открыть – выводит рабочую книгу с диска на экран.

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

3.2. Уберите книгу с экрана.

3.3. Вернитесь к своей книге раб_1.xls.

3.4. Закройте файл.

Задание 4. Завершение работы с Excel.

Для выхода из Excel можно воспользоваться одним из следующих способов:

с помощью команды Файл, выход.:
из системного меню – команда Закрыть.;
с помощью “горячих клавиш” – Alt+F4.

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

Задание 5. Подведем итоги.

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

Проверьте:

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

Предъявите преподавателю:

краткий конспект;
файл раб_1.xls на экране и в личной папке.

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

«Решение задачи табулирования функции в Excel»

Цели работы:

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

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

Постановка задачи: вычислить значения функции y=kx(x 2 -1)/(x 2 +1) для всех x на интервале [-2; 2 ] с шагом 0,2 при k=10.

Решение должно быть получено в виде таблицы:

			y1=x^2-1	Y2=x^2+1	y=k*(y1/y2)

Задание 1. Прежде чем перейти к выполнению задачи, познакомтесь со способами адресации в Excel.

Абсолютная, относительная и смешанная адресации ячеек и

блоков (диапазонов)

При обращении к ячейке можно использовать описанные ранее способы: B3, A1: G9 и т. д. Такая адресация называется относительной. При её использовании в формулах Excel запоминает расположение относительно текущей ячейки. Так, например, когда вы вводите формулу =B1+B2 в ячейку B4, то Excel интерпретирует формулу как “прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки, расположенной двумя рядами выше”.

Если вы скопировали формулу =В1+B2 из ячейки B4 в С4, Excel также интерпретирует формулу как “прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше”. Таким образом, формула в ячейке С4 примет вид =С1+С2.

Если при копировании формул вы пожелаете сохранить ссылку на конкретную ячейку или область, то вам необходимо воспользоваться абсолютной адресацией. Для её задания необходимо перед именем столбца и перед номером строки ввести символ $. Например: B$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой - нет.

Задание 2. Заполните основную и вспомогательную таблицы

2. 1. Заполните шапку основной таблицы начиная с ячейки А1:

в ячейку А1 занесите N;

в ячейку B1 занесите X;

в ячейку C1 занесите K и т.д.

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

2.2. Заполните вспомогательную таблицу начальными исходными данными начиная с ячейки H1:

	Step

Где х0 - начальное значение х, step - шаг изменения х, k - коэффициент (константа).

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

2. 3. Используя функцию автозаполнения, заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22.
2. 4. Заполните столбец В значениями х:
в ячейку В2 занесите $H$2.

Это означает, что в ячейку В2 заносится значение из ячейки Н2 (начальное значение х), знак $ указывает на абсолютную адресацию;

в ячейку В3 занесите =В2 + $I$2.

Это означает, что начальное значение х будет увеличено на величину шага, который берется из ячейки I2;

скопируйте формулы из ячейки В3 в ячейки В4 ; В22.

Столбец заполнится значениями х от 2 до -2 шагом 0,2.

2. 5. Заполните столбец С значениями коэффициента k:
в ячейку С2 занесите =$J$2;
в ячейку С3 занесите =С2.

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

скопируйте формулу из ячейки С3 в ячейки С4: С22.

Весь столбец заполнится значением 10.

2. 6. Заполните столбец D значениями функции y1 =x^2-1:
в ячейку D2 занесите =B2 *B2-1;
скопируйте формулу из ячейки D2 в ячейки D3: D 22.

Столбец заполнится как положительными, так и отрицательными значениями функции у1. Начальное и конечное значения равны 3.

2. 7. Аналогичным образом заполните столбец Е значениями функции у2=х^2+1.

Проверьте! Все значения положительные; начальное и конечное значения равны 5.

2. 8. Заполните столбец F значениями функции y = k*(x^2-1)/(x^2+1):
в ячейку F2 занесите =С2*(D2/E2);
скопируйте формулу из F2 в ячейки F2:F22.

Проверьте! Значения функции как положительные, так и отрицательные; начальное и конечное значения равны 6.

Задание 3. Понаблюдайте за изменениями в основной таблице при смене данных во вспомогательной.

3. 1. Измените во вспомогательной таблице начальное значение х: в ячейку Н2 занесите -5.
3. 2. Измените значение шага: в ячейку I2 занесите 2.
3. 3. Измените значение коэффициента: в ячейку J2 занесите 1.

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

3. 4. Прежде чем продолжить работу, верните прежние начальные значения во вспомогательной таблице: х0 = –2, step = 0,2, k=10.

Задание 4. Оформить основную и вспомогательную таблицы.

4. 1. Вставьте две пустые строки для оформления заголовков:
установите курсор на строку номер 1;
выполните команды меню Вставка, Строки (2 раза).
4. 2. Введите заголовки:
в ячейку А1 «Таблицы»;
в ячейку А2 «Основная»;
в ячейку Н2 «Вспомогательная».
4. 3. Объедините ячейки А1:J1 и разместите заголовок «Таблицы» по центру:
выделите блок А1:J1;
кнопку Центрировать используйте к о столбцам панели инструментов Форматирование.
4. 4. Аналогичным образом разместите по центру заголовки «основная» и «вспомогательная».
4. 5. Оформите заголовки определенными шрифтами.

Шрифтовое оформление текста.

Символы любой ячейки или блока можно оформить разными шрифтами. Для этого необходимо выделить ячейку или блок, а затем воспользоваться кнопками из панели Форматирование или командой меню Формат, Ячейки, Шрифт. При использовании команды появится диалоговое окно вида:

Рис. 3. 1.

для заголовка «Таблицы» задайте шрифт Courier New Cyr, размер шрифта 14, полужирный.

Используйте кнопки панели инструментов Форматирование;

Для заголовков «основная» и «вспомогательная» задайте шрифт Courier New Cyr, размер шрифта 12, полужирный.

Используя команды меню Формат, Ячейки, Шрифт;

для шапок таблиц установите шрифт Courier New Cyr, размер шрифта 12, курсив.

Любым способом.

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

Выравнивание.

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

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

4.8. Задайте рамки для основной и вспомогательной таблиц, используя кнопку панели инструментов Форматирование.

Для задания рамки используется кнопка в панели Форматирование или команда меню Формат, Ячейки, Рамка

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

Фон

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

Для задания фона используется кнопка в панели Форматирование или команда мену Формат , Ячейка , Вид .

Вид экрана после выполнения работы представлен на рис. 3. 2.

Рис. 3. 2.

Задание 6. Завершите работу.

Задание 7. Подведите итоги.

Проверьте:

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

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

Предьявите преподавателю:

краткий конспект;
файл раб_2.xls на экране и на рабочем диске в личном каталоге.

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

«Использование функций и форматов чисел в Excel»

Цели работы:

познакомиться с использованием функций в EXCEL;
познакомиться с форматами чисел;
научиться защищать информацию в таблице;
научиться распечатывать таблицу.

Задание 1. Откройте файл раб_2.xls.

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

Защита ячеек.

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

Установка защиты выполняется в два действия:

1) отключают защиту (блокировку) с ячеек, подлежащих последующей корректировке;

2) включают защиту листа или книги.

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

Разблокировка (блокировка) ячеек.

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

Включение (снятие) защиты с листа или книги.

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

2.1. Выделите блок H4:J4 и снимите блокировку.

Выполните команду Формат, Ячейки, Защита, убрать знак [  ] в окне Защищаемая ячека.

2.2. Защитите лист.

Выполните команду Сервис, Защита, Защитить лист, Ок.

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

2.3. Попробуйте изменить значения в ячейках:

в ячейке А4 с 1 до 10.

Это невозможно.

Значение шага во вспомогательной таблице с 0,2 на 0,5.

Это возможно. В основной таблице произошел пересчет;

Измените текст "step" в ячейке 13 на текст "шаг".

Каков результат? Почему?

Верните начальное значение шага 0,2.

Задание 3. Сохраните файл под старым именем.

Выполните команду Сервис, Защита, Снять защиту листа.

Задание 4. Снимите защиту с листа.

Задание 5. Познакомьтесь с функциями пакета EXCEL.

Функции

Функции предназначены для упрощения рвссчетов и имеют следующую форму: y=f(x) , где y - результат вычисления функции, а х - аргумент, f - функция.

Пример содержимого ячейки с функцией: =А5+sin(C7), где А5 - адрес ячейки; sin() - имя функции, в круглых скобках указывается аргумент, С7 – аргумент (число, текст и т.д.), в данном случае ссылка на ячейку, содержащую число.

Некоторые функции

SQRT(X) - вычисляет положительный квадратный корень из числа х. Например: sqrt(25)=5

SIN(X) - вычисляет синус угла х, измеренного в радианах. Например:

sin(0,883) = 0,772646

MAX(список) - возвращает максимальное число списка. Например: max(55,39,50,28,67,43) = 67

SUM(список) - возвращает сумму чисел указанного списка (диапазона). Например: SUM(А1:А300) подсчитывает сумму чисел в техстах ячейках диапазона А1:А300

Имена функции в русифицированных версиях могут задаваться на русском языке.

Для часто используемой функции суммирования закреплена кнопка на панели инструментов  .

Для вставки функции в формулу можно воспользоваться "Мастером функций", вызываемым командой меню Вставка, Функция или кнопкой с изображением f x .

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

Рис. 4.1.

Второе диалоговое окно (второй шаг "Мастера функций") позволяет задать аргументы к выбранной функции. (Рис. 4 .2.)

5.1. Познакомтесь с видами функций в Excel.

Нажмите кнопку f x и выберите категорию 10 недавно использовавшихся. . Посмотрите, как обозначаются функции  , min, max.

5.2. Подсчитайте сумму вычисленных значений у и запишите ее в ячейку F25.

Кнопка  панели инструментов Стандартная.

В ячейку Е25 запишите поясняющий текст "Сумма у="

5.3. Оформите нахождение среднего арифметического вычисленных значений y (по аналогии с нахождением суммы)

Занесите в ячейку Е26 поясняющий текст, а в F26 - среднее значение.

Рис 4. 2.

5.4. Оформите нахождение максимального и минимального значений у, занеся в ячейки Е27 и Е28 поясняющий текст, а в ячейки F27 и F28 - минимальное и максимальное значения.

Задание 6. Оформление блок ячеек Е25:F28.

6.1. Задайте рамку для блока Е25:F28.

6.2. Заполните этот блок темже фоном, что и шапки таблицы.

6.3. Поясняющие подписи в ячейках Е25:F28 оформление шрифтом Arial Cyr полужирным с выравниванием вправо.

Вид экрана после выполнения данной части работы представлен на рис. 4. 3.

Задание 7. Сохраните файл под новым именем раб2_2.xls.

Рис. 4. 3.

Задание 8. Познакомьтесь с форматами чисел в Excel.

Числа

Число в ячейке можно представить в различных форматах. Например,100 будет выглядеть как 100,00 р. - в денежном формате; 10000% - в процентном выражении.

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

Для выполнения команды необходимо:

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

2. Выбрать команду Формат, Ячейки, Число;

3. Выбрать желаемый формат числа в диалоговом окне (рис. 4. 4.).

Рис. 4. 4.

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

Если ячейка отображается в виде символов ####, это означает, что столбец недостаточно широк для отображения числа целиком в установленном формате.

8.1. Скопируйте значения из столбца F в столбцы K, L, M.

Для этого воспользуйтесь правой кнопкой мыши. Откроется контекстно-зависимое меню, где нужно выбрать пункт Копировать.

8.2. В столбце К задайте формат, в котором отражаются все значащие цифры после запятой 0,00.

8.3. В столбце L задайте формат ПРОЦЕНТ.

В столбце М установите собственный формат – четыре знака после запятой (Формат, Ячейки, Число, Числовой формат, Число десятичных знаков – 4, Ок).

8.5 Оформите диапазон K3:M24.

Рис. 4. 5.

Задание 9. Предъявите результат работы учителю .

Вид экрана представлен на рисунке

Задание 10. Сохраните файл под старым именем раб2_2.xls.

Задание 11. Распечатайте таблицу на принтере, предварительно распечатав ее вид на экране.

Печать таблицы на экране и принтере

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

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

11. 1. Задайте режим предварительного просмотра с помощью кнопки Просмотр панели инструментов Стандартная.

11. 2. Щелкните по кнопке Страница и в окне параметров выберите альбомную ориентацию.

11. 3. Щелкните по кнопке Поля; на экране будут видны линии, обозначающие поля.

Установите указатель мыши на квадратик, расположенный слева по вертикали. Нажмите и не отпускайте левую кнопку мыши.

Внизу вы увидите цифры 2,50. Это высота установленного в данный момент верхнего поля.

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

11. 4. Убедитесь в том, что принтер подключен к вашему компьютеру и работоспособен.

11. 5. Нажмите на кнопку Печать.

Задание 12. Завершите работу с Excel

Задание 13. Подведите итоги .

Проверьте:

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

Если нет, еще раз перечитайте соответствующие разделы работы.

Предъявите преподавателю:

краткий конспект;
файл раб2_2.xls на экране и на рабочем диске в личном каталоге;
распечатку таблицы раб2_2.xls.

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

«Составление штатного расписания хозрасчетной больницы»

Цели работы:

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

Постановка задачи

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

Построим модель решения этой задачи.

Поясним, что является исходными данными. Казалось бы, ничего не дано, кроме общего фонда заработной платы. Однако заведующему больницей известно больше: он знает, что для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 заведующий аптекой, 3 заведующих отделениями, 1 главный врач, 1 заведующий хозяйством, 1 заведующий больницей. На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение о сокращении числа санитарок, чтобы увеличить оклад каждой из них.

Итак, заведующий принимает следующую модель задачи. За основу берется оклад санитарки, а все остальные вычисляются исходя из него: во сколько-то раз или на сколько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада санитарки: A*C+B, где C - оклад санитарки; A и B - коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.

Допустим, совет решил, что:

медсестра должна получать в 1,5 раза больше санитарки (A=1.5, B=0);
врач - в 3 раза больше санитарки (B=0, A=3);
заведующий отделением - на $30 больше, чем врач (A=3, B=30);
заведующий аптекой - в 2 раза больше санитарки (A=2, B=0);
заведующий хозяйством - на $40 больше медсестры (A=1.5, B= 40);
главный врач - в 4 раза больше санитарки (A=4, B=0);
заведующий больницей - на $20 больше главного врача (A=4, B=20).

Задав количество человек на каждой должности, можно составить уравнение:

N1*(A1*C+B1)+N2*(A2*C+B2)+...+N8*(A8*C+B8)=10000, где N1- количество санитарок; N2 - количество медсестер и т. д.

В этом уравнении нам известны A1... A8 и B1... B8, а неизвестны C и N1... N8.

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

Проделать такую работу трудно. Но вам поможет электронная таблица.

Рис. 5. 1.

Ход работы

Отведите для каждой должности одну строку и запишите названия должностей в столбец A (см. рис. 5. 1 - пример заполнения таблицы).
В столбцах B и C укажите соответственно коэффициенты A и B.
В ячейку H5 занесите заработную плату санитарки (в формате с фиксированной точкой и двумя знаками после нее).
В столбце D вычислите заработную плату для каждой должности по формуле A* C+B.

Обратите внимание! Этот столбец должен заполняться формулами с использованием абсолютной ссылки на ячейку H5, в которой указана зарплата санитарки. Изменение содержимого этой ячейки должно приводить к изменению содержимого всего столбца D и пересчету всей таблицы.

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

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

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

См. рис. 5.2 - пример оформления штатного расписания больницы без подобранных числовых значений.

9. 1. Оставьте видимыми столбцы A, D, E, F.

Столбцы B, C можно скрыть, воспользовавшись пунктом меню Формат, столбец, Скрыть.

Рис. 5. 2

9. 2. Дайте заголовок таблице «Штатное расписание хозрасчетной таблицы» и подзаголовок «зав. больницей Петров И. С. ».

9. 3. Оформите таблицу, используя авто форматирование. Для этого:

Рис. 5. 3.

выберите пункт меню Формат, Автоформат (см. рис. 5. 3);
выберите удовлетворяющий вас формат.

Сохраните отредактированную таблицу в личной папке под именем

раб_3. xls.

Предъявите преподавателю: файл раб_3. xls..

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

«Знакомство с графическими возможностями Excel»

Цели работы:

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

Задача

Построить графики функций y1 = x 2 - 1, y2 = x 2 + 1, y = 10 * (y1/y2) по данным лабораторной работы №3.

Построение графиков

Для построения обыкновенных графиков функций y = f(x) используется тип диаграммы ХУ – график с точечными маркерами . Эта возможность используется для проведения сравнительного анализа значений У при одних и тех же значениях Х, а также для графического решения систем уравнений с двумя переменными.

Воспользуемся таблицей, созданной в лабораторной работе №3. На одной диаграмме построим три совмещенных графика: y1 = x 2 -1, y2 = x 2 + 1, y = 10*(y1/y2).

Задание 1. Загрузите файл раб_2.xls (см. рис. 6. 1.).

Рис. 6. 1.

Задание 2. Снимите защиту с листа .

Задание 3. Переместите вспомогательную таблицу под основную, начиная с ячейки В27.

Задание 4. Щелкните по кнопке Мастер диаграмм и выберите на вкладке Стандартные, Т ип: График, В ид: График с маркерами, помечающими точки данных. (см. рис. 6. 2.)

Рис. 6. 2.

Задание 5. Постройте график по шагам, для этого надо щелкнуть по кнопке Далее. .

Рис. 6. 3.

5.1. На 2-м шаге укажите ячейки D3:F24 (см. рис. 6. 3.)

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

5.2. На 3-м шаге вид диалогового окна представлен на рис. 6. 4.

Рис. 6. 4.

5.3. На 4-м шаге выберите размещение диаграммы на имеющемся Листе1 и щелкните по кнопке Готово (см. рис. 6. 5.)

Рис. 6. 5.

В результате этих действий экран примет вид рис. 6. 6.

Рис. 6. 6.

5.4. Теперь надо исправить неправильный образец диаграмм.

Для этого выполните команду Диаграмма, Параметры диаграммы, Заголовки, где добавьте название диаграммы «Совмещенные графики». Укажите название по оси Х - «х», название по оси У - «у» (см. рис. 6. 7.)

Рис. 6. 7.

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

Рис. 6. 8.

Задание 6. Самостоятельно отформатируйте область построения диаграммы подобно рис. 6. 8. Для этого используйте команды пункта меню Диаграмма.

Задание 7. Сохраните файл под новым именем раб_4.xls.

Задание 8. Подготовьте таблицу и график к печати: выберите альбомную ориентацию.

Задание 9. Распечатайте таблицу и график на одном листе.

Задание 10. Подведите итоги.

Проверьте:

знаете ли вы, что такое Мастер диаграмм;
умеете ли вы : строить одиночный график; строить совмещенные графики; редактировать область диаграмм.

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

Предъявите преподавателю:

файл раб_4.xls на экране и на рабочем диске в личном каталоге;
распечатанные на одном листе таблицу и график.

Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования «Казанский государственный технологический университет» Лабораторные работы по информатике MS EXCEL Методические указания Казань 2006 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» УДК 658.26:66.094 Составители: доц. Е.С. Воробьев, доц. Е.В. Николаева, доц. Ф.И. Воробьева Лабораторные работы по информатике. MS Excel: Метод. указания / Казан. гос. технол. ун-т; Сост.: Е.С. Воробьев, Е.В. Николаева, Ф.И. Воробьева. – Казань, 2006. – 58 с. Изложены основные приемы работы в пакете MS Excel, описаны порядок и правила создания и редактирования электронных таблиц и диаграмм, выполнения основных вычислений, операций сортировки и фильтрации данных, анализа и обобщения данных, а также использование логических выражений, итоговых и дистрибутивных функций и матричных операций. Отдельная лабораторная работа посвящена поиску решения одно- и двупараметрической задачи. Могут быть использованы при изучении дисциплин «Информатика», «Применение ЭВМ в технологии» и «Применение ЭВМ в расчетах», могут служить пособием для внеаудиторной работы студентов, а также использоваться специалистами любой предметной области для самостоятельного освоения компьютерных технологий. Предназначены для студентов очной и заочной форм обучения специальностей 240802.65 «Основные процессы химических производств и химической кибернетики» и 240801 «Машины и аппараты химических производств», обучающихся по направлению 2480800 «Энерго- и ресурсосберегающие процессы в химической технологии, нефтехимии и биотехнологии». Ил. 68, табл. 1, библиогр. 5 назв. Подготовлены на кафедре общей химической технологии. Печатается по решению редакционно-издательского совета Казанского государственного технологического университета. Рецензенты: Б.К. Курбатов, доцент КГТУ им. Н.А. Туполева Э.А. Мухутдинов, доцент КГЭУ Казанский государственный технологический университет, 2006 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №1 Упражнение 1 Основные понятия, связанные с работой электронных таблиц Excel 1. Запустите программу Microsoft Excel: щелкните по кнопке Пуск; в появившемся меню выберите пункт Программы; в выплывающем меню выберите Microsoft Excel . 2. Внимательно рассмотрите окно программы Microsoft Excel (Рис. 1). Многие пункты горизонтального меню и кнопки панелей инструментов совпадают с пунктами меню и кнопками окна редактора Word. Однако совсем другой вид имеет рабочая область, которая представляет собой размеченную таблицу, состоящую из ячеек одинакового размера. Одна из ячеек явно выделена (обрамлена черной рамкой – табличным курсором). Как выделить другую ячейку? Для этого достаточно щелкнуть по ней мышью, причем указатель мыши в это Рис. 1 время должен иметь вид светлого креста. Попробуйте выделить различные ячейки таблицы. Для перемещения по таблице воспользуйтесь полосами прокрутки. 3. Для того чтобы ввести текст в одну из ячеек таблицы, необходимо ее выделить и сразу же (не дожидаясь, как в процессоре Word текстового курсора) “писать”. Выделите одну из ячеек таблицы и “напишите” в ней название сегодняшнего дня недели. 4. Основным отличием работы электронных таблиц от текстового процессора является то, что после ввода данных в ячейку, их необходимо зафиксировать, т.е. дать понять программе, что вы закончили вводить информацию -3- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» в эту конкретную ячейку. Зафиксировать данные можно одним из следующих способов: нажать клавишу {Enter}; щелкнуть мышью по другой ячейке; воспользоваться кнопками управления курсором на клавиатуре (стрелки). Зафиксируйте введенные вами данные. 5. Выделите ячейку таблицы, содержащую день недели, и воспользуйтесь кнопками выравнивания абзацев. Каким образом происходит выравнивание? Сделайте вывод. После всех экспериментов обязательно верните исходное выравнивание – влево, в дальнейшем это будет важно. 6. Вы уже заметили, что таблица состоит из столбцов и строк, причем у каждого из столбцов есть свой заголовок (А, В, С...), и все строки пронумерованы (1, 2, 3...) (Рис. 1). Для того чтобы выделить столбец целиком, достаточно щелкнуть мышью по его заголовку, чтобы выделить строку целиком, нужно щелкнуть мышью по ее заголовку. Выделите целиком тот столбец таблицы, в котором расположено введенное вами название дня недели. Каков заголовок этого столбца? Выделите целиком ту строку таблицы, в которой расположено название дня недели. Какой заголовок имеет эта строка? Воспользуйтесь полосами прокрутки для того, чтобы определить, сколько строк имеет таблица и каково имя последнего столбца. 7. Выделите ту ячейку таблицы, которая находится в столбце С и строке 4. Обратите внимание на то, что в «Поле имени» (Рис. 1), расположенном выше заголовка столбца А, появился адрес выделенной ячейки С4. Выделите другую ячейку, и вы увидите, что в Поле имени адрес изменился. Какой адрес имеет ячейка, содержащая день недели? 8. Давайте представим, что в ячейку, содержащую день недели, нужно дописать еще и часть суток. Выделите ячейку, содержащую день недели, введите с клавиатуры название текущей части суток, например "утро", и зафиксируйте данные, нажав клавишу {Enter}. Что произошло? Часть суток не "дописалась" в ячейку, а новые данные заменили исходные и вместо дня недели вы получили часть суток. То есть, если выделить ячейку таблицы, содержащую некоторые данные и ввести новые данные с клавиатуры, в ячейке таблицы окажется последняя информация. Как же дополнить содержимое ячейки таблицы (отредактировать), не набирая заново все данные? Выделив ячейку, содержащую часть суток, вы увидите, что ее содержимое дублируется в «Строке формул», расположенной выше заголовков столбцов (Рис. 1). Именно в «Строку формул» можно щелчком мыши установить традиционный текстовый курсор, внести все требуемые изменения и затем зафиксировать окончательный вариант данных. Выделите ячейку таблицы, содержащую часть суток, установите текстовый курсор перед текстом в «Строке формул» и наберите заново день недели. Зафиксируйте данные. У вас должна получиться следующая картина (Рис. 2). -4- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» 9. Видно, что запись вышла за пределы своей ячейки и заняла часть вторник, утро соседней. Это происходит только тогда, когда соседняя ячейка пуста. Давайте ее Рис. 2 заполним и проверим, что изменится. Выделите ячейку таблицы, расположенную правее ячейки, содержащей ваши данные (ячейку, на которую они "заехали "), и введите в нее любой текст. Теперь видна только та часть ваших данных, которая помещается в ячейке (Рис. вторник, пятница 3). Как просмотреть всю запись? И Рис. 3 опять к вам на помощь придет «Строка формул». Именно в ней можно увидеть все содержимое выделенной ячейки. Итак, «Строка формул» позволяет: внести изменения в содержимое выделенной ячейки; просмотреть содержимое ячейки, если запись не видна целиком. Выделите ячейку, содержащую день недели и часть суток, и просмотрите в «Строке формул» полное содержимое ячейки. 10.Как увеличить ширину столбца для того, чтобы в ячейке одновременно были видны и день недели, и часть суток? Для этого подведите указатель мыши к правой границе заголовка столбца, "поймайте" момент, когда указатель мыши примет вид черной двойной стрелки и, удерживая нажатой левую клавишу мыши, переместите границу столбца вправо. Столбец расширился. Аналогично можно изменять высоту строки. В этом случае курсор при подводе к нижнему краю заголовка строки принимает вид. Измените ширину столбца, содержащего день недели и часть суток таким образом, чтобы весь введенный текст был виден в ячейке таблицы. 11.Часто бывает нужно выделить не одну ячейку и не целый столбец, а блок ячеек (несколько ячеек, расположенных рядом). 12.Для этого нужно установить указатель мыши в крайней ячейке выделения и, при нажатой левой клавише, переместить мышь к противоположному краю выделения (весь выделенный блок "охвачен" рамкой, все ячейки, кроме той, с которой начали выделение, окрашены в черный цвет). Обратите внимание, что в процессе выделения в «Поле имени» регистрируется количество строк и столбцов, попадающих в выделение. В тот же момент, когда вы отпустили левую клавишу, в «Поле имени» высвечивается адрес ячейки, с которой начали выделение. Выделите блок ячеек, начав с ячейки А1 и закончив ячейкой, содержащей "пятница". Для выделения всей таблицы используйте "пустую" угловую кнопку, расположенную над заголовком первой строки. -5- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке. 13. Каким образом удалить содержимое ячейки? Для этого достаточно выделить ячейку (или блок ячеек) и нажать клавишу {Delete} или воспользоваться командой горизонтального меню «Правка» ⇒ «Очистить». Удалите все свои записи. Упражнение 2 Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних и верхних индексов Составим таблицу, вычисляющую n-й член и сумму арифметической прогрессии. Для начала напомним формулу n-го члена арифметической прогрессии: an = a1 + d (n − 1) и формулу суммы n первых членов арифметической прогрессии: n S n = (a1 + an) ⋅ , 2 где a1 – первый член прогрессии, а d – разность арифметической прогрессии. На рис. 4 представлена таблица для вычисления n-го члена и суммы арифметической прогрессии, первый член которой равен -2, а разность равна 0,725. Рис. 4 Перед выполнением упражнения придумайте свою арифметическую прогрессию, т. е. задайте собственные первый член прогрессии и разность. Выполнение упражнения можно разложить по следующим этапам: выделите ячейку А1 и введите в нее заголовок таблицы «Вычисление n-го члена и суммы арифметической прогрессии». Заголовок будет размещен в одну строчку и займет несколько ячеек правее А1; в ячейку A2 введите «d», в ячейку В3 – «n», в С3 – «an», в D3 – «Sn». Для набора нижних индексов сначала наберите весь текст, который должен быть в ячейке (например, аn), затем войдите в «Строку формул», выделите текст, который должен быть нижним индексом (например, n), откройте команду «Формат» ⇒ «Ячейки...» (в открывающемся диалоговом окне всего одна вкладка «Шрифт») и активизируйте переключатель «нижний индекс» в группе «Видоизменение»; Вычисление n-го члена и суммы арифметической прогрессии d n an Sn 0,725 1 -2 -2 0,725 2 -1,275 -3,275 0,725 3 -0,55 -3,825 0,725 4 0,175 -3,65 0,725 5 0.9 -2.75 0,725 6 1,625 -1,125 0,725 7 2,35 1,225 0,725 8 3,075 4,3 0,725 9 3,8 8,1 0,725 10 4,525 12,625 -6- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» выделите заполненные четыре ячейки. При помощи соответствующих кнопок панели инструментов увеличьте размер шрифта на 1 пт, выровняйте по центру и примените полужирный стиль начертания символов. Шапка таблицы оформлена. Теперь можете приступить к заполнению таблицы. 1. В ячейку А3 введите величину разности арифметической прогрессии (в нашем примере это 0,725). 2. Далее нужно заполнить ряд нижних ячеек таким же числом. Набирать в каждой ячейке одно и то же число неинтересно и нерационально. В редакторе Word мы пользовались приемом копировать–вставить. Excel позволяет еще больше упростить процедуру заполнения ячеек одинаковыми данными. Выделите ячейку А3, в которой размещена разность арифметической прогрессии. Выделенная ячейка окаймлена рамкой, в правом нижнем углу которой есть маленький черный квадрат – маркер заполнения. Если подвести указатель мыши к маркеру заполнения, и в тот момент, когда указатель мыши принимает форму черного крестика, протянуть маркер заполнения на несколько ячеек вниз (при этом справа от курсора появляется подсказка, какое значение вводится в текущую ячейку), то весь ряд выделенных ячеек заполнится данными, расположенными в первой ячейке. Заполните, таким образом, еще девять ячеек ниже ячейки А3 значением разности арифметической прогрессии. 3. В следующем столбце размещена последовательность чисел от 1 до 10. И опять нам поможет заполнить ряд маркер заполнения. Введите в ячейку В3 число 1, в ячейку В4 число 2, выделите обе эти ячейки и, ухватившись за маркер заполнения, протяните его вниз. Отличие от заполнения одинаковыми данными заключается в том, что, выделив две ячейки, вы указали принцип, по которому следует заполнить оставшиеся ячейки. Маркер заполнения можно "протаскивать" не только вниз, но и вверх, влево или вправо, в этих же направлениях распространится и заполнение. Элементом заполнения может быть не только формула или число, но и текст. Можно ввести в ячейку "январь" и, заполнив ряд дальше вправо, получить "февраль", "март", а "протянув" маркер заполнения от ячейки "январь" влево, соответственно получить "декабрь", "ноябрь" и т.д. Попробуйте выполнить это вне создаваемой таблицы. Самое главное, прежде чем распространять выделение, выделить именно ту ячейку (или те ячейки), по которой форматируется заполнение. 4. В третьем столбце размещаются n-е члены прогрессии. Введите в ячейку С3 значение первого члена арифметической прогрессии. В ячейку С4 нужно поместить формулу для вычисления n-го члена прогрессии, которая заключается в том, что каждая ячейка столбца отличается от предыдущей прибавлением разности арифметической прогрессии. Все формулы начинаются со знака равенства. Для ввода в ячейку формулы необходимо: активизировать ячейку; -7- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» ввести с клавиатуры знак равно «=» или в строке формул щелкнуть по кнопке «Изменить формулу» ; ввести (без пробелов) необходимые значения или ссылки, а также необходимые операторы; зафиксировать ввод. Адрес ячейки вводится в формулы в латинском регистре. Если ввод был выполнен в русском регистре, то появляется сообщение об ошибке «#ИМЯ?». Выделите ячейку С4 и наберите в ней формулу =С3+А4 (не забудьте перейти на латиницу, а вместо ссылки на ячейку А4 можно ввести конкретное значение разности вашей арифметической прогрессии). Можно и не набирать с клавиатуры адрес той ячейки, на которую делается ссылка. Набрав знак равенства, щелкните мышью по ячейке С4 и в строке формул появится ее адрес, затем продолжите набор формулы. В этом случае вам не нужно переключаться на латиницу. Полностью введя формулу, зафиксируйте ее нажатием {Enter}, в ячейке окажется результат вычисления, а в «Строке формул» сама формула. Вот проявилась и еще одна функция «Строки формул»: если в ячейке вы увидите результат вычислений по формуле, то саму формулу можно просмотреть в «Строке формул», выделив соответствующую ячейку. Если вы неправильно набрали формулу, исправить ее можно в «Строке формул», предварительно выделив ячейку. Выделите ячейку С4 и, аналогично заполнению ячеек разностью прогрессии, заполните формулой, «протащив» маркер заполнения вниз, ряд ячеек, ниже С4. Выделите ячейку С8 и посмотрите в «Строке формул», как выглядит формула, она приняла вид =С7+А8. Заметно, что ссылки в формуле изменились относительно смещения самой формулы. 5. Аналогично введите в ячейку D3 формулу =(-2+С3)*B3/2 для подсчета суммы n первых членов арифметической прогрессии, где вместо -2 должен быть первый член вашей придуманной арифметической прогрессии. Выделите ячейку D3 и заполните формулами нижние ячейки, протащив вниз маркер заполнения. 6. Теперь данными заполнены все ячейки, остается их только оформить. Все столбцы одинаковой ширины, хотя и содержат информацию разного объема. Можно вручную (используя мышь) изменить ширину отдельных столбцов, а можно автоматически подогнать ширину. Для этого выделите все ячейки таблицы, содержащие данные (не столбцы целиком, а только блок заполненных ячеек без заголовка "Вычисление n-го члена и суммы арифметической прогрессии") и выполните команду «Формат» ⇒ «Столбец» ⇒ «Автоподбор ширины». 7. Теперь отформатируем заголовок таблицы "Вычисление n-го члена и суммы арифметической прогрессии". Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки. Заголовок довольно неэстетично "вылезает" вправо за пределы нашей маленькой таблички. -8- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выделите четыре ячейки от А1 до D1 и выполните команду «Формат» ⇒ «Ячейки...», выберите закладку «Выравнивание» и установите переключатели (Рис. 6): группы «Выравнивание» ⇒ «по горизонтали:» в положение «по центру выделения»; группы «Отображение» ⇒ «Переносить по словам». Это позволит расположить заголовок в несколько строк и по центру выделенного блока ячеек. Таблицу почти привели к 8. виду образца. Если в этот момент выполнить просмотр «Файл» ⇒ «Предварительный просмотр», то окажется, что остается выполнить обрамление таблицы. Для этого выделите таблицу (без заголовка) и выполните команду «Формат» ⇒ «Ячейки...». В открывающемся диалоговом окне выберите вкладку «Граница», определите тип линии и активизируйте переключатели «Сверху», «Снизу», «Слева», «Справа» (Рис. 5). Данная процедура распространяется на каждую из ячеек выделенной области. Затем выделите блок ячеек, относящихся к заголовку: от А1 до D2 и, проделав те же операции, установите переключатель «Внешние». В этом случае получается рамка вокруг всех выделенных ячеек, а не каждой. Выполните просмотр. Рис. 5 Рис. 6 -9- Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №2 Упражнение 1 Закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: сортировка данных, типы выравнивания текста в ячейке, формат числа Грузоотправитель и его адрес Грузополучатель и его адрес К Реестру № Дата получения «___»___________200__г. СЧЕТ № 123 от 15.11.2000 Поставщик Торговый дом «Рога и Копыта» Адрес 243100, Клинцы, ул. Пушкина, 23 Р/счет № 45638078 в МММ-банке, МФО 985435 Дополнения: № Наименование Ед.измерения 1 2 3 4 5 6 ИТОГО Руководитель предприятия Кол-во Цена Сумма Сидоркин А.Ю. Главный бухгалтер Иванова А.Н. Упражнение заключается в создании и заполнении бланка товарного счета. Выполнение упражнения лучше всего разбить на три этапа: 1-и этап – создание таблицы бланка счета; 2-й этап – заполнение таблицы; 3-й этап – оформление бланка. 1-й этап Заключается в создании таблицы. Основная задача уместить таблицу по ширине листа. Для этого: предварительно установите поля, размер и ориентацию бумаги («Файл» ⇒ «Параметры страницы…»); - 10 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» выполнив команду «Сервис» ⇒ на «Параметры...», вкладке «Вид» в группе переключателей Параметры окна активизируйте переключатель «Авторазбиение на страницы» (Рис. 7) В результате вы получите правую границу полосы набора в виде вертикальной пунктирной линии (если ее не видно, переместитесь при помощи горизонтальной Рис. 7 полосы прокрутки вправо) и нижнюю границу полосы набора в виде горизонтальной пунктирной линии (для того, чтобы ее увидеть переместитесь при помощи вертикальной полосы прокрутки вниз). Авторазбиение на страницы позволяет уже в процессе набора данных и форматирования таблицы следить за тем, какие столбцы помещаются на странице, а какие нет. № 1 2 3 4 5 6 Наименование Ед.измерения Кол-во Цена Сумма ИТОГО Рис. 8 Создайте таблицу по предлагаемому образцу с таким же числом строк и столбцов (Рис. 8). Выровняйте и сформатируйте шрифт в ячейках-заголовках, подберите ширину столбцов, изменяя ее при помощи мыши. Введите нумерацию в первом столбце таблицы, воспользовавшись помощью маркера заполнения. «Разлинуйте» таблицу, используя линии различной толщины. Обратите внимание на то, что в последней строке пять соседних ячеек не имеют внутреннего обрамления. Проще всего добиться этого следующим путем: выделить всю таблицу и установить рамку – «Внешние» жирной линией; - 11 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» затем выделить все строки, кроме последней, и установить рамку тонкой линией «Справа», «Слева», «Сверху», «Снизу»; после этого выделить отдельно самую правую ячейку нижней строки и установить для нее рамку «Слева» тонкой линией; останется выделить первую строку таблицы и установить для нее рамку «Снизу» жирной линией. Хотя можно действовать и наоборот. Сначала «разлиновать» всю таблицу, а затем снять лишние линии обрамления. На этом этапе желательно выполнить команду «Файл» ⇒ «Предварительный просмотр», чтобы убедиться, что таблица целиком вмещается на листе по ширине и все линии обрамления на нужном месте. 2-й этап Заключается в заполнении таблицы, сортировке данных и использовании различных форматов числа. Заполните столбцы «Наименование», «Кол-во» и «Цена» по своему усмотрению. Установите денежный формат числа в тех ячейках, в которых будут размещены суммы и установите требуемое число десятичных знаков, если они вообще нужны. В нашем случае это ячейки столбцов «Цена» и Рис. 9 «Сумма». Их нужно выделить и выполнить команду «Формат» ⇒ «Ячейки...», выбрать вкладку «Число» и выбрать категорию «Денежный» (Рис. 9). Это даст вам разделение на тысячи, чтобы удобнее было ориентироваться в крупных суммах. Введите формулу для подсчета суммы, которая заключается в умножении цены на количество, и заполните формулой ряд ячеек вниз. Введите формулу в ячейку для итоговой суммы. Для этого выделите блок тех ячеек, которые нужно сложить, и одну пустую ячейку под этим блоком, в которую нужно поместить результат. После этого нажмите кнопку панели инструментов. Попробуйте изменить данные в отдельных ячейках и проследите, как изменится результат вычислений. Отсортируйте записи по алфавиту. Для этого выделите все строки таблицы, кроме первой (заголовка) и последней («Итого»), можно не выделять и - 12 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» нумерацию. Выполните команду «Данные» ⇒ «Сортировка...» (Рис. 10), выберите столбец, по которому нужно отсортировать данные (в нашем случае это столбец В, так как именно он содержит перечень товаров, подлежащих сортировке), и установите переключатель в положение «По возрастанию». 3-й этап оформления счета вставьте Для дополнительные строки перед таблицей. Для этого выделите несколько первых строк таблицы и выполните команду «Вставка» ⇒ «Строки». Рис. 10 Вставится столько же строк, сколько вы выделили. Наберите необходимый текст до и после таблицы. Следите за выравниванием. Обратите внимание, что текст «Дата получения «__»_______200_г.» и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы «Сумма» (самый правый столбец нашей таблички), только применено выравнивание вправо. Текст «СЧЕТ №…» внесен в ячейку самого левого столбца, и применено выравнивание по центру выделения (предварительно выделены ячейки одной строки по всей ширине таблицы счета). Применена рамка для этих ячеек сверху и снизу. Вся остальная текстовая информация до и после таблицы внесена в самый левый столбец, выравнивание влево. Выполните просмотр. Упражнение 2 Введение понятия «абсолютная ссылка», установка точного значения ширины столбца при помощи команд горизонтального меню. Вставка функции при помощи мастера функций Новое понятие «абсолютная ссылка» можно рассмотреть на конкретном примере. Подготовим традиционную таблицу квадратов двузначных чисел (Рис. 11), так хорошо знакомую каждому из курса алгебры. В ячейку A3 введите число 1, в ячейку А4 - число 2, выделите обе ячейки и протащите маркер выделения вниз, чтобы заполнить столбец числами от 1 до 9. Аналогично заполните ячейки В2 - К2 числами от 0 до 9. Когда вы заполнили строчку числами от 0 до 9, то все необходимые вам для работы ячейки одновременно не видны на экране. Давайте сузим их, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой). Для этого выделите столбцы от А до К и выполните команду «Формат» ⇒ - 13 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» «Столбец» ⇒ «Ширина...», в поле ввода «Ширина столбца» введите значение, например 5. Разумеется, каждому понятно, что в ячейку В3 нужно поместить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце А, и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке В3 можно задать формулой А3*10+В2 (число десятков, умноженное на десять плюс число единиц). Остается возвести это число в квадрат. 1 2 3 4 5 6 7 8 9 0 100 400 900 1600 2500 3600 4900 6400 8100 1 121 441 961 1681 2601 3721 5041 6561 8281 ТАБЛИЦА КВАДРАТОВ 2 3 4 5 144 169 196 225 484 529 576 625 1024 1089 1156 1225 1764 1849 1936 2025 2704 2809 2916 3025 3844 3969 4096 4225 5184 5329 5476 5625 6724 6889 7056 7225 8464 8649 8836 9025 6 256 676 1296 2116 3136 4356 5776 7396 9216 7 289 729 1369 2209 3249 4489 5929 7569 9409 8 324 784 1444 2304 3364 4624 6084 7744 9604 9 361 841 1521 2401 3481 4761 6241 7921 9801 Рис. 11 Попробуем воспользоваться «Мастером функций». Для этого выделите ячейку, в которой должен разместиться результат вычислений (ВЗ), и выполните команду «Вставка» ⇒ «Функция...» (Рис. 12). В диалоговом окне «Мастер функций (шаг 1 из 2)» (Рис. 12) имеется два подокна: «Категория» и «Функция». При выборе определенной функции в нижней части диалогового окна появляется краткое ее описание. Среди предложенных Рис. 12 категорий функций выберите «Математические», среди «Функций» - «Степень», нажмите кнопку Ok. В следующем диалоговом окне (Рис. 13) введите в поле «Число» (основание степени) – А3*10+В2 и в поле «Показатель степени» − 2. Так же, как и при наборе формулы непосредственно в ячейке электронной таблицы, нет необходимости вводить с клавиатуры адрес каждой ячейки, на которую ссылается формула. В - 14 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» диалоговом окне второго шага «Мастера функций» достаточно указать мышью на соответствующую ячейку электронной таблицы, и ее адрес появится в поле ввода «Число» диалогового окна. Вам останется ввести только арифметические Рис. 13 знаки (*, +) и число 10. В тех случаях, когда для ввода аргументов нужно выделять ячейки, закрытые окном, справа от каждого поля для ввода аргументов имеются кнопки, которые позволяют сворачивать и разворачивать диалоговое окно. Кроме того, окно «Мастера функций» можно переместить в сторону, «схватив» мышью за заголовок. В этом же диалоговом окне (Рис. 13) можно увидеть значение самого числа (10) и результат вычисления степени (100). Остается только нажать кнопку Ok. В ячейке В3 появился результат вычислений. Хотелось бы распространить эту формулу и на остальные ячейки таблицы. Выделите ячейку В3 и заполните, протянув маркер заполнения вправо, соседние ячейки. Что произошло (Рис. 14)? Рис. 14 Почему результат не оправдал наших ожиданий? В ячейке С3 не видно числа, т. к. оно не помещается целиком в ячейкуРасширьте мышью столбец С. Число появилось на экране, но оно явно не соответствует квадрату числа 11 (Рис. 15). Рис. 15 Почему? Дело в том, что когда мы распространили формулу вправо, Excel автоматически изменил адреса ячеек с учетом нашего смещения, на которые ссылается формула, и в ячейке С3 возводится в квадрат не число 11, а число, вычисленное по формуле В3*10+С2. Во всех предыдущих упражнениях нас вполне устраивали относительные ссылки на ячейки таблицы (при перемещении формулы по такому же закону смещаются и ссылки), однако здесь возникла необходимость зафиксировать определенные ссылки, т.е. указать, что число десятков можно брать только из столбца А, а число единиц только из строки 2 (для того, чтобы формулу можно было - 15 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» распространить вниз). Для этого в Excel существует возможность задавать абсолютные и смешанные ссылки. Абсолютная ссылка – это ссылка, которая не изменяется при копировании формул. Для этого перед именем столбца и номером строки добавляется знак доллара $ (либо вводится с клавиатуры, либо после ввода адреса ячейки нажимается функциональная клавиша F4). Смешанными называются ссылки, которые только частично являются абсолютными, т.е. фиксируется либо столбец, либо строка. В этом случае знак доллара $ устанавливается либо перед буквой, в случае, когда фиксируется столбец, либо перед цифрой, в случае, когда фиксируется строка. Знак доллара $ либо вводится с клавиатуры, либо после ввода адреса ячейки нажимается функциональная клавиша F4 до тех пор, пока знак $ не окажется в необходимом месте. При копировании формулы, содержащей смешанную ссылку, изменяется только относительная часть ссылки. Верните ширину столбца С в исходное положение и выполните следующие действия: Выделите ячейку В3 и, установив текстовый курсор в «Строку формул», исправьте имеющуюся формулу =СТЕПЕНЬ(А3*10+В2;2) на правильную =СТЕПЕНЬ($А3*10+В$2,2). Теперь, воспользовавшись услугами маркера заполнения, можно заполнить этой формулой все свободные ячейки таблицы (сначала протянуть маркер заполнения вправо, затем, не снимая выделения с полученного блока ячеек, вниз). Для ввода ссылок на ячейки с данными столбца А и строки 2 мы использовали смешанные ссылки. Абсолютную ссылку в нашем примере можно было бы использовать, если бы мы ввели в формулу не число 10, на которое умножаются числа в столбце А, а адрес ячейки, например A15 (где ввели бы это число 10). В этом случае формула в ячейке B3 записалась бы в виде: =СТЕПЕНЬ($A3*$A$15+B$2), а затем также скопировали ее в остальные ячейки. Попробуйте это выполнить. Осталось оформить таблицу: ввести и ячейку А1 заголовок, отформатировать его и отцентрировать по выделению, выполнить обрамление таблицы и заполнение фоном отдельных ячеек. Упражнение 3 Введение понятия «имя ячейки» Представьте, что вы имеете собственную фирму по продаже какой-либо продукции и вам ежедневно приходится распечатывать прайс-лист с ценами на товары в зависимости от курса доллара. Подготовьте таблицу, состоящую из столбцов: «Наименование товара»; «Эквивалент $ US»; «Цена в р.». - 16 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Заполните все столбцы, креме «Цена в р.». Столбец «Наименование товара» заполните текстовыми данными (перечень товаров по вашему усмотрению), а столбец «Эквивалент $ US» числами (цены в $). Понятно, что в столбце «Цена в р.» должна разместиться формула: «Эквивалент $ US»*«Курс доллара». Почему неудобно в этой формуле умножать на конкретное значение курса? Да потому, что при каждом изменении курса, вам придется менять свою формулу в каждой ячейке. Проще отвести под значение курса доллара отдельную ячейку, на которую и ссылаться в формуле. Ясно, что ссылка должна быть абсолютной, т.е. значение курса доллара можно брать только из этой конкретной ячейки с зафиксированным адресом. Как задавать абсолютные ссылки, мы рассмотрели выше, однако существует еще один удобный способ: ссылаться не на адрес ячейки, а на имя, которое можно присвоить ячейке. При присвоении имен ячейке или диапазону ячеек, можно обращаться к этой ячейке или диапазону в любой момент и из любой точки таблицы, даже если они меняют свои местоположения или находятся на других листах. Выделите ячейку, в которую будет вводиться курс доллара (выше таблицы), введите в нее значение курса доллара на сегодняшний день и выполните команду «Вставка» ⇒ «Имя» ⇒ «Присвоить...». В открывающемся диалоговом окне (Рис. 16) можно ввести любое имя и выбрать в поле «Формула» диапазон, для которого вводится это имя. Имя может иметь в длину до 255 символов и содержать буквы, цифры, подчерки (_), символы: обратная косая черта (\), точки и вопросительные знаки. Однако первый символ должен быть буквой, подчерком (_) или символом обратная косая черта (\). Не допускаются имена, которые воспринимаются как числа или ссылки на ячейки. В появившемся диалоговом окне вам остается только ввести имя ячейки (ее точный адрес уже приведен в поле ввода «Формула») и нажать кнопку Оk. Обратите внимание на то, что в «Поле имени», вместо адреса ячейки, теперь размещено ее имя. В ячейку, расположенную левее ячейки «Курс_доллара», можно ввести текст «Курс доллара». Рис. 16 Теперь остается ввести формулу для подсчета цены в рублях. Для этого выделите самую верхнюю пустую ячейку столбца «Цена в рублях» и введите формулу следующим образом: введите знак «=», затем щелкните мышью - 17 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» по ячейке, расположенной левее (в которой размещена цена в дол.), после этого введите знак «*» и «Курс_доллара». Формула должна выглядеть приблизительно так: =В7*Курс_доллара. Заполните формулу вниз, воспользовавшись услугами маркера заполнения. Выделите соответствующие ячейки и примените к ним денежный формат числа. Оформите заголовок таблицы: выровняйте по центру, примените полужирный стиль начертания шрифта, расширьте строку и примените вертикальное выравнивание по центру, воспользовавшись командой «Формат» ⇒ «Ячейки...», выберите вкладку «Выравнивание» и в группе «Вертикальное:» выберите «По центру». В этом же диалоговом окне активизируйте переключатель «Переносить по словам» на случай, если какой-то заголовок не поместится в одну строчку. Измените ширину столбцов. Выделите таблицу и задайте для нее обрамление. - 18 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №3 Упражнение 1 Изменение ориентации текста в ячейке, ознакомление с возможностями баз данных Excel. Сортировка данных по нескольким ключам Представьте себя владельцем маленького магазина. Необходимо вести строгий учет прихода и расхода товаров, ежедневно иметь перед глазами реальный остаток, иметь возможность распечатать наименование товаров по отделам и т.д. Даже в таком непростом деле Excel может заметно облегчить работу. Разобьем данное упражнение на несколько заданий в логической последовательности: создание таблицы; заполнение таблицы данными традиционным способом и с применением формы; подбор данных по определенному признаку. 1. Создание таблицы Введите заголовки таблицы в соответствии с предложенным образцом. Учтите, что заголовок располагается в двух строках таблицы: в верхней строке «Приход», «Расход», «Остаток», а строкой ниже остальные пункты заголовка (Рис. 17). Сумма остатка Остаток Кол-во остатка Кол-во расхода Расход Цена расхода Наименование товара Кол-во прихода Отдел Цена прихода № Единица измерения Приход 1 2 3 4 5 6 Рис. 17 Ввод текста заголовка лучше начать со второй строки. Вы уже обратили внимание на то, что графа «Приход» распространяется на две ячейки. Слово «Приход» набрано в том же столбце, что и «Цена прихода», затем выделены две соседние ячейки, и текст отцентрирован по выделению (данная операция неоднократно рассматривалась в предыдущих упражнениях). Аналогично сформатированы ячейки «Расход» и «Остаток». Выделите вторую строку заголовка и выровняйте по центру. Также видно, что для того, чтобы вся таблица могла разместиться по ширине на листе, в некоторых ячейках текст «повернут на 90°». Выделите те ячейки, в - 19 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» которых нужно «развернуть» текст и выберите команду «Формат» ⇒ «Ячейки...», на вкладке «Выравнивание» (Рис. 18) выберите «Ориентацию текста» 90o и обязательно активизируйте переключатель «Переносить по словам» (вертикальное выравнивание оставьте «По нижнему краю»). Для оставшихся (неразвернутых) ячеек примените вертикальное выравнивание «По центру». Задайте обрамление таблицы («Формат» ⇒ «Ячейки…», вкладка «Граница»). Установите в ячейках, Рис. 18 содержащих цены, денежный формат числа («Формат» ⇒ «Ячейки…», вкладка «Число»). Введите нумерацию строк таблицы (столбец №), при помощи маркера заполнения. Вставьте формулы для количества остатка («Кол-во прихода» минус «Кол-во расхода») и суммы остатка («Кол-во остатка» умножить на «Цену расхода»). Распространите эти формулы вниз по таблице. В процессе выполнения задания во многих случаях удобнее пользоваться контекстным меню, вызываемым нажатием правой клавиши мыши. Так, для форматирования ячеек их достаточно выделить, щелкнуть правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения и выбрать команду «Формат» ⇒ «Ячеек...». При этом вы перейдете к тому же диалоговому окну «Формат ячеек» (Рис. 18). Да и редактировать содержимое ячейки (исправлять, изменять данные) совсем не обязательно в «Строке формул». Если дважды щелкнуть мышью по ячейке или нажать клавишу F2, в ней появится текстовый курсор и можно произвести все необходимые исправления. 2. Заполнение таблицы Переименуйте «Лист1» в «Наличие». Для этого нажмите правую кнопку мыши на ярлыке «Лист1» и выберите команду переименовать. Введите новое название и нажмите клавишу {Enter}. Определитесь, каким видом товара вы собираетесь торговать, и какие отделы будут в вашем магазине. Вносите данные в таблицу не по отделам, а вперемешку (в порядке поступления товаров). Заполните все ячейки, кроме тех, которые содержат формулы («Остаток»). Обязательно оставьте последнюю строку таблицы пустой (но эта строка должна содержать все формулы и нумерацию). Вводите данные таким - 20 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» образом, чтобы встречались разные товары из одного отдела (но не подряд) и обязательно присутствовали товары с нулевым остатком (все продано) (Рис. 19). Согласитесь, что традиционный способ заполнения таблицы не особенно удобен. Воспользуемся возможностями баз данных Excel. 1 2 3 4 5 6 Отдел Кондитерский Молочный Мясной Мясной Вино-водочный Наименование товара Зефир в шоколаде Сыр Колбаса Московская Балык Водка «Абсолют» Расход Остаток Цена прихода Кол-во прихода Цена расхода Кол-во расхода Кол-во остатка Сумма остатка № Единица измерения Приход упак. 20 р. 15 кг. 65 р. 10 кг. 110 р. 20 кг. 120 р. 10 бут. 2 л. 400 р. 100 25р. 85 р. 120р. 140 р. 450 р. 15 8 15 5 99 0 2 5 5 1 0 0 р. 170 р. 600 р. 700 р. 450 р. 0 р. Рис. 19 Выберите команду «Данные» ⇒ «Форма...» Вы получите форму данных (Рис. 20), содержащую статический текст (имена полей базы данных) и окон редактирования, в которых можно вводить и редактировать текст. Вычисляемые поля (в которых размещены формулы) выводятся на экран без окон редактирования («Колво остатка» и «Сумма остатка»). Теперь вы имеете свою таблицу как бы в форме отдельных карточекзаписей (каждая из которых представляет строку таблицы). Рис. 20 Перемещаться между записями можно либо при помощи кнопок «Предыдущая», «Следующая», либо клавишами управления курсором (вверх, вниз), либо перемещая бегунок на полосе прокрутки формы данных. Дойдя до последней записи (мы специально оставили ее пустой, но распространили на нее формулы и нумерацию), заполните ее новыми данными. Перемещаться между окнами редактирования, в которые вносятся данные, удобно клавишей {Tab}. Когда заполните всю запись, нажмите клавишу {Enter}, и вы автоматически перейдете к новой чистой карточке-записи. Как только вы заполните новую запись, вся внесенная вами информация автоматически воспроизведется в исходной таблице. Заполните несколько новых записей и нажмите кнопку «Закрыть». - 21 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Как видно, заполнять таблицу в режиме формы довольно удобно. 3. Заполнение таблицы с помощью готового списка данных Поскольку количество отделов у нас ограничено и их названия постоянны, то при заполнении таблицы лучше всего пользоваться заранее подготовленным списком этих отделов. Сотрем названия отделов из столбца «Отдел» и введем короткий список, включающий названия всех отделов по одному разу, за пределами таблицы, например в столбце L. Затем выделяем ячейки столбца «Отдел» в таблице и выбираем команду «Данные» ⇒ «Проверка». При этом появится Рис. 21 диалоговое окно «Проверка вводимых значений» (Рис. 21), где мы должны указать условия проверки. В нашем примере мы должны выбирать из списка (что и вводим в поле «Тип данных»). Чтобы выбрать «Источник» данных воспользуйтесь кнопкой свертывания окна. Нажмите ее, выделите список наших отделов в столбце L и вернитесь в окно с помощью кнопки разворачивания окна. Выполнив эти действия, нажмите кнопку Ok. Теперь при переходе в ячейки столбца «Отдел», где установлено условие проверки, справа от этих ячеек будет появляться квадрат со стрелкой, щелкнув по которому, мы можем выбрать необходимое нам название отдела (Рис. 22). Рис. 22 Чтобы скрыть таблицу отделов, можно сделать шрифт в ячейках столбца L белым, либо скрыть весь столбец. Чтобы скрыть столбец L, выделите его, выберите команду «Формат» ⇒ «Столбец» ⇒ «Скрыть». Чтобы вернуть столбец L на экран, - 22 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» необходимо выделить столбцы вокруг скрытого столбца (столбцы K и М) и выполнить команду «Формат» ⇒ «Столбец» ⇒ «Отобразить». Заметим, что команду «Скрыть» можно применять и к строкам. Для этого выделяется строка, выбирается команда «Формат» ⇒ «Строка» ⇒ «Скрыть». Чтобы вернуть строку на экран, необходимо выделить строки вокруг скрытой строки и выполнить команду «Формат» ⇒ «Строка» ⇒ «Отобразить». Рис. 23 Создавать список можно и на другом листе. Однако, в этом случае нельзя в качестве «Источника» информации указать адреса, включающие имя листа, т.е. необходимо вводить в качестве адресации имя диапазона ячеек. На прошлом занятии мы познакомились с тем, как задавать имя одной ячейки. Для задания имени диапазоны ячеек необходимо перед выполнением команды «Вставка» ⇒ «Имя» ⇒ «Присвоить» выделить диапазон ячеек, а не одну ячейку. Перенесем наш список отделов из столбца L Листа1 на Лист2 в столбец А. Выделяем ячейки, в которых помещен наш список и выполняем команду «Вставка» ⇒ «Имя» ⇒ «Присвоить». В открывающемся диалоговом окне (Рис. 23) можно ввести любое имя, например «Отдел», и выбрать в поле «Формула» диапазон, для которого вводится это имя (по умолчанию здесь помещается адрес выделенного нами диапазона). После этого нажимаем кнопку Ok. Теперь в диалоговом окне команды «Проверка…» в качестве источника (Рис. 21) достаточно ввести знак «=», затем нажать клавишу F3 для открытия списка имеющихся имен ячеек, выбрать в открывшемся списке «Отдел» и нажать кнопки Ok для закрытия диалоговых окон. 4. Сортировка данных Итак, вы заполняли таблицу в порядке поступления товаров, а хотелось бы иметь список товаров по отделам, для этого применим сортировку строк. Выделите таблицу со второй строкой заголовка, но без первого столбца «№», и выберите команду «Данные» ⇒ «Сортировка...» (Рис. 24). Рис. 24 - 23 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выберите первый ключ сортировки: в раскрывающемся списке «Сортировать по» выберите «Отдел» и установите переключатель в положение «По возрастанию» (все отделы в таблице расположатся по алфавиту). Если же вы хотите, чтобы внутри отдела все товары размещались по алфавиту, то выберите второй ключ сортировки: в раскрывающемся списке «Затем по» выберите «Наименование товара», установите переключатель в положение «По возрастанию». Теперь вы имеете полный список товаров по отделам. 5. Фильтрация данных Продолжим знакомство с возможностями баз данных Excel. Вспомним, что нам ежедневно нужно распечатывать список товаров, оставшихся в магазине (имеющих ненулевой остаток) или показать остатки в какомлибо отделе, но для этого сначала нужно получить такой список, т.е. отфильтровать данные. Выделите таблицу со второй строкой заголовка (как перед созданием формы данных). Выберите команду меню «Данные» ⇒ «Фильтр...» ⇒ «Автофильтр». Снимите выделение с таблицы. У каждой ячейки заголовка таблицы появилась кнопка со стрелкой (она не выводится на печать), позволяющая задать критерий фильтра (Рис. 25). Рис. 25 Пусть мы хотим оставить все записи для «Кондитерского отдела». Раскройте список ячеек «Отдел» и выберите «Кондитерский». В этом случае Excel изменит таблицу и покажет ее в виде, где будут присутствовать только данные, относящиеся к выбранному отделу (Рис. 26), а стрелка в колонке, где был применен автофильтр, станет синей. Рис. 26 - 24 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Точно также можно посмотреть данные и по другим отделам или выбрать критерий фильтрации в другом столбце. В отфильтрованном виде таблицу можно распечатать. Отфильтрованные строки можно выделить цветом шрифта, фоном, рамками, иным способом отформатировать. Убрав фильтрацию, мы получим очень наглядную разметку таблицы. В отфильтрованном участке таблицы можно вычислять суммы, произведения, производить другие операции так, как будто нет других строк таблицы. Например, мы хотим посчитать сумму остатка по кондитерскому отделу. Для этого мы выделяем данные столбца «Сумма остатка», захватив свободную последнюю ячейку, и нажимаем кнопку «Автосуммирования» . В свободной ячейке появится функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; F2:F8) (Рис. 26). В ней первым аргументом является номер математической или статистической операции (1 вычисление среднего значения; 2 и 3 – подсчет количества чисел и непустых ячеек; 4 и 5 – вычисление максимума и минимума; 6 - произведение; 7 и 8 – стандартное отклонение; 9 – сумма; 10 и 11 – дисперсия), а вторым – интервал вычислений. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ находится в категории математические и отличается тем, что она вычисляет значения только по видимым ячейкам, а невидимые не учитывает. При изменении фильтрации меняются и промежуточные итоги (Рис. 25), тогда как обычная функция суммирования или произведения останется неизменной. Изменим немного условие задачи, пусть мы хотим посмотреть данные по ненулевым остаткам кондитерского отдела. Для этого выберем в списке столбца «Сумма остатка» пункт «Условие». При этом появится диалоговое окно «Пользовательский автофильтр» (Рис. 28). В верхнем поле выбираем «больше» «0.00р.». Полученный результат представлен на рис. 28. Рис. 27 А теперь, допустим, мы хотим посмотреть данные по ненулевым остаткам кондитерского и мясного отделов. Для этого в столбце «Сумма остатка» мы оставляем тот же самый фильтр, а в столбце «Отдел» выбираем «Условие» (Рис. 28). В верхнем поле выбираем «равно» Рис. 28 - 25 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» «Кондитерский», внизу «равно» «Мясной», а в качестве логической функции задаем ИЛИ. Полученный результат представлен на рис. 29. Рис. 29 Чтобы снова увидеть таблицу целиком, необходимо щелкнуть по стрелке в столбце, где применялась фильтрация (они показаны синими стрелками), выбрать в списке «Все» или еще раз зайти в команду «Фильтр» ⇒ «Автофильтр» меню «Данные» для отмены режима фильтрации. - 26 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа № 4 Упражнение 1 Создание и редактирование графиков в документе Excel Очень часто в жизни приходится измерять зависимость одной переменной от другой и показывать эти зависимости в виде графиков. Excel обеспечивает возможность такого наглядного отображения числовых данных электронных. Допустим, был проведен какой-нибудь эксперимент, например, измерили зависимость какогото параметра от температуры. Начальная температура была равна 10оC. Шаг изменения – 10оС. Введите эти данные на лист Excel (Рис. 30). Чтобы расположить заголовок «Исходные данные» над таблицей исходных данных так, как Рис. 30 показано на рисунке, выделите две ячейки, выполните команду «Формат» ⇒ «Формат ячейки…» и на вкладке «Выравнивание» поставьте переключатель в полях «переносить по словам», «объединить ячейки» в группе отображение и выравнивание «по центру» и по горизонтали, и по вертикали (см. Лабораторная работа №1). Теперь подготовим таблицу экспериментальных данных (Рис. 31). Введите данные первой колонки – номера по порядку вводятся с помощью маркера заполнения (черной точки в углу табличного курсора). Заголовок столбца «Температура» вводится по формуле: =A2 (т.е. в формуле ссылка на адрес ячейки, Рис. 31 где помещено название параметра в таблице «Исходные данные» (Рис. 30)). Начальная температура у нас равна исходной температуре, т.е. =B2. Далее она отличается от предыдущей на шаг. Следовательно, во второй строке температура равна начальной температуре + шаг, т.е. =E3+$B$3. Чтобы далее использовать маркер заполнения для копирования формулы, а шаг должен браться постоянно из одной и той же ячейки, мы делаем ссылку на нее абсолютной. Введенная таким образом информация позволяет нам автоматизировать подстройку таблицы экспериментальных данных к изменению начальных условий. Попробуйте вместо «температуры» в исходных данных ввести «давление», а исходное значение поставить, например, 20. Значения Yэкпер должны браться из эксперимента, поэтому просто введите их с клавиатуры. Для форматирования заголовка «Эксперимент» лучше всего воспользоваться возможностью копирования формата. Для этого: - 27 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Рис. 32 Рис. 33 выберите ячейку «Исходные данные»; щелкните на кнопке «Формат по стандартной панели образцу» инструментов. При этом Excel копирует формат выбранной ячейки, а указатель мыши превращается в кисточку, слева от которой расположен знак «плюс»; протащите указатель мыши по диапазону ячеек, в которые нужно скопировать выбранный формат. Для того чтобы построить график зависимости Yэкпер от температуры, выделите эти два столбца, включая их заголовки, и выполните команду «Вставка» ⇒ «Диаграмма» или нажмите кнопку вызова Мастера диаграмм на панели инструментов. Первое диалоговое окно «Мастер диаграмм (шаг 1 из 4) – тип диаграммы» (Рис. 32) имеет две вкладки – «Стандартные» и «Нестандартные». На этом этапе выбирается вариант строящейся диаграммы из имеющихся образцов. Чтобы построить график зависимости одной величины от другой, нужно выбрать тип диаграммы «Точечная», а затем любой из пяти ее видов. Пусть мы нанесем только точки и не будем прорисовывать линии. Щелкните по кнопке «Просмотр результата» (Рис. 32). Excel сразу покажет, как будут отображаться наши данные на готовой диаграмме. Для перехода к каждому последующему шагу Мастера диаграмм используется кнопка «Далее». Во втором окне «Мастер диаграмм (шаг 2 из 4): источник данных диаграммы» на вкладке «Диапазон данных» отображаются адрес выделенного диапазона исходных данных и образец строящейся диаграммы (Рис. 33). Выберите «в столбцах» и нажмите кнопку «Далее». - 28 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» На третьем этапе работы Мастера диаграмм (на шаге 3) в окне «Параметры диаграммы» (Рис. 34) определите характер оформления диаграммы – выполните ее форматирование. Третье окно Мастера диаграмм имеет следующие вкладки: заголовок – позволяет ввести текст заголовка диаграммы и подписи осей; оси – позволяет определить Рис. 34 отображение и маркировку осей координат; линии сетки – позволяет определить тип линий и характер отображения сетки; легенда – позволяет отобразить или скрыть легенды и определить ее место на диаграмме. Легенда – Рис. 35 это небольшое подокно на диаграмме, в котором отображаются названия рядов данных и образцы их раскраски на диаграмме (в виде ключа Легенды); подписи данных – позволяет управлять отображением надписей, соответствующих отдельным элементам данных на диаграмме; таблица данных – позволяет добавить или скрыть на диаграмме таблицу данных, использованную для построения диаграммы. Последнее (четвертое) окно мастера диаграмм (Рис. 35) служит для определения ее размещения в рабочей книге. Рекомендуется выбрать ее размещение на отдельном листе, т.к. в этом случае диаграмму легче вставлять в другие документы, она не загораживает исходные данные, она более читабельна и т.д. Как только вы определили все необходимые параметры, нажмите кнопку «Готово». Excel построил нам диаграмму (Рис. 36), используя некий набор параметров, таких как цвет области построения (области, в которой отображена сама диаграмма, без заголовков, легенды и других элементов), шрифт, шкалы, размер точек и т.д., по умолчанию. Чтобы изменить тот или иной параметр форматирования диаграммы, необходимо щелкнуть по нему правой кнопкой мыши и в открывшемся контекстном меню выбрать соответствующую команду. Сделайте фон области построения диаграммы белым. Для этого щелкните по области построения диаграммы правой кнопкой мыши и выберите команду «Формат области построения». В открывшемся диалоговом окне (Рис. 37) поставьте переключатель в группе заливка «обычная». Нажмите кнопку Ok. - 29 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Y=f(x) 12 10 8 Y 6 4 2 0 0 20 40 60 80 100 120 X Yэкспер Рис. 36 Рис. 37 Рис. 38 Увеличьте размер точек. Для этого щелкните по точкам правой кнопкой мыши и выберите команду «Формат ряда данных». В открывшемся диалоговом окне (Рис. 38) в группе «размер» установите размер точек например 8 пт. Здесь же можно выбрать другие параметры данных, например, изменить маркер, т.е. вид точек, провести линию, выбрав ее цвет, толщину и тип, сгладить линию и др. Наш аргумент функции изменяется от 10 до 100, а шкала по оси X имеет минимальное значение 0, а максимальное – 120. Кроме того, шрифт подписи данных мелковат. Как изменить это? Щелкните по оси X правой кнопкой мыши и выберите команду «Формат оси». В открывшемся диалоговом окне (Рис. 39) на вкладке «Шкала» установите минимальное значение – 10, максимальное – 100 и цену основных делений – 10, т.к. наши данные изменяются с шагом 10. В этом же окне на вкладке «Шрифт» можно увеличить размер шрифта и изменить его начертание, например, увеличить до 8 пт и сделать курсивом. На складке «Выравнивание» можно установить вертикальное написание подписей. Аналогичным образом можно - 30 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» отформатировать ось Y. Сделайте это. Вы уже поняли принцип форматирования диаграммы? Тогда увеличьте размер шрифта заголовка, настройте легенду, названий осей. Пример отформатированной диаграммы представлен на рисунке (Рис. 40). Чтобы предсказывать значения отклика – параметра Y на выходе эксперимента от фактора – независимых переменных X на входе в систему (в нашем случае это температура), необходимо знать функциональную зависимость Y = f (X). В Excel имеется возможность автоматического подбора такой функции. Рис. 39 Yэкспер Y=f(x) 12 10 8 Y 6 4 2 100 90 80 70 60 50 40 30 20 10 0 X Рис. 40 Щелкните правой кнопкой мыши по точкам и выберите в контекстном меню «Добавить линию тренда». В открывшемся диалоговом окне (Рис. 41) на закладке «Тип» выберите тип линии тренда. Обычно для описания системы используется полиномиальная линия тренда второго порядка: Y = a0 + a1 * X + a2 * X 2 , (1) где ai – коэффициенты уравнения. При необходимости можно изменить степень до 6. Тогда уравнение примет вид: - 31 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Y = a0 + a1 * X + a2 * X 2 + a3 * X 3 + + a4 * X 4 + a5 * X 5 + a6 * X 6 . (2) Рис. 41 Рис. 42 На вкладке «Параметры» установите переключатели в пункты «показать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации» (Рис. 42). Это позволит вам увидеть уравнение и точность аппроксимации наших данных. Выведенные на экран уравнение и точность можно переместить в любое место диаграммы (также как и другие надписи, например, заголовка диаграммы, названия осей, легенду), «схватив» левой кнопкой мыши за рамку. Примерный окончательный вид нашей зависимости представлен на рис. 43. Сохраните результаты упражнения 1, они понадобятся нам позднее (см. ниже Лабораторная работа № 6). y = -0.0054x2 + 0.6014x - 5.9667 R2 = 0.9817 Y=f(x) Yэкспер Полиномиальный (Yэкспер) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Рис. 43 - 32 - 70 80 90 100 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Создание и редактирование поверхностей в документе Excel В предыдущем упражнении мы рассматривали возможности визуализации однопараметрической зависимости (функция зависит только от одной переменной). В реальности, такие простые зависимости встречаются достаточно редко. Чаще приходится сталкиваться с многопараметрическими функциями. Как их визуализировать, рассмотрим на примере двухпараметрической задачи. Пусть у нас имеется уравнение: Z = sin X 2 − Y 2 + 5 ⋅ X ⋅ Y , (3) где X и Y меняются от -5 до 5 с шагом 1. Необходимо построить график поверхности полученных значений Z. Для этого сначала необходимо построить матрицу данных (Рис. 44). () Рис. 44 В ячейку B1 введите первое значение Y = -5. Затем выполните команду «Правка» ⇒ «Заполнить» ⇒ «Прогрессия…». В открывшемся диалоговом окне (Рис. 45) установите: «Расположение» − по строкам, «Шаг:» равный 1 и «Предельное значение:» равное 5. После этого нажмите кнопку Ok. Точно таким же образом Рис. 45 заполняются значения X в столбце А, за тем лишь исключением, что «Расположение» должно быть по столбцам. Выполните это. После того, как значения аргументов введены в таблицу, заполните ячейку B2 формулой для расчета Z (3). Функция Sin находится в категории математические «Мастера функций». - 33 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Не забывайте, что формула должна содержать смешанные ссылки, так как значения X должно постоянно выбираться из столбца А, а значения Y − из строки 1. Для заполнения всей таблицы воспользуйтесь маркером заполнения. Данные для построения поверхности готовы, осталось их только построить на диаграмме. Как и в предыдущем упражнении воспользуемся «Мастером диаграмм» (Рис. 32 – 35). Сначала выделите матрицу значений функции (выделять Рис. 46 значения X и Y не надо!), откройте мастер диаграмм любым известным вам способом и выберите тип диаграммы «Поверхность». Далее построение поверхности от построения графика ничем не отличается. Окончательный вид диаграммы будет примерно таким, как показано на рис. 46. Повернуть или настроить диаграмму можно в диалоговом окне «Формат трехмерной поверхности» (Рис. 47), которое Рис. 47 открывается щелчком правой кнопкой мыши по стенкам поверхности и выбором пункта контекстного меню «Объемный вид…». - 34 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №5 Упражнение 1 Логические выражения в Excel Введем в ячейку А1 формулу =7>5. Она вернет значение ИСТИНА. Скопируем содержимое А1 в А2 и исправим в А2 формулу: =3>5. Эта формула вернет значение ЛОЖЬ. Правые части обеих формул представляют собой высказывания, т.е. утверждения, относительно которых можно заключить, верны они или нет. Рассмотрим другой пример. Введем в ячейку А4 число 2, а в ячейку В4 формулу =А4>3. Формула возвращает значение ЛОЖЬ. Введем в А4 число 6. Формула возвращает значение ИСТИНА. В В4 записан предикат, т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения ИСТИНА и ЛОЖЬ. В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящееся в ячейке А4, превышает 3?» В зависимости от значения А4 ответ будет ДА (ИСТИНА) или НЕТ (ЛОЖЬ). В формуле =А4>3 ее составные части (А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =(А4^2-1)>(2*А4+1). В этом выражении скобки можно опустить, потому что арифметические операции имеют более высокий приоритет, чем операции сравнения, но скобки придают формуле наглядность. Операции сравнения сведем в табл. 1. Таблица 1 > больше >= больше или равно < <= меньше или равно меньше = <> равно не равно Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками: > и =. Причина в том, что на клавиатуре нет знака ≥. Высказывание и предикат имеют общее название − логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции (НЕ, И, ИЛИ). У логических функций аргументы могут принимать только два значения: ИСТИНА и ЛОЖЬ. Функция НЕ может иметь только один аргумент, а функции И и ИЛИ могут иметь два и более аргументов. Пример 1 В ячейке А1 (с именем z) запишите любое число. Выясните, принадлежит ли оно отрезку . Решение. Присвоим ячейке А1 имя z («Вставка» ⇒ «Имя» ⇒ «Присвоить»). Введем в А1 число 3. Для того чтобы z принадлежал отрезку , нужно, чтобы одновременно были истинны два предиката: z ≥ 2 и z ≤ 5 . В ячейке В1 разместим - 35 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» формулу =И(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В В1 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2<=z<=5. Введите эту формулу в С1 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности. Пример 2 В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-∞,2) или (5,∞). Решение. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z > 5. В ячейке D1 разместите формулу =ИЛИ(z<2;z>5). А1 содержит число 3, поэтому формула возвращает ЛОЖЬ. Задачу можно было решить иначе с учетом того обстоятельства, что на рабочем листе есть формула проверки принадлежности числа z отрезку . Упомянутые два луча составляют на числовой оси дополнение к этому отрезку. Введем в ячейку Е1 формулу =НЕ(В1). Убедитесь, вводя в ячейку А1 различные числа, что формулы в ячейках D1 и Е1 дают идентичные результаты. На практике «в чистом виде» логические выражения, как правило, не используются. Логическое выражение служит первым аргументом функции ЕСЛИ: ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь) Во втором аргументе записывается выражение, которое будет вычислено, если лог_выражение возвращает значение ИСТИНА, а в третьем аргументе − выражение, вычисляемое, если лог_выражение возвращает ЛОЖЬ. Пример 3 1. Введем в ячейку A2 формулу, которая возвращает z+1, если z >1, и z в противном случае: = ЕСЛИ(z>1;z+1;z). (В Мастере функций ЕСЛИ находится в категории «Логические», также как функции И, ИЛИ, НЕ.); 2. Если z > 60, то в ячейке В2 выводить сообщение «Превышено пороговое значение», в противном случае выводить z: =ЕСЛИ(z>60;"Превышено пороговое значение";z) Обратите внимание, что текст в формулах вводится в кавычках. 3. Если z ∈ , то возвращать z, если z < 10, то возвращать 10, если z > 25, то возвращать 25. Выражение для этого условия будет выглядеть примерно следующим образом (запишем формулу в С2): =ЕСЛИ(z<10;10;ЕСЛИ(z<=25;z;25)) Теперь попробуйте менять значение z в ячейке А1, следя за тем как меняются значения в ячейках с формулами. - 36 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Итоговые функции в Excel Некоторые функции Excel возвращают одно значение, в то время как аргументом имеют блок или несколько блоков. Такие функции будем называть итоговыми. Наиболее часто используемой из таких функций является СУММ − для ее ввода на панели «Стандартная» даже размещена специальная кнопка. Эта функция как бы подводит итог колонке чисел − отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические». Функция СУММ допускает до 30 аргументов. Поэтому с ее помощью можно находить сумму чисел из нескольких блоков. Допустима, например, такая формула =СУММ(В2:В9;12;-4.96;А4:С18). Если в блоке в какой-либо ячейке находится текстовое значение, то оно считается равным нулю. Кроме суммы к итоговым функциям относятся, например: МАКС и МИН − вычисление максимального и минимального значений, СРЗНАЧ − среднее арифметическое значение и т.д. Прочие итоговые функции вы можете найти в «Справке». К итоговым можно отнести функции И и ИЛИ. Пример 4 данные Имеются метеостанции – количество осадков (в мм) (Рис. 48). Необходимо получить суммарное, максимальное, минимальное и среднемесячное количество осадков. Решение. Введите формулы: в В17 =СУММ(В3:В14); в В18: =МАКС(В3:В14); в В19: =МИН(В3:В14); в В20: =СРЗНАЧ(В3:В14). Далее эти формулы скопированы в С17:D20. В блоке Е17:Е20 подведены итоги за три года. В Е17 формула =СУММ(В17:D17), в Е18: т.д. На =MAKC(B18:D18) и Рис. 48 Рис. 49 - 37 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» результаты наложен формат: одна цифра после десятичного разделителя (кнопка «Уменьшить разрядность»). Существует две полезные функции, примыкающие к итоговым: СЧЕТЕСЛИ и СУММЕСЛИ. Их названия показывают, что они считают и суммируют не все данные, а только удовлетворяющие некоторому критерию. Функция СЧЕТЕСЛИ (интервал; критерий) подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Пример 5 Вычислим количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков (Рис. 49). В ячейке В22 формула =СЧЕТЕСЛИ(В3:В14;"<10"). Критерий взят в двойные кавычки, как текстовая строка. Формула скопирована в C22:D22. В Е22 подсчитана сумма. Функция СУММЕСЛИ(интервал;критерий;сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. Предположим, нужно вычислить суммарные осадки, которые выпали в незасушливые месяцы. Дополним таблицу (Рис. 49). В ячейку В23 введена формула =СУММЕСЛИ(B3:B14;">=10"), далее скопированная в C23:D23. С помощью этой функции можно решить более трудную задачу: каково суммарное количество осадков было в 1993 г. в те месяцы, которые в 1994 г. были засушливыми. Решение дается формулой =СУММЕСЛИ(D3:D14;"<10"; С4:С15), которая возвращает значение 128,6. Поместите ее в ячейку В24. К итоговым можно отнести еще две функции: НАИБОЛЬШИЙ(блок;k) и НАИМЕНЬШИЙ(блок;k). Первая из этих функций возвращает k-e наибольшее значение из множества данных, а вторая − наименьшее. Пример 6 В ячейки A1:D1 введем набор чисел. В блок А2:А5 введем формулы, которые показаны в соседнем столбце Рис. 50 (Рис. 50). Для понимания работы функции важно отметить, что третье наибольшее значение в блоке не 2, как можно было бы подумать, а 6, т.е. совпадает со вторым наибольшим значением. И еще: если в блоке n элементов, то функция НАИБОЛЬШИЙ(блок,n) возвращает минимальное значение, что мы и видим в примере. Обратите внимание, что в А2:А5 получен исходный массив чисел, отсортированный по убыванию. Если в исходном блоке изменить какое-либо число, - 38 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» то блок А2:А5 будет автоматически перестроен. В этом отличие от операции сортировки, которая проводится самим пользователем при необходимости. Обязательно сохраните результаты примеров 4-6, так как мы будем их использовать позднее. Упражнение 3 Табличные формулы в Excel Табличные формулы − очень мощное средство Excel, позволяющее в формулах обращаться с блоками, как с обычными ячейками, давать компактные решения сложных задач. В некоторых задачах вообще невозможно обойтись без табличных формул. Пример 7 Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные (Рис. 51). Рис. 51 В ячейку D2 можно набрать формулу: =В2-С2, а затем скопировать ее в D3:D5. В этих ячейках появятся формулы =В3-С3 и т.д. Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора. Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым и третьим столбцами таблицы (В1:С5) и дайте команду меню «Вставка» ⇒ «Имя» ⇒ «Создать». Диапазон В2:В5 получит имя «Приход», а диапазон С2:С5 имя «Расход». Ввод табличной формулы с использованием имен диапазонов. Прежде мы вводили формулу в отдельную ячейку. А сейчас введем ее в диапазон. Подробно опишем шаги. Выделим блок D2:D5. В этом блоке активна ячейка D2. Наберем знак равенства =. Нажмем функциональную клавишу F3. Появится диалоговое окно «Вставка имени». Выберем имя «Приход» и щелкнем Оk. Формула примет вид: =Приход. Наберем знак минус -. Вновь нажмем клавишу F3. В диалоговом окне «Вставка имени» выберем имя «Расход» и щелкнем Ok. Формула примет вид: =Приход-Расход. Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}. - 39 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прокомментируем шаги. На третьем и пятом шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще и нет риска ошибиться в наборе имени. На шестом шаге мы нажимаем не Enter, как ранее при вводе формулы, a Shift+Ctrl+Enter (при нажатии клавиши Enter должны быть нажаты обе клавиши Shift и Ctrl). Это очень важно. Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока D2 (Проверьте!). Фигурные скобки, окружающие формулу, говорят о том, что это табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст). Ввод табличной формулы. Разумеется, табличную формулу можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12. Повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9:С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы. Выделение блока с табличной формулой. Выделите одну из ячеек блока и нажмите клавишу F5 (эквивалент пункта меню «Правка» ⇒ «Перейти»). В диалоговом окне щелкните по кнопке «Выделить», установите переключатель «Текущий массив». Изменение табличной формулы. Попытайтесь очистить одну из ячеек, занятую табличной формулой. Например, выделите ячейку D8 и нажмите клавишу Del. В этом случае должно появится сообщение «Нельзя изменять часть массива». Удалить блок можно только целиком. Отредактировать формулу можно так: выделить блок с формулой, нажать функциональную клавишу F2, внести изменения в формулу, нажать сочетание клавиш Shift+Ctrl+Enter. (Попробуйте, например, ввести формулу {=Приход-Расход-1}, потом отмените это.) Коррекция табличной формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год - 1996, приход - 240, расход – 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, мы бы поступили просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат 50, т.е. число из первой ячейки блока с табличной формулой. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13:В16-С13:С16} – образовался второй блок, что вовсе не входило в наши планы. Удалим формулы в ячейках D6 и D13. Правильное решение для первой и второй таблиц разное. Для первой таблицы изменим именованные блоки (выделим В1:С6 и «Вставка» ⇒ «Имя» ⇒ «Создать», для каждого имени Excel задаст вопрос: «Заменить» существующее определение имени?» Отвечаем «Да»). Выделяем D2:D6, нажимаем клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажимаем клавиши Shift+ Ctrl+Enter. Для второй таблицы выделяем D8:D13, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В8:В12 и выделим блок В8:В13, также поступим с блоком С8:С12 либо просто заменим в адресах блоков цифру 2 на цифру 3. Нажимаем сочетание клавиш Shift+Ctrl+Enter. - 40 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Коррекция табличной формулы при уменьшении блока. Теперь мы хотим удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменяем поименованные блоки (в ячейке D6 результат отображается как #Н/Д - недоступно). Выделяем блок с табличной формулой, нажимаем клавишу F2 и добавляем в самое начало формулы апостроф (он расположен на клавише с буквой "Э"). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Shift+Ctrl+Enter. Аналогично поступаем со второй таблицей. Как видим, процедура непростая и неприятная. Для решения задачи проще было воспользоваться простыми формулами. Но применение табличных формул, как мы убедимся, дает такие дополнительные возможности, что с неудобствами, связанными с изменениями этих формул, придется смириться. Упражнение 4 Дистрибутивные функции в Excel В Excel можно к блоку применить функцию (большое множество функций Excel), с тем, чтобы она вернула новый блок, содержащий значения функции для элементов исходного блока. Пример 8 Пусть в блоке А1:А4 записаны числа 1, 4, 9, 16. Поместим в B1:В4 табличную формулу {=КОРЕНЬ(А1:А4)}. Будет выведен столбец значений: 1, 2, 3, 4. Можно считать, что функция КОРЕНЬ была применена к вектору из четырех компонент и вернула новый вектор. (Разумеется, тот же результат можно было получить, записав в В1 формулу =КОРЕНЬ(А1) и скопировав ее в блок В2:В4.) Теперь рассмотрим функцию, которую нельзя применять к блоку. Поместим в D1:D2 логические значения ИСТИНА и ЛОЖЬ. В блок Е1:Е2 запишем табличную формулу {=И(D1:D2;”ИСТИНА”)}. Эта формула вернет значение ЛОЖЬ во всех ячейках блока Е1:Е2. Получается, что функцию КОРЕНЬ можно применять к массиву, а функцию И – нет. Функции, которые можно применять к списку, называются дистрибутивными. Продолжим пример с вычислением квадратного корня от элементов блока. Мы хотим вычислить сумму корней ∑ ai . Поместим в ячейку В5 формулу =СУММ(В1:В4). Результат, разумеется, 10. А теперь вычислим эту же сумму, не используя промежуточный блок В1:В4. Поместим в ячейку А5 табличную формулу {=СУММ(КОРЕНЬ(А1:А4))}. Обратите внимание, хотя формула возвращает значение в одной ячейке, она должна вводиться как табличная, т.е. ее ввод заканчивается нажатием комбинации клавиш Shift+Ctrl+Enter. Для сравнения введите в А6 эту формулу как обычную, – она вернет сообщение об ошибке #ЗНАЧ!. - 41 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Итак, к массиву, возвращаемому дистрибутивной функцией, можно применить итоговую функцию, которая возвращает всего одно значение. Но вводить такую формулу нужно как табличную. Пример 9 Рис. 52 Функцию ИЛИ можно использовать в табличных формулах, но как итоговую, т.е. если ИЛИ имеет всего один аргумент и этот аргумент – блок. Пусть в блоке А1:В2 находятся названия планет (Рис. 52). В ячейку D2 введем табличную формулу {=ИЛИ(СОВПАД(А1:В2;D1))}. Функция СОВПАД возвращает логическое значение ИСТИНА или ЛОЖЬ в зависимости от совпадения или несовпадения своих аргументов – текстовых строк. Если бы в А4:В5 была введена формула массива {=СОВПАД(А1:В2;D1)}, то она вернула бы четыре значения (Рис. 52). Функция ИЛИ(А4:В5) возвращает значение ИСТИНА. Эти две формулы мы объединяем в одну табличную формулу, которую и ввели в D2. Пример 10 Вернемся к задаче обработки данных метеостанции (пример 4 и пример 5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:H14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводим формулу =ЕСЛИ(И(В3>20;В3<80);1;0) и копируем ее в остальные ячейки блока F3:H14. В блоке выводятся нули и единицы. Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 – выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 – в противном случае. Остается подсчитать сумму таких месяцев. Для этого введем в ячейку F25 формулу =СУММ(F3:F14) и скопируем ее в блок G25:H25. А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ(ЕСЛИ(В4:В15>20;ЕСЛИ(В4:В15<80;1;0);0))} и скопируем ее в C25:D25. (Таким образом, машина, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае – проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном - 42 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» случае – 0). Формула {=СУММ(ЕСЛИ(И(В4>20;В4<80);1;0))} не приведет к успеху, так как функция И не является дистрибутивной). Этот пример очень важен. Он Рис. 53 дает рецепт, как подсчитать в множестве количество элементов, удовлетворяющих определенному критерию. Нужно составить на основе вложенных функций ЕСЛИ индикаторную функцию подмножества и применить к ней итоговую функцию СУММ, введя формулу как табличную. Теперь вычислим суммарное количество осадков, выпавших в эти месяцы Введите в А26 текст «Осадки в нормальные месяцы», в В26 – табличную формулу {=СУММ(ЕСЛИ(В3:В14>20; ЕСЛИ (В3: В14<80;В3:В14;0);0))} и скопируйте ее в C26:D26. В E25 и Е26 введите формулы для суммирования значений в строках (выделите блок В25:Е26 и щелкните кнопку «Автосумма»). Вы получите блок, показанный на рис. 53. Пример 11 В блоке А1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей. Решение. Перейдите на новый лист. Запишите любую последовательность чисел в блок А1:А10. Окончательное решение можно записать одной формулой (поместите его в ячейку A12). {=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=СЧЕТ(А1:А10)1;"возрастающая";"не является возрастающей")} Разберем теперь эту формулу: А2:А10-А1:А9 (т.е., из А10 вычитается А9, из А9 вычитается А8 и т.д.) – образует блок, состоящий из первых разностей элементов исходного блока; ЕСЛИ(А2:А10-А1:А9>0;1;0) – составляет блок из индикаторов положительных первых разностей; СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0)) – считает количество ненулевых элементов в блоке индикаторов; СЧЕТ(А1:А10)-1 – рассчитывает размер блока индикаторов, равный уменьшенному на 1 размер исходного блока; если количество ненулевых элементов в блоке индикаторов равно размеру блока индикаторов, то последовательность возрастающая, иначе – нет. Попробуйте поэтапно строить соответствующие блоки и итоговые функции от них, чтобы добиться ясного понимания, как составлена итоговая формула. - 43 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 5 Матричные операции в Excel Простейшие операции, которые можно проделывать с матрицами: сложение (вычитание), умножение на число, перемножение, транспонирование, вычисление обратной матрицы. Пример 12 Сложение матриц и умножение матрицы на число. Сложить матрицы M и N, где − 1 0 4  2 − 3 7 M = и N =   2 − 3 5 .    − 1 5 6 Решение. Введем матрицы М и N в блоки А1:С2 и Е1:G2. В блок А4:С5 введем табличную формулу {=А1:С2+E1:G2}. Обратите внимание, что выделен блок, имеющий те же размеры, что и исходные матрицы. Что произойдет, если перед вводом формулы выделить блок A4:D6? В «лишних» ячейках появится #Н/Д, т.е. «НеДоступно». А если выделить А4:В5? Будет выведена только часть матрицы, без каких-либо сообщений. Проверьте. Использование имен делает процедуру ввода табличной формулы намного проще. Дайте диапазонам А1:С2 и E1:G2 имена М и N соответственно (выполните команду для каждого блока «Вставка» ⇒ «Имя» ⇒ «Присвоить»). В блок E4:G5 введите табличную формулу {=М+N}. Результат, естественно, должен получиться тот же. Теперь вычислим линейную комбинацию матриц 2М-N. В блок А7:С8 введем табличную формулу {=2*М-N}. У Вас должны получиться результаты:  5 − 6 10 1 − 3 11 M +N = и 2 M − N = − 4 13 7  .    1 2 11 Рассмотренные примеры подводят нас к мысли, что обычная операция умножения применительно к блокам не вполне эквивалентна перемножению матриц. И действительно, для матричных операций в Excel предусмотрены функции, входящие в категорию «Математические»: МОПРЕД – вычисление определителя матрицы; МОБР – вычисление обратной матрицы; МУМНОЖ – перемножение матриц; ТРАНСП – транспонирование. Первая из этих функций возвращает число, поэтому вводится как обычная формула. Остальные функции возвращают блок ячеек, поэтому они должны вводиться как табличные формулы. Первая буква «М» в названии трех функций − сокращение от слова «Матрица». Пример 13 Вычислите определитель и обратную матрицу для матрицы - 44 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» − 73 78 24 A =  92 66 25 .   − 80 37 10  Проверьте правильность вычисления обратной матрицы умножением ее на исходную. Повторите эти действия для той же матрицы, но с элементом а33=10,01. Решение. Разместим исходную матрицу в блоке А1:С3. В ячейке В5 поместим формулу для вычисления определителя =МОПРЕД(А1:С3). В блок А7:С9 введем формулу для вычисления обратной матрицы. Для этого выделим блок А7:С9 (он имеет три строки и три столбца, как и исходная матрица). Введем формулу {=МОБР(А1:С3)}. Даже если вы используете Мастер функций, нужно завершать ввод нажатием комбинации клавиш Shift+Ctrl+Enter (вместо щелчка по кнопке «Оk»). Если Вы забыли предварительно выделить блок А7:С9, а ввели формулу в ячейку А7 как обычную формулу ввод Excel (закончив нажатием Enter), то не нужно вводить ее заново: выделите А7:С9, нажмите клавишу F2 (редактирование), но не изменяйте формулу, просто нажмите клавиши Рис. 54 Shift+Ctrl+Enter. Скопируйте блок А1:С9 в блок E1:G9. Чуть-чуть измените один элемент исходной матрицы: в ячейку G3 вместо 10 введите 10,01. Изменения в определителе и в обратной матрице разительны! Этот специально подобранный пример иллюстрирует численную неустойчивость вычисления определителя и обратной матрицы: малое возмущение на входе дает большое возмущение на выходе. Для дальнейших вычислений присвоим матрицам на рабочем листе имена: А1:С3 – А, А7:С9 – Ainv, E1:G3 – АР, E7:G9 – APinv. Чтобы в уже введенных формулах появились эти имена, выделите соответствующие формулы, выберите в меню пункт «Вставка» ⇒ «Имя» ⇒ «Применить», выделите в диалоговом окне нужные имена и щелкните «Оk». Теперь проверим правильность вычисления обратной матрицы. В блок А12:С14 введем формулу {=MУMHOЖ(A,Ainv)}, a в блок E12:G14 – формулу {=МУМНОЖ(АР,АPinv)}. У вас должен получиться результат, как на рис. 54. Как и следовало ожидать, получились матрицы, близкие к единичным. - 45 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Заметим, что набор матричных операций в Excel беден. Если вам нужно серьезно работать с матрицами, лучше прибегнуть к помощи таких математических пакетов как MatLAB (Matrix LABoratory), Mathematica, Derive. - 46 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа № 6 Упражнение 1 Поиск решения В лабораторной работе № 4 мы рассматривали пример автоматического нахождения функциональной зависимости Y = f(X). Напомним, что нахождение подобной зависимости необходимо для предсказания значений отклика – параметра Y на выходе эксперимента от фактора – независимых переменных X на входе в систему (см. Лабораторная работа № 4). В некоторых Рис. 55 случаях представленных в Excel функций бывает недостаточно. Поэтому важно уметь подобрать такую функцию самостоятельно, используя какой-нибудь из математических методов оптимизации, например метод наименьших квадратов. Суть его состоит в том, чтобы минимизировать сумму квадрата разности экспериментальных (Yэкпер) и расчетных (Yрасчет) данных: n ∑ (Yэкпер,i − Y расчет,i) 2 , i =1 (4) где n в нашей задаче было равно 10. Откройте задачу лабораторной работы № 4 и продолжите заполнение таблицы. Экспериментальные Y уже введены. Теперь заполним таблицу расчетными Y. Для этого нам понадобится дополнительная таблица коэффициентов, значения которых мы для начала приравняем к 1 (Рис. 55). Теперь введите формулу полинома второй степени (1) для Yрасчет (Рис. 55). Далее задача заключается в том, чтобы Рис. 56 подобрать коэффициенты уравнения так, чтобы разница между Yрасчет и Yэкспер была минимальной. Для этого нужно ввести формулу - 47 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» расчета квадрата разности (3) и формулу расчета критерия Пирсона для оценки точности нашего расчета (Рис. 56). И та, и другая формулы являются встроенными в Excel и служат примером функций, для которых можно обойтись без ввода табличных формул (см. выше Лабораторная работа № 4). Откройте Мастер функций любым известным вам способом. В категории «Математические» выберите формулу СУММКВРАЗН и нажмите Ok. Во втором окне Мастера функций в Рис. 57 качестве массива_x введите массив Yэкспер, в качестве массива_y – массив Yрасчет и нажмите Ok. Формула для расчета критерия Пирсона находится в категории «Статистические» (функция ПИРСОН). Во втором окне Мастера функций в качестве массива_x также введите массив Yэкспер, в качестве массива_y – массив Yрасчет и нажмите Ok. Чтобы найти значения коэффициентов, в Excel имеется надстройка «Поиск решения», которая позволяет решать задачи отыскания наибольших и наименьших значений, а также решать различные уравнения. Выделите ячейку, где введена формула расчета квадрата разности и выполните команду «Сервис» ⇒ «Поиск решения». Если в меню «Сервис» такая команда отсутствует, то нужно сначала выполнить команду «Сервис» ⇒ «Надстройки» и в открывшемся диалоговом окне поставить переключатель в графе «Поиск решения» (Рис. 57), и только затем выполнить команду «Сервис» ⇒ «Поиск решения». В диалоговом окне «Поиск решения» (Рис. 58) введите параметры: адрес целевой ячейки с подбираемым значением (адрес ячейки с формулой суммы квадрата разности), если вы заранее выделили ее, то адрес помещается автоматически; в поле «Равной:» установите переключатель на «минимальному значению»; - 48 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» в поле «Изменяя ячейки» введите диапазон ячеек изменяемых коэффициентов. Кнопка «Параметры» служит для изменения и настройки параметров поиска. В Рис. 59 их число входят: способ решения задачи, время проведения вычислений и точность результатов. Однако в большинстве случаев достаточно использовать настройки по умолчанию. Поиск решения осуществляется после щелчка по кнопке «Выполнить». Если поиск решения успешно завершен, то результаты вычислений заносятся в исходную таблицу, а на экране появляется диалоговое окно «Результаты поиска решения» (Рис. 59), с помощью которого можно сохранить найденные решения в исходной таблице, восстановить исходные значения, сохранить результаты поиска решения в виде сценария, сформировать отчет по результатам выполнения операции поиска решения. Сравните полученные значения коэффициентов с коэффициентами в уравнении линии тренда. Добавьте расчетные значения Y на график. Для этого перейдите в окно диаграммы, щелкните правой кнопкой мыши в любом ее месте и выберите в контекстном меню команду «Исходные данные». В открывшемся одноименном диалоговом окне (Рис. 60) перейдите на закладку «Ряд» и нажмите кнопку «Добавить». В поле «Имя» щелкните по кнопке свертывания окна, Рис. 60 перейдите на лист с вашими данными, выделите ячейку заголовка столбца Yрасчет и вернитесь в окно с помощью кнопки разворачивания окна. Аналогичным образом Рис. 58 - 49 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» введите «Значения X» (диапазон ячеек со значениями X или температуры) и «Значения Y» (диапазон ячеек со значениями расчетного Y). По окончании ввода нажмите кнопку Ok. Обратите внимание, что точки Yрасчет легли на линию тренда, построенную нами ранее (Рис. 61). В завершение обязательно сохраните свой файл, мы будем его использовать на следующем уроке (см. ниже Лабораторная работа №7). y = -0.0054x2 + 0.6014x - 5.9667 2 R = 0.9817 Y=f(x) Yэкспер Yрасчет Полиномиальный (Yэкспер) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Рис. 61 - 50 - 70 80 90 100 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Лабораторная работа №7 Упражнение 1 Поиск решения двухпараметрической задачи в Excel В предыдущих уроках мы рассматривали возможности обработки и визуализации однопараметрической зависимости (функция зависит только от одной переменной). В реальности такие простые зависимости встречаются достаточно редко. Чаще приходится сталкиваться с многопараметрическими функциями. Как обрабатывать такие зависимости и каким образом их визуализировать, рассмотрим на примере двухпараметрической задачи. Пусть был проведен эксперимент, например, измерили зависимость какого-то параметра от температуры и давления. Средняя температура была равна 100оC. Шаг изменения – 50оС. Среднее давление – 2 атм. Шаг изменения – 1 атм. Такая система будет описываться зависимостью: Y = f (X1, X 2) , (5) являющейся поверхностью, которую часто показывают в виде, подобном контурной карте (Рис. 62). Рис. 62 - 51 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Чтобы найти эту зависимость для нашего случая, воспользуемся заготовкой уроков 4 и 6. Для этого откройте ваш сохраненный файл и перейдите на лист с данными. Щелкните по ярлыку Листа и выберите команду «Переместить/скопировать» (Рис. 63). В открывающемся диалоговом окне (Рис. 64) можно выбрать: куда мы хотим переместить (скопировать) наш лист (в текущую книгу или новую). Выберите название текущей книги; перед каким листом мы хотим поместить текущий лист или его копию. Выберите «(переместить в конец)». Не забудьте поставить галочку «Создать копию», в противном случае лист просто переместиться в конец книги. После чего нажмите Ok. По умолчанию Excel создает копию с именем текущего листа, добавляя в конце в скобках номер копии. Для удобства переименуем его. Для этого щелкните мышкой по ярлыку листа и выберите команду «Переименовать» (Рис. 63); введите новое имя, например, «Эксперимент_2» и нажмите клавишу «Enter». Сначала перестроим таблицу исходных данных, как показано на рис. 65. Выделите две ячейки в верхней части старой таблицы (те, в которых было помещено название параметра «Температура» и ее значение) и выполните команду «Вставка» ⇒ «Ячейки…». При этом откроется диалоговое окно «Добавление ячеек», в котором будет предложено их местоположение (Рис. 66). Поставьте переключатель в положение «ячейки со сдвигом вниз» и нажмите кнопку Ok. - 52 - Рис. 63 Рис. 64 Рис. 65 Рис. 66 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Выделите пустой столбец С (щелкните по заголовку этого столбца) и выполните команду «Вставка» ⇒ «Столбцы». Внесите необходимые изменения в таблицу (Рис. 65). Приведите подобным образом таблицу эксперимента к виду, показанному на рис. 67. Напомним, что заголовки столбцов «Температура» и «Давление» должны вводиться по формулам, чтобы сделать заготовку более универсальной. Рис. 67 Заполним теперь данные таблицы «Эксперимент». Координаты точек 1 – 9 можно вычислить в соответствии с рис. 62 по нижеследующим формулам: № 1 2 3 4 5 6 7 8 9 Темпер. Xср,1-Шаг Xср,1 Xср,1+Шаг Xср,1-Шаг Xср,1 Xср,1+Шаг Xср,1-Шаг Xср,1 Xср,1+Шаг Давл. Xср,2-Шаг Xср,2-Шаг Xср,2-Шаг Xср,2 Xср,2 Xср,2 Xср,2-Шаг Xср,2-Шаг Xср,2-Шаг При вводе формул не забывайте делать постоянными ссылки, чтобы использовать возможность копирования. Значения Yэкспер мы должны взять из эксперимента. Пусть они равны: № точки Yэкспер 1 1 2 7 3 5 4 17 5 25 6 15 Yрасчет должны вычисляться по формуле: Yрасчет = A0 + A1 X 1 + A2 X 2 + A11 X 12 + A12 X 1 X 2 + A22 X 22 . - 53 - 7 3 8 10 (6) 9 4 Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прежде чем вводить формулу (6), необходимо модифицировать таблицу коэффициентов, как показано на рис. 68, введя начальные значения коэффициентов 1. Для подбора функции воспользуемся методом минимизации суммы квадратов разности экспериментальных (Yэкспер) и расчетных (Yрасчет) данных, который мы рассматривали в прошлом уроке. Рис. 68 Формулы расчета квадрата разности и формула расчета критерия Пирсона у нас на листе уже есть. Теперь достаточно поправить в них ссылки и выполнить. Поиск решения выполняется также, как и в случае однопараметрической функции, но поскольку у нас зависимость более сложная, необходимо в диалоговом окне «Поиск решения» открыть подокно «Параметры» (Рис. 69) и установить следующие опции: допустимое отклонение – 1%; «Автоматическое масштабирование»; оценки – «Квадратичные»; разности – «Центральные». Рис. 69 После этого нажать кнопку Ok и в окне «Поиск решения» − «Выполнить». Если удовлетворительная точность не достигнута с первой попытки, операцию поиска решения можно повторить. Наконец, нам осталось только построить поверхность. Для этого сначала на новом листе построим матрицу данных (Рис. 70). Перейдите на новый лист и введите заголовок таблицы. - 54 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Рис. 70 Значения X и Y вычисляются по формулам. Чтобы ввести первое значение для давления введите «=», затем перейдите на лист «Эксперимент_2» и щелкните мышкой ячейке с минимальным значением давления (в нашем случае это 1) и нажмите клавишу «Enter». Те же самые действия необходимо выполнить для ввода минимального значения температуры. Последующие значения температуры и давления вычисляются по формуле: Yi = Yi −1 + Ymax − Ymin , l (7) где Xi, Yi – текущие значения температуры и давления соответственно, Xmin, Ymin – минимальные значения температуры и давления соответственно, Xmах, Ymах – максимальное значение температуры и давления соответственно, l – шаг сетки (пусть он будет равен 10). Введите формулы расчета второго значения давления и температуры. Они будут выглядеть примерно следующим образом: =B4+(Экперимент_2!$G$11-Экперимент_2!$G$3)/10. Для ввода третьего и т.д. значений температуры и давления воспользуйтесь маркером заполнения. Осталось ввести только значения функции (5). Значения коэффициентов возьмите с листа «Эксперимент_2». Не забывайте, что ссылки на коэффициенты должны быть абсолютными, а на значения температуры и давления – смешанными. Формула должна выглядеть примерно следующим образом: =Экперимент_2!$B$7+Экперимент_2!$B$8*$A5+Экперимент_2!$B$9*B$4 +Экперимент_2!$B$10*$A5^2+Экперимент_2!$B$11*$A5*B$4+ Экперимент_2!$B$12*B$4^2 Данные для построения поверхности готовы, осталось их только построить на диаграмме. - 55 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Воспользуйтесь «Мастером диаграмм», выбрав тип диаграммы «Поверхность» (см. Лабораторная работа № 4). Окончательный вид диаграммы будет примерно таким, как показано на Рис. 71. Рис. 71 - 56 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» 1. 2. 3. 4. 5. Библиографический список Фултон, Д. Освой самостоятельно Microsoft Excel 2000. 10 минут на урок. / Д. Фултон. – М.: Издательский дом «Вильямс», 2001. – 224 с. Левин, А.Ш. Excel – это очень просто! / А.Ш. Левин. – СПб.: Питер, 2004. – 74 с. Безручко, В.Т. Практикум по курсу «Информатика». Работа с Windows 2000, Word, Excel: Учеб. пособие. / В.Т. Безручко. – М.: Финансы и статистика, 2003. – 544 с. Лавренов, С.М. Excel: Сборник примеров и задач. / С.М. Лавренов – М.: Финансы и статистика, 2004. – 336 с. Воробьев, Е.С. Основы информатики. Приемы работы в среде MS Office. Учеб. пособие / Е.С. Воробьев, Е.В. Николаева, Воробьева Ф.И., Казан. гос. технол. ун-т. Казань, 2005. – 84 с. - 57 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Содержание Лабораторная работа № 1 ............................................................................................... 3 Упражнение 1. Основные понятия, связанные с работой электронных таблиц Excel.............................................. 3 Упражнение 2. Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних и верхних индексов............................. 6 Лабораторная работа № 2 ............................................................................................. 10 Упражнение 1. Закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: сортировка данных, типы выравнивания текста в ячейке, формат числа.................... 10 Упражнение 2. Введение понятия «абсолютная ссылка», установка точного значения ширины столбца при помощи команд горизонтального меню. Вставка функции при помощи мастера функций............................................................................. 13 Упражнение 3. Введение понятия «имя ячейки»................................................... 16 Лабораторная работа № 3 ............................................................................................. 19 Упражнение 1. Изменение ориентации текста в ячейке, ознакомление с возможностями баз данных Excel. Сортировка данных по нескольким ключам................................................................... 19 Лабораторная работа № 4 ............................................................................................. 27 Упражнение 1. Создание и редактирование графиков в документе Excel.......... 27 Упражнение 2. Создание и редактирование поверхностей в документе Excel .. 33 Лабораторная работа № 5 ............................................................................................. 35 Упражнение 1. Логические выражения в Excel ..................................................... 35 Упражнение 2. Итоговые функции в Excel............................................................. 37 Упражнение 3. Табличные формулы в Excel ......................................................... 39 Упражнение 4. Дистрибутивные функции в Excel ................................................ 41 Упражнение 5. Матричные операции в Excel ........................................................ 43 Лабораторная работа № 6 ............................................................................................. 47 Упражнение 1. Поиск решения................................................................................ 47 Лабораторная работа № 7 ............................................................................................. 51 Упражнение 1. Поиск решения двухпараметрической задачи............................. 51 Библиографический список.......................................................................................... 57 - 58 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Редактор: Т.М. Петрова Лицензия № 020404 от 6.03.97 г. Подписано в печать Бумага писчая. уч.-изд. л. 2005. Печать Формат 60х84 1/16 усл. печ. л. Тираж 100 экз. Заказ “C” 60 Издательство Казанского государственного технологического университета Офсетная лаборатория Казанского государственного технологического университета 420015, Казань, К. Маркса, 68

Министерство образования и науки

Российской Федерации

Федеральное государственное автономное образовательное учреждение

высшего профессионального образования

Национальный исследовательский ядерный университет «МИФИ»

Волгодонский инженерно-технический институт – филиал НИЯУ МИФИ

Создание таблиц

МЕТОДИЧЕСКИЕ УКАЗАНИЯ к лабораторной работе

по информатике в программе microsoft excel

Волгодонск 2010

УДК 519.683(076.5)

Рецензент канд. техн. наук З.О. Кавришвили

Составитель В.А. Булава

Создание таблиц . Методические указания к лабораторной работе в программе Microsort Excel. 2010. 13 с.

Методические указания содержат пояснения и рекомендации по выполнению лабораторной работы по курсу информатика в программе Microsort Excel.

_____________________________________________________________________________

ã Волгодонский институт НИЯУ МИФИ, 2010

ã Булава В.А, 2010

Лабораторная работа Создание таблиц в программе Excel с помощью автоматизации ввода данных.

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

Постановка задачи .

Вычислить значение функции y = f (x )/ g (x ) для всех х на интервале [ a , b ] с шагом к . Значение функций f (x ) , g (x ) , значение концов интервала a и b и значение шага к задается из таблицы 1 в Приложении согласно варианту для конкретной специальности.

Решение должно быть получено в виде таблиц «Основной» и «Вспомогательной».

Вычисленные значения функции у скопировать в столбец К без формул.

Запуск программы Excel осуществляется с помощью команд Пуск → Программы → Microsort Excel .

При создании таблицы в первой строке объединить ячейки А1:Н1 и в центре расположить текст «Таблицы».

Во второй строке объединить ячейки А2:Е2 и в центре расположить текст «Основная». Объединить ячейки G2:H2 и в центре расположить текст «Вспомогательная»

В ячейку А3 ввести текст «№ п/п». В ячейках В3:F3 разместить соответственно названия столбцов: х ; f (x )=…(согласно своему варианту) ; g (x )=…(согласно своему варианту) ; y = f (x )/ g (x ).

В ячейках G3:H3 разместить соответственно названия столбцов: a ; к .

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

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

Цвет шрифта у названий таблиц должен быть синий.

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

Форма отчетности .

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

Печатный вариант отчета должен содержать:

а) титульный лист;

б) цель работы;

в) постановку задачи;

г) результат выполнения задания.

2. Результат выполнения лабораторной работы в электронном виде предоставить на дискете 3,5 дюйма в виде файла с именем «Таблицы».

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

Что такое абсолютная, относительная, смешанная адресации?

Каким образом происходит автозаполнение ячеек числами, формулами?

Какие существуют способы выравнивания содержимого ячейки?

Каким образом можно изменить цвет и толщину линий внешних и внутренних границ таблицы?

Каким образом можно изменить цвет фона ячеек таблицы?

Типовой пример.

Вычислить значение функции у = х∙sin(x)/(x+1) на отрезке с шагом 0,1. Решение предоставить в виде таблицы. Вычисленные значения функции у скопировать в столбец К без формул.

Решение.

В данном случае f (x ) = x ∙ sin (x ) , g (x ) = x +1 , a =0 , b = 2 , k = 0.1

1. В первой строке таблицы выделим ячейки А1:Н1. Выполним команду Формат → Ячейки , в открывшемся окне раскроем вкладку выравнивание и выберем пункт объединение ячеек . В центре объединенных ячеек введем текст «Таблицы».

2. Аналогичным образом во второй строке объединим ячейки А2:Е2 и в центре расположим текст «Основная» и объединим ячейки G2:H2, и в центре расположим текст «Вспомогательная».

3. В третьей строке в ячейке А3 введем текст № п/п (название первогостолбца таблицы) , в ячейке В3 – х (название второгостолбца таблицы), вячейке С3 – f (x )= x ∙ sin (x ) , в ячейке D3 – g (x )= x +1 , в ячейке Е3 – у= f (x )/ g (x ) , в ячейке G3 – a , в ячейке H3 – k .

4. В ячейку А4 введем 1 и заполним ячейки А5:А24 числами от 2 до 21. Для этого выделим ячейку А4 (сделаем ее текущей), она выделится в черную рамку. Наведем курсор мыши на маркер заполнения (черный крестик в правом нижнем углу ячейки) и нажав правую кнопку мыши протянем маркер заполнения вдоль столбца А таким образом, чтобы черная рамка охватила ячейки А5:А24. Отпустив правую кнопку мыши, в открывшемся меню выберем пункт заполнить . Ячейки А5: А24 заполнятся числами 2;3;4…

5. В ячейку G4 занесем значение 0 (значение левого конца интервала).

6. В ячейку Н4 занесем значение 0,1 (величина шага).

7. Заполним столбец В значениями х :

В ячейку В4 занесем формулу =$ G $4 (начальное значение х), знак $ указывает на абсолютную адресацию. В ячейку В5 занесем формулу =В4+$ H $4. Это означает, что начальное значение х будет увеличено на величину шага;

с помощью метода автозаполнения заполним этой формулой ячейки В5:В24. Выделим ячейку В5. Наведем указатель мыши на маркер заполнения и, нажав левую кнопку мыши, протянем маркер заполнения таким образом, чтобы черная рамка охватила ячейки В5:В24. Столбец В заполнится числами 0; 0,1; 0,2;…, а в строке формул будут соответствующие формулы.

8. Заполним столбец С значениями функции f(x)=x∙sin(x). В ячейку С4 занесем формулу =В4∙sin(B4). Заполним этой формулой ячейки С5:С24 с помощью метода автозаполнения.

9. Заполним столбец D значениями функции g(x)=x+1. В ячейку D4 занесем формулу =В4+1. Заполним этой формулой ячейки D5:D24 с помощью метода автозаполнения.

10. Заполним столбец E значениями функции y=f(x)/g(x). В ячейку E4 занесем формулу =C4/D4, заполним этой формулой ячейки E5:E24 с помощью метода автозаполнения.

11. Выполним обрамление таблиц:

12. Изменим цвет фона ячеек основной и вспомогательной таблиц:

выделим основную таблицу;

введем команды меню Формат → Ячейки → Вид. В открывшемся окне выберем цвет желтый. Щелкнем по кнопке ОК.

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

13. В основной таблице полученные в результате вычислений значения у скопируем в столбец К без формул:

выделим ячейки Е4:Е24;

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

нажав правую кнопку мыши и не отпуская ее переместим указатель мыши в ячейку К4;

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

В результате выполнения работы получим таблицы:


Основная						Вспомогательная

Просмотров

Лабораторные работы по excel. Лабораторные работы по EXCEL Лабораторные работы по теме excel

Задание № 1. Таблицы MS Excel 2007.

Задание № 2. Условное форматирование.

Задание № 3. Организация таблиц.

Задание № 4. Функции.

Задание № 5. Диаграммы.

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

Лабораторная работа Создание таблиц в программе Excel с помощью автоматизации ввода данных.