Задание 1: Связывание листов рабочей книги с использованием ссылок в формулах и функций из категории ССЫЛКИ и МАССИВЫ .

1. Загрузить EXCEL. Нажать кнопку OFFIСE , и выбрать пункт ПАРАМЕТРЫ EXCEL.

2. Установить параметры отображения информации: в меню ПАРАМЕТРЫ EXCEL , на вкладке ДОПОЛНИТЕЛЬНО включить флаги: ОТОБРАЖАТЬ СЕТКУ, ЗАГОЛОВКИ СТРОК И СТОЛБЦОВ, ГОРИЗОНТАЛЬНАЯ/ВЕРТИКАЛЬНАЯ ПОЛОСА ПРОКРУТКИ, ЯРЛЫЧКИ ЛИСТОВ, ПО УМОЛЧАНИЮ : эти флаги должны быть уже включены. Выключить их. Закрыть окно настройки параметров, нажав ОК, и убедиться в том, что данные настройки применены. В некоторых случаях работа в таком режиме может быть удобна, но для выполнения данного задания удобнее включить все ранее снятые значки. Выполнить самостоятельно.

3. Переименовать листы рабочей книги, назвав их соответственно Прайс , Затраты , Предложение . В контекстном меню листа выбрать пункт ПЕРЕИМЕНОВАТЬ и задать новое имя листа, например ПРАЙС . Аналогично переименовать два других листа рабочей книги. Контекстное меню позволяет проводить и другие операции с листами рабочей книги: УДАЛИТЬ, ПЕРЕИМЕНОВАТЬ, ПЕРЕМЕСТИТЬ, КОПИРОВАТЬ, ДОБАВИТЬ . Отработать эти операции самостоятельно.

4. Набрать на листе ПРАЙС информацию, представленную на рис. 2 . Стандартная ширина колонок в таблице EXCEL равна 8 символам. Для её изменения необходимо выполнить следующие действия: установить курсор мыши на разделительную линию в заголовке столбца и убедиться, что он принял вид двунаправленной стрелки; нажать левую кнопку мыши и, удерживая её, переместить мышь влево/вправо, отпустить кнопку мыши. Также для изменения ширины столбца можно установить курсор мыши на разделительную линию в заголовке столбца и сделать двойной щелчок левой кнопкой мыши. То же можно выполнить на вкладке ГЛАВНАЯ/ФОРМАТ и выбрать АВТОПОДБОР ШИРИНЫ СТОЛБЦА . Самостоятельно отработать изменение ширины/высоты столбца/строки рабочей книги. На рис. 3 для того, чтобы текст в ячейках A3:D3 переносился по словам и был расположен в центре ячейки, выполнены установки в окне ФОРМАТ ЯЧЕЕК : в контекстном меню выделенного диапазона ячеек выбрать окно ФОРМАТ ЯЧЕЕК/ВЫРАВНИВАНИЕ и выполнить установки, представленные на рис. 3.

На рис. 4 представлена процедура установки рамки таблицы. Для этого выделить всю таблицу. Затем на панели ГЛАВНАЯ на значке ГРАНИЦЫ выбрать ВСЕ ГРАНИЦЫ . Другой способ задания рамки таблицы: в контекстном меню ФОРМАТ ЯЧЕЕК , вкладка ГРАНИЦЫ . Наименование таблицы ПРАЙС-ЛИСТ ввести в ячейку А1, затем выделить блок ячеек А1:D2 , и нажать кнопку ОБЪЕДИНИТЬ И ПОМЕСТИТЬ В ЦЕНТРЕ на панели ГЛАВНАЯ , как показано на рис. 5. Дальнейшее выравнивание выполняется на панели ГЛАВНАЯ , вкладка ВЫРАВНИВАНИЕ .

5 . Выполнить форматирование чисел в диапазоне С4:С12. Для этого нужно: выделить этот блок ячеек и в контекстном меню выбрать пункт ФОРМАТ ЯЧЕЕК (рис. 6). На вкладке ЧИСЛО выбрать пункт ВСЕ ФОРМАТЫ . Из списка предлагаемых форматов выбрать шаблон, представленный на рис. 6 стрелкой. В поле ТИП изменить стандартный шаблон и нажать ОК. Результат показан на рис. 7.

6. В ячейки D4:D12 ввести формулы для вычисления цены товара в рублях. Для этого ввести формулу =C4*$G$3 в ячейку D4 (знак $ набирать на клавиатуре или в момент нахождения курсора ввода на фрагменте этой формулы G3 нажать на клавиатуре F4). Затем формулу следует копировать из ячейки D4 на диапазон ячеек D5:D12. Копирование можно выполнять автозаполнением. Для этого нужно: выделить ячейку D4, установить курсор на маркер автозаполнения – прямоугольник в левом нижнем углу ячейки (при нахождении на нём маркер имеет вид креста), нажать левую кнопку мыши и, удерживая её, протянуть указатель мыши до ячейки D12 (рис. 7), отпустить кнопку мыши. Результат представлен на рис. 8.

7. В ячейку G5 ввести формулу =ТДАТА() для вычисления текущей даты. Формулу можно вводить непосредственным набором или с использованием панели ФОРМУЛЫ/ВСТАВИТЬ ФУНКЦИЮ или про помощи кнопки fx в строке ввода формул. Её следует выбирать из категории ДАТА/ВРЕМЯ .

8. На листе ЗАТРАТЫ набрать таблицу, представленную на рис. 9. В ячейках столбца С - формулы, содержащие ссылку на ячейки листа ПРАЙС . В ячейке С8 формула =СУММ(С3:С7). Она может быть непосредственно набрана в эту ячейку или введена автосуммированием: двойной щелчок левой кнопкой мыши на кнопке Автосумма (∑) на панели ГЛАВНАЯ.

9. Для автоматического поиска информации на листе ПРАЙС можно использовать функции из категории ССЫЛКИ И МАССИВЫ . Пример такой функции на рис. 10. Образец для поиска функция ПРОСМОТР берёт из ячейки А5. Этот образец она ищет на диапазоне ячеек В4:В12 листа ПРАЙС . Результат поиска – значение цены для товара заданного артикула функция ищет в диапазоне D4:D12 листа ПРАЙС . Формулу можно вводить непосредственным набором или с использованием панели ФОРМУЛЫ/ВСТАВИТЬ ФУНКЦИЮ или при помощи кнопки fх в строке ввода формул. Необходимое условие применения функции ПРОСМОТР состоит в том, что в диапазоне поиска все записи должны быть отсортированы по возрастанию значений поля АРТИКУЛ .

10. На листе ПРЕДЛОЖЕНИ Е ввести данные, представленные на рис. 11

Задание 2 : Формула массива

Вычисление обратной матрицы с использованием возможностей MS EXCEL.

1. Вычисление обратной матрицы. В ячейки B16:D18 вводим исходную матрицу. Выделить диапазон ячеек G16:I18. Нажать на кнопку fx в строке ввода и выбрать категорию МАТЕМАТИЧЕСКИЕ , функция МОБР (рис. 12)

В поле МАССИВ вводим адреса ячеек исходной матрицы (рис. 13) и нажимаем сочетание клавиш CTRL+SHIFT+ENTER. Результат представлен на рис. 14

2. Умножение матрицы А на матрицу В . В диапазоне ячеек А7:В8 вводим матрицу А. В диапазоне ячеек D7:E8 вводим матрицу В. Выделить ячейки в диапазоне G7:H8. Нажать на кнопку fx в строке ввода и выбрать категорию МАТЕМАТИЧЕСКИЕ , функция МУМНОЖ (рис. 15)

Для ввода формулы нажимаем сочетание клавиш CTRL+SHIFT+ENTER. Результат вычисления представлен на рис. 18.

Самостоятельно вычислить определитель матрицы (рис. 18)

Задание 3: Логические функции

1. Создать таблицу следующего вида (рис. 19).

Рекомендации по выполнению задания: Ввести в ячейку Е7:Е8 проценты уценки товаров: соответственно для октябрьской и более ранней даты договора – 15 %; для декабрьской и ноябрьской даты договора – 0%. В ячейки F7:F8 ввести соответствующие формулы. Например, F7: =D7-D7*E7. На экране должно появиться следующее (рис. 20).

3. Рассчитать цену товара после уценки в зависимости от даты составления АКТА УЦЕНКИ , причём если между датой уценки и датой договора меньше 62 дней, то процент уценки будет 0%, в остальных случаях – 15%.

Рекомендации по выполнению задания: Ввести дату уценки 29.12.2008 года. Далее удалить проценты уценки из таблицы и вставить соответствующие формулы для процента уценки в зависимости от даты договора. Для выполнения задания необходимо использовать логическую функцию ЕСЛИ . Формат этой функции можно посмотреть с помощью МАСТЕРА ФУНКЦИЙ . Нажать на кнопку fx в строке ввода и выбрать категорию ЛОГИЧЕСКИЕ , функция ЕСЛИ (рис. 21). Записать в ячейку Е5 столбца ПРОЦЕНТ УЦЕНКИ формулу =ЕСЛИ($E$2-A5<62;0%;15%) (рис. 22).

Затем нужно скопировать формулу на диапазон ячеек Е6:Е9 автозаполнением ячеек. Результат представлен на рис. 23.

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

4. Скопировать эту формулу в диапазон E5:E12 , а формулу из F5 в диапазон F5:F12.

Анализ показывает, что формулы в столбцах E и F корректно работают для заполненных строк Акта уценки (5,6,7,8,9 строки), а для строк, начиная с десятой, появляется лишняя информация, т.е. формула не проверяет, введены ли все данные для расчета процента и цены после уценки.

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

ЕСЛИ(ИЛИ($C$2=0;A5=0;D5=0);’ ‘;ЕСЛИ($C$2-A5<31;0%;15%))

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

С помощью мастера функций изучите формат логического оператора ИЛИ .

6. Скопировать новую формулу в диапазон E5:E12.

7. Изменить формулу в ячейке F5 на следующую:

ЕСЛИ(E5=’ ‘;’ ‘;D5-D5*E5).

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

8. Скопировать новую формулу в диапазон F5:F12 и добавить новые записи.

9. Самостоятельно рассчитать цену товара после уценки в зависимости от даты составления Акта уценки, причем, если между датой уценки и датой договора меньше 31 дня, то процент уценки будет 0%, если между датой уценки и датой договора меньше 61 дня, то процент уценки будет 15%, в остальных случаях - 30%.

Проверить правильность работы электронного Акта уценки и сохранить в личной папке.

Задание 3 : Консолидация данных.

1. Добавить в рабочую книгу новые листы, так, чтобы общее количество листов было 8. Назвать листы рабочей книги следующим образом: Лист1 – МЕНЮ, Лист2 – СЕВЕР, Лист3 – ЮГ, Лист4 – ЗАПАД, Лист5 – ВОСТОК, Лист6 – НЕСВЯЗАННАЯ КОНСОЛИДАЦИЯ, Лист7 – СВОДНЫЙ ОТЧЁТ (Рис. 34).

2. Сгруппировать листы СЕВЕР – СВОДНЫЙ ОТЧЁТ рабочей книги. Для этого, удерживая клавишу CTRL , щёлкать мышкой на ярлыках листов. Если листы сгруппированы, то они выделяются белым цветом. При внесении информации на один из листов группы, информация автоматически заносится на все листы группы.

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

4. Разгруппировать листы. Для этого, нажав клавишу CTRL , щёлкать левой клавишей мыши на ярлыках всех листов. Они должны стать серого цвета. Внести на листы Север, Юг, Запад, Восток переменную информацию: количество товаров, стоимости товаров и в заголовке внести названия регионов. Пример заполнения одного листа представлен на рис. 36. Для других листов (Юг, Запад, Восток ) внести новые значения в поля Количество и Стоимость.

5. Перейти на лист Сводный отчёт . Выделить диапазон ячеек В6:С15. Вызвать диалоговое окно Консолидация: Вкладка ДАННЫЕ/РАБОТА С ДАННЫМИ/КОНСОЛИДАЦИЯ (Рис. 37). В поле Функция выбрать Сумма . Щёлкнуть мышкой в поле Ссылка и ввести ссылку на консолидируемый диапазон ячеек: перейти на лист Север , выделить диапазон ячеек Стоимость и Количество без заголовка и итоговой строки (Рис. 38), нажать кнопку Добавить в окне Консолидация . Аналогично ввести данные для листов Юг, Запад. Восток . Включить флаг . Результат представлен на рис. 39.

6. Для выполнения консолидации данных нажать клавишу ОК . Результат представлен на Рис. 39. Слева создана структура: клавиши с изображением знака плюс. При нажатии на такую клавишу структура разворачивается: выдаются строки, показывающие данные с листов Север, Юг, Запад, Восток , на основе которых получены результаты вычисления суммы.

7. Самостоятельно выполните несвязанную консолидацию данных на листе Несвязанная консолидация . Флаг Создавать связи с исходными данными отключите

Задание 5 : Макросы

1. На листе МЕНЮ рабочей книги КОНСОЛИДАЦИЯ разместить объект Word Art: надпись ГЛАВНОЕ МЕНЮ . Для этого надо открыть панель ВСТАВКА /ТЕКСТ вкладка Word Art, выбрать нужный стиль написания и ввести текст надписи: главное меню. Разместить друг под другом несколько прямоугольников с помощью панели ВСТАВКА , вкладка ФИГУРЫ , как показано на рис. 40.

2. С помощью кнопки ИЗМЕНИТЬ ТЕКСТ в контекстном меню прямоугольников, внутри фигур выполнить надписи: названия листов рабочей книги КОНСОЛИДАЦИЯ . Установить выравнивание надписи ПО ЦЕНТРУ (рис. 41). Выполненные объекты предназначены для создания кнопочного меню. Кнопочное меню будет использовано для быстрого перехода к объектам рабочей книги.

3 Для каждой кнопки меню нужно записать макросы: программы на языке VISUAL BASIС , позволяющие автоматизировать выполнение определённых операций. В нашем случае макросы будут автоматизировать операции перехода на выбранные при помощи кнопок листы рабочей книги. Для записи макроса необходимо на панели ВИД выбрать вкладку МАКРОСЫ/ЗАПИСЬМАКРОСА . Затем ввести имя макроса, например, для макроса, прикрепляемого на кнопку СЕВЕР можно ввести имя Север (рис. 42) и закрыть диалоговое окно Запись макроса.

4. После этого перейти на лист СЕВЕР и выбрать в панели ВИД , вкладку МАКРОСЫ команду ОСТАНОВИТЬ ЗАПИСЬ . Аналогично написать макросы для кнопок ЮГ, ЗАПАД, ВОСТОК и ОТЧЁТЫ . Прикрепить макрос СЕВЕР к одноимённой кнопке на листе МЕНЮ . Для этого в контекстном меню кнопки СЕВЕР выбрать пункт НАЗНАЧИТЬ МАКРОС и в появившемся диалоговом окне выбрать из списка макросов соответствующий: СЕВЕР (рис. 43) и нажать кнопку ОК . Аналогично прикрепить все макросы к кнопкам меню.

5. На листах СЕВЕР, ЮГ, ЗАПАД, ВОСТОК и ОТЧЁТ создать кнопки или объекты (например объекты Word Art) для возврата на лист МЕНЮ . Затем написать макрос с именем ВОЗВРАТ , осуществляющий переход на лист меню. Прикрепить этот макрос к кнопкам (или другим объектам, выполняющим их функции), как показано на рис. 44. Выполнить аналогичные действия для всех листов рабочей книги КОНСОЛИДАЦИЯ , кроме МЕНЮ .

7. На листе Меню установить параметры отображения (Кнопка OFFICE, Параметры EXCEL ): не выводить Сетку, Заголовки строк и столбцов, линейки прокрутки. Результат представлен на рис. 45. После привязки всех макросов к кнопкам меню возможно его использование для перехода на листы рабочей книги Консолидация и для возврата обратно на лист Меню. При наведении указателя мышки на кнопку, он принимает вид руки.

Задание 6: Базы данных

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

2. Переименовать лист 2 рабочей книги EXCEL, назвав его Сортировка. Скопировать всю информацию с листа Список на лист Сортировка . Выполнить сортировку данных таблицы Автосалон . Для этого выделить диапазон ячеек A2:F9, и на панели ДАННЫЕ выбрать вкладку СОРТИРОВКА и заполнить диалоговое окно Сортировка как показано на рис. 47.

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

5. Переименовать лист рабочей книги EXCEL, назвав его Итоги. Скопировать всю информацию с листа Сортировка на лист Итоги . Удалить строку Итого таблицы Автосалон , установить курсор в таблице Автосалон и на вкладке ДАННЫЕ/СТРУКТУРА и выбрать пункт ПРОМЕЖУТОЧНЫЕ ИТОГИ (Рисунок 49). Появится таблица ИТОГИ . В диалоговом окне Промежуточные итоги заполнить поля как показано на рис. 50.

Результат выполнения этой операции представлен на рис. 51.

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

Удерживая нажатой клавишу CTRL, щелкать левой кнопкой мыши на заголовках колонок B, C, D и E для их выделения. В контекстном меню выделенных столбцов выбрать пункт Скрыть – рис. 53.

Результат представлен на рис. 54.

По этой таблице построить круговую диаграмму. Для этого выделить ячейки A6:F12, выбрать вкладку ВСТАВКА и выбрать КРУГОВУЮ ДИАГРАММУ . Результат показан на рис. 55

6. Переименовать лист рабочей книги EXCEL на Сводная таблица1 . Скопировать на него всю информацию с листа Список . Установить курсор в таблицу Автосалон и выбрать на вкладке ВСТАВКА кнопку СВОДНАЯ ТАБЛИЦА (рис. 56).

7. В диалоговом окне Мастера согласиться с заданным по умолчанию диапазоном $A$2:$F$10 или, если задан другой диапазон, исправить его на нужный. Результат задания диапазона исходных данных представлен на рис. 56. В окне установить флаг Существующий лист . Нажать кнопку ОК. Поле ПРОДАВЕЦ перетащить мышкой в поле Фильтр отчёта .Поле Марка перетащить мышкой в поле НАЗВАНИЕ СТРОК , поле Дата перетащить в поле НАЗВАНИЕ СТОЛБЦОВ , а поле Стоимость с НДСв у.е . перетащить в поле ∑ значения . После переноса поля Стоимость с НДСв у.е . в поле ∑ значения оно принимает название Сумма по полю стоимость , так оно становится вычисляемым полем (рис. 57).

После заполнения закрыть Список полей сводной таблицы . Результат представлен на рис.58.

На рис. 60 представлена сводная таблица подготовленная для группировки строк.

В ней выделены все строки, которые мы будем объединять в группу 1. После выполнения команды группировать таблица будет представлена как показано на рис.61. Здесь Группа 1 была переименована в Филиал 1. Затем были выделены все строки, относящиеся к группе 2, выполнена операция группировки для этих строк и аналогично переименована группа 2.

По этим группам также могут быть определены итоги. Для подведения итогов нужно выделить группу и выбрать в контекстном меню пункт Параметры поля (рис. 62).

Результат представлен на рис.63.

Задание 7 : Вычисление таблицы подстановок.

1. Ввести данные, представленные на рис 64. Для ввода функции ПЛТ , возвращающей сумму периодического платежа, нажать кнопку fx в строке ввода данных и выбрать категорию ФИНАНСОВЫЕ . Ввести аргументы функции. Результат представлен на рис 65.

2. В ячейки D11:D17 ввести тестируемые значения годовой процентной ставки, представленные на рис. 58. В ячейке E10 укажите адрес формулы, для которой требуется получить список результатов =E8. Результат: для формулы из ячейки E8 будет вычислен для каждого значения процентной ставки в таблице.

3. Выделите ячейки D10:E17. Выбрать панель ДАННЫЕ вкладку РАБОТА С ДАННЫМИ/АНАЛИЗ «ЧТО-ЕСЛИ»/ТАБЛИЦА ДАННЫХ так, как это показано на рис. 66: в поле ПОДСТАВЛЯТЬ ЗНАЧЕНИЕ ПО СТРОКАМ укажите адрес ячейки $Е$4, и нажмите ОК. Е4 – это ячейка, в которую мы последовательно вводили бы значения процентной ставки, если бы проводили исследования выплаты вручную. EXCEL автоматически подставит вычисленные значения выплат. Сверить результат с рис. 67.

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

Задание 8: Финансовые функции EXCEL.

1. Набрать таблицу, представленную на рис. 69, содержащую сведения об износе имущества предприятия.

2. В графах СУММА АМОРТИЗАЦИИ будут вводиться формулы для вычисления амортизационных отчислений. В EXCEL возможны три варианта расчёта амортизационных отчислений, поэтому на рис. 69 представлены три столбца для формул различного вида. Для ввода формул в столбец Е требуется установить курсор в ячейку Е4 и вызвать МАСТЕР ФУНКЦИЙ , нажав кнопку в строке ввода формул. Далее нужно из категории ФИНАНСОВЫЕ выбрать функцию АСЧ и задать её параметры в виде, представленном на рис. 70. Затем нажать ОК и скопировать формулу из ячейки Е4 на диапазон ячеек Е5:Е11 с использованием автозаполнения. Результат представлен на рис. 71.

3. В ячейку F4 ввести формулу с использованием ещё одной функции для вычисления амортизационных отчислений – ФУО . Функция находится в категории ФИНАНСОВЫЕ ФУО представлен на рис. 72. После ввода формулы её нужно скопировать на диапазон ячеек F5:F11 автозаполнением. Результат представлен на рис. 73.

4. В ячейку G4 ввести формулу с использованием финансовой функции для определения амортизационных отчислений – ДДОБ . Пример заполнения окна параметров функции ДДОБ представлен на рис. 74. Затем нужно скопировать формулу на диапазон ячеек G5:G11 автозаполнением ячеек. Результат представлен на рис. 75.

5. По трём видам начисления амортизации имущества построить линейную диаграмму с помощью МАСТЕРА ДИАГРАММ . Для этого нужно на панели ВСТАВКА выбрать вкладку ДИАГРАММЫ . Результат представлен на рисунке 76.

Название диаграммы и подписи осей задать на вкладке МАКЕТ при выделенной диаграмме.

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

Цель работы:

Изучить возможности редактора электронных таблиц Excel 2007.

Получить навыки работы с таблицами, формулами и функциями.

Задание:

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

Изучить порядок выполнения работы.

Методические указания

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

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

Кроме простейших арифметических формул в ячейках можно использовать математические функции и даже микропрограммы, написанные на языке VBA (Visual Basic for Applications – Visual Basic для приложений). Этот уровень использования Excel характерен для научных кругов. Excel является идеальным средством для проведения статистических расчетов и для обработки результатов экспериментов, для подготовки графиков и диаграмм.

ЭЛЕМЕНТЫ ОКНА ЕХСЕL

Чтобы запустить Ехсе l , следует зайти в меню Пуск , в подменю Все программы, открыть программную группу Microsoft Office , а затем выбрать пункт Microsoft Office Excel 2007 .

После запуска редактор Ехсеl автоматически откроет пустую книгу с названием Книга1 , которое будет отображено в Строке заголовка, расположенной в верхней части окна.

В отличие от предыдущих версий Ехсе l 2007 имеет несколько измененный интерфейс (рис. 1). Как и в редакторе Word , здесь имеется:

кнопка Office - служит для вывода списка возможных действий с документом (открытие, сохранение, печать и т.п.), а также для настройки параметров Ехсеl .

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

Строка состояния

Рис.1 Интерфейс Excel

Остальные элементы интерфейса Ехсеl остались прежними. Кратко рассмотрим их назначение.

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

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

Поле имени - это поле, расположенное слева на строке формул, в нем выводится имя активной ячейки (например, А1) или выделенного объекта (например, Диаграмма 1). В этом поле также можно присвоить имя ячейке или диапазону ячеек/

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

Строка состояния расположена в нижней части окна Ехсеl . В левой ее части отображается название выполняемой операции (открытие или сохранение файла, копирование ячеек или запись макроса и т.д.). Также здесь может выводиться подсказка, например, при нажатии на границе выделенного блока ячеек отображается подсказка, как перетащить данный блок; при нажатии на маркере заполнения (выводится подсказка, как заполнить ячейки рядом данных, и т.п.) Правая часть строки состояния содержит ярлыки переключения режимов просмотра документа, кнопку Масштаб, которая открывает одноименное диалоговое окно для выбора масштаба отображения документа, и панель масштабирования, на которой с помощью бегунка можно вручную уменьшать и увеличивать масштаб. Можно также воспользоваться кнопками Уменьшить или Увеличить , при нажатии на которые масштаб уменьшается или увеличивается с шагом 10%.

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

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

Работа с листами

Каждая рабочая книга по умолчанию содержит три листа со стандартными названиями; Лист1 , Лист2 , Лист3. Выбор того или иного листа осуществляется с помощью ярлычков листов в левом нижнем углу рабочей области. По умолчанию для текущего листа ярлычок отображается более светлым фоном, а для всех остальных - темным. Чтобы выбрать лист, следует щелкнуть по его ярлычку.

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

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

Примечание. Количество листов, которое имеет новая книга, по умолчанию выставляется при помощи опции Число листов, расположенной на странице Основные окна Параметры Ехс el , которое вызывается одноименной командой из меню кнопкой Office .

Рис. 2 Элементы управления ярлычками

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

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

СОЗДАНИЕ ТАБЛИЦЫ

Ввод данных

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

Имя ячейки (адрес ячейки) в Ехсеl формируется по аналогии с именованием клеток на шахматной доске: по имени столбца и строки, на которых расположена ячейка. Так, ячейка С3 находится на пересечении столбца С и 3-й строки.

Примечание . Ссылки могут иметь так называемый стиль R 1С1, где R 1 - строка 1, а С1- столбец 1. Переключение между стилями осуществляется при помощи опции Стиль ссылок R 1С1, расположенной на странице Формулы окна Параметры Ехсе1 , которое вызывается одноименной командой из меню кнопки Office .

а

б

в

Рис. 3. Ввод текста:

а – просмотр содержимого ячейки В2;

б – переход на пустую ячейку С2; в – ввод текста в ячейку С2

Ссылаться можно как на отдельные ячейки, так и на диапазоны прямоугольные блоки) ячеек. Когда в диапазон входят смежные ячейки, например А1, А2 и АЗ или А1, В1 и С1, такой диапазон обозначается в формуле при помощи ссылок на первую и последнюю его ячейки, между которыми ставится знакдвоеточия “:” (А1:А3 и А1:С1 соответственно). Если же ячейки диапазона являются несмежными, т. е. они были выделены с помощью клавиши Ctrl , то ссылки на вес ячейки диапазона перечисляются в формуле через точку с запятой “;” (А1;А3;С1).

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

При открытии нового документа автоматически активной устанавливается ячейка А1, она обрамляется черной рамкой. И если сразу же начать вводить текст, он отобразится в этой ячейке. Чтобы ввести текст в другую ячейку, например А2, необходимо ее активизировать, т.е. щелкнуть мышью по этой ячейке либо установить в нее курсор, произведя двойной щелчок (ссылка на активную ячейку отображается в поле имени). Далее следует ввести данные и завершить ввод нажатием клавиши Tab , в результате чего курсор переходит на соседнюю ячейку справа - В2.

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

Примечание . Направление перехода курсора при вводе данных в ячейку указывается при помощи опции Переход к другой ячейке после нажатия клавиши ВВОД и выпадающего списка Направление, расположенных на странице Дополнительно окна Параметры Ехсе1, которое вызывается одноименной командой из меню кнопки Office .

Если информация уже введена в ячейку и требуется лишь добавить или скорректировать ранее введенные данные (например, если в ячейке В2 следует ввести не Наименование, а Наименование товара), необходимо выполнить такие действия:

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

Установить курсор в то место, где нужно добавить текст, т.е. вконец слова Наименование, нажать клавишу Пробел и ввести слово товара.

Для подтверждения внесенных изменений нажать клавишу Enter или Tab либо кнопку Ввод в строке формул.

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

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

ВЫДЕЛЕНИЕ ЭЛЕМЕНТОВ ТАБЛИЦЫ

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

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

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

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

Теперь, когда нужные столбцы выделены, можно зайти на вкладку ленты Главная и в группе Ячейки выполнить команду Столбец  Автоподбор ширины столбца. В результате Ехсеl автоматически подберет необходимую ширину для каждого выделенного столбца таблицы.

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

Выделение смежных ячеек

а

б

Рис. 4. Выделение смежных ячеек:

а – в двух столбцах; б – в одном столбце

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

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

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

Выделение несмежных ячеек

Для того чтобы выделить несмежные ячейки, вначале необходимо выделить первый диапазон (в данном случае А3:А7, здесь двоеточие - оператор диапазона), затем нажать клавишу Ctrl и, удерживая ее, выделить остальные ячейки (т.е. диапазон D 3:Е7). После того как будут выделены все нужные ячейки, к ним можно применить операцию выравнивания.

КОПИРОВАНИЕ И ПЕРЕМЕЩЕНИЕ ЯЧЕЕК

В редакторе Ехсеl копирование и перемещение данных осуществляется стандартным для Windows способом, который состоит из следующих этапов:

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

Скопировать (переместить) выделенный блок в буфер обмена (например, посредством кнопки Копировать (Вырезать ), Буфер обмена на вкладке Главная.

Установить курсор в то место документа, куда будет вставлена переносимая информация.

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

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

Маркер заполнения

а

б

Рис. 5. Копирование содержимого ячеек:

а – выделение исходного текста; б – результат копирования

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

Выделить ячейку или блок ячеек.

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

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

Отпустить кнопку мыши, а затем клавишу Ctrl .

При этом все имеющиеся в области вставки данные будут заменены новыми.

Использование специальной вставки

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

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

Скопировать значения диапазона D 2:D 5 в буфер обмена.

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

В открывшемся одноименном диалоговом окне (рис. 7) в поле Операция выбрать пункт сложить.

Нажать кнопку ОК.

Рис. 6 Добавление данных

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

Как видно на рис. 7, окно Специальная вставка позволяет копировать различные сложные элементы:

группа Вставить определяет объект копирования;

группа Операция при необходимости назначает математическую операцию, которую можно применить к копируемым данным;

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

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

Например, чтобы транспонировать строку, в которой перечислены месяцы отчетного периода (диапазон В1:Е1), в столбец (диапазон А2:А5), следует выполнить такие действия:

Рис. 7. Диалоговое окно Специальная вставка

Рис. 8 Результат сложения

Скопировать значения исходного диапазона В1:Е1 в буфер обмена.

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

В открывшемся одноименном диалоговом окне включить опцию транспонировать и нажать ОК.

В результате скопированная строка отобразится в столбце, как показано на рис. 9

Рис. 9. Результат транспонирования

Ввод данных в несколько ячеек одновременно

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

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

Ввести необходимую информацию (рис. 10а).

Нажать сочетание клавиш Ctrl + Enter .

В результате все выделенные ячейки будут содержать одинаковое значение (рис. 10б).

Рис. 10 Ввод данных в несколько ячеек одновременно:

а – ввод необходимой информации;

б – одновременное копирование во все выделенные ячейки

Заполнение ячеек копированием

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

Ввести значение в некоторую ячейку.

Нажать и удерживать клавишу Ctrl .

Захватить маркер заполнения левой кнопкой мыши и протащить по заполняемым ячейкам.

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

ДОБАВЛЕНИЕ СТРОК И СТОЛБЦОВ

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

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

Вызвать контекстное меню нажатием правой кнопки мыши.

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

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

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

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

РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ

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

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

Операндами могут быть:

постоянные значения;

имена;

функции.

Существуют четыре вида операторов:

арифметические;

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

текстовый оператор «&», который используется для обозначения операции объединения нескольких последовательностей символов в одну;

адресные операторы.

Операторы всех перечисленных разновидностей приведены ниже (табл. 1-3).

Таблица 1 Арифметические операторы

Арифметические операторы

Операторы сравнения

Оператор

Значение

Оператор

Значение

Сложение

Равно

Вычитание

Больше

Умножение

Меньше

Деление

Больше или равно

Процент

Меньше или равно

Возведение в степень

Не равно

Таблица 2 Операторы сравнения

Оператор

Значение

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

Оператор объединения, который ссылается на объединения ячеек диапазонов

(пробел)

Оператор пересечения, который ссылается на общие ячейки диапазонов

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

Таблица 3. Приоритет операторов

Оператор

Описание

Оператор

Описание

Получение диапазона ячеек

Возведение в степень

(пробел)

Пересечение диапазонов

* и /

Умножение и деление

Объединение диапазонов

И -

Сложение и вычитание

Смена знака выражения

Объединение текстовых строк

Вычисление процента

= < > <= <= <>

Сравнение данных

Рис. 11 Элементы формулы

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

Например, чтобы из числа, находящегося в ячейке А2, вычесть число 3 и умножить эту разницу на сумму значений ячеек В3, В4 и В5, следует совершить такие действия:

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

Ввести знак равенства (=) и адреса ячеек с арифметическими операторами (рис. 11).

Нажать клавишу Enter .

ИСПОЛЬЗОВАНИЕ ССЫЛОК

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

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

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

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

Например, если в ячейке А3 была записана формула =А1*А2, то при копировании содержимого АЗ в ячейки ВЗ и СЗ новые формулы с обновленными ссылками примут следующий вид: = В1*В2, =С1*С2 (рис. 12а).

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

Рис. 12. Использование ссылок

а – относительных; б – абсолютных

Например, если необходимо зафиксировать в формуле =А1*В1 значение ячейки А1 (рис. 12 б), которое не должно изменяться в случае копирования данной формулы, то абсолютная ссылка на эту ячейку будет иметь следующий вид: $А$1. Таким образом, при копировании формулы из ячейки В2 в ячейку С2 формула примет вид =$А$1*С1.

Выделить ячейку с формулой.

Нажатием клавиши F 4 выбрать требуемый тип ссылки.

Последовательность изменения типов ссылок для ячейки А1 при использовании клавиши F 4 такая:

А$ 1 - изменяемый столбец и неизменяемая строка;

$А1 - неизменяемый столбец и изменяемая строка;

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

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

Второй способ включает в себя следующие действия:

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

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

Нажать клавишу Enter .

ПОНЯТИЕ ФУНКЦИИ В ЕХСЕL

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

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

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

Математические, финансовые и другие функции

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

Рис. 13. Панель Библиотека функций на вкладке Формулы

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

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

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

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

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

Правила синтаксиса при записи функций

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

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

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

Рис. 14. Запись функций

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

Например, в формуле, отображенной на рис. 15, осуществляется суммирование ячеек В2, ВЗ, В4, В5 и Е6.

Рис.15. Суммирование ячеек

Рассмотрим работу функции ОКРУГЛ(арг1;арг2), которая округляет число до заданного количества знаков после запятой и имеет два аргумента:

арг1 - адрес ячейки с числом (или само число), которое нужно округлить;

арг2 - количество цифр после запятой у числа после округления.

Чтобы округлить число 2,71828, находящееся в ячейке А1, с точностью до одного, двух или трех знаков после запятой и записать результаты вычислений соответственно в ячейки В1, С1 и D 1, необходимо действовать следующим образом:

Ввести число 2,71828 в ячейку А1.

Ввести в ячейки В1, С1 и D 1 такие формулы (рис. 16): =ОКРУГЛ(А1;1)

ОКРУГЛ(А1;2)

ОКРУГЛ(А1;3)

Рис. 16. Результат использования функции округления

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

СУММ(ОКРУГЛ(А1;2);ОКРУГЛ(А2;2))

Здесь функция ОКРУГЛ является вложенной. Ехсеl позволяет использовать в формулах не более семи уровней вложенности функций.

В Ехсеl существуют функции, которые не имеют аргументов. Примерами таких функций являются ПИ (возвращает значение числа  , округленное до 15 знаков) или СЕГОДНЯ (возвращает текущую дату). При использовании подобных функций следует в строке формул сразу после названия функции ставить круглые скобки. Другими словами, чтобы получить в ячейках значение числа  или текущую дату, нужно ввести формулы такого вида:

ПИ()

СЕГОДНЯ()

ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Вариант 1

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

Фамилия И.

Алгебра

Геометрия

Общ. пок-ль

Кол-во оценок

Баллы

Средний балл

Кол-во оценок

Баллы

Средний балл

Иванов М.

Петров Д.

Сидоров В.

3,571428571

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

3. Результат столбцов Средний балл получить при помощи формулы.

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

6. Переименовать лист1 в лист Математика.

7. На втором листе создать аналогичную таблицу с блоком гуманитарных дисциплин (н-р, Литература, История ).

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

Вариант 2

1. На первом листе открытой книги набрать таблицу, содержащую данные о ДТП с участием детей за январь месяц 2008/2009 гг.

Наименование ОВД

Всего

Погибло

Ранено

ДТП

2008

2009

2008

2009

2008

2009

ГУВД по г.Тамбову

3

3

Жердевский РОВД

Кирсановский ГРОВД

1

2

Котовский ГОВД

Мичуринское ГУВД

Мичуринский РОВД

1

2

Моршанский ГОВД

1

1

Моршанский РОВД

Рассказовский ГРОВД

1

1

Бондарский РОВД

2. На втором листе создать аналогичную таблицу за февраль месяц 2008/2009 гг.

Наименование ОВД

Всего

Погибло

Ранено

ДТП

2008

2009

2008

2009

2008

2009

ГУВД по г.Тамбову

4

4

Жердевский РОВД

Кирсановский ГРОВД

Котовский ГОВД

Мичуринское ГУВД

2

2

Мичуринский РОВД

1

1

Моршанский ГОВД

1

1

Моршанский РОВД

Рассказовский ГРОВД

Бондарский РОВД

Переименовать первый лист книги Excel лист1 в 01 , а лист2 –в 02 .

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

Обозначить лист3 как лист с названием +2 .

Вариант 3

1. На первом листе открытой книги набрать таблицу с заголовком Платежное извещение

Адрес: ул. Пролетарская, 11, кв. 067

Лицевой счет

2234567654

Период

янв.08

Всего к оплате

Добр. страх жилья:

23,35

Всего со страховкой:

Вид платежа (ед. изм.)

Тариф

Объем

Начислено по тарифу

Сод. и рем. Жил. (м2)

4,33

46,7

Отопление (м2)

23,68

46,7

Газ (плиты) (чел)

Водоснабжение (чел.)

84,27

Водоотведение (чел.)

58,16

Гор. Водоснабж. (чел.)

150,73

Вывоз ТБО (чел.)

20,13

Домофон

Всего к оплате:

Приборы

На день

Предыдущее

Кол-во (кВт.)

учета

выписки

Свет/1/

3200

3050

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

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

Значения столбца Начислено по тарифу получить с помощью формулы (необходимо перемножить значения в столбцах Тариф и Объем ).

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

Вариант 4

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

Модель

Цена

Продано, шт.

Доход, руб.

Panasonic TX-R32LM70

25 848,00р.

2

51 696,00р.

Panasonic TX-R32LX70

33 084,00р.

3

99 252,00р.

Panasonic TX-R32LX700

44 604,00р.

1

44 604,00р.

Итого:

195 552,00р.

3. Переименовать лист1 в Panasonic .

В отдельной от таблице ячейке оформить значение курса евро:

курс евро

36,20р.

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

На втором листе книги Excel аналогичным образом получить следующую таблицу продаж телевизоров:

Модель

Цена

Продано, шт.

Доход

Philips 20PF4121

10 980,00р.

4

43 920,00р.

Philips 20PF5120

16 812,00р.

5

84 060,00р.

Philips 20PF5123

11 376,00р.

1

11 376,00р.

Итого:

139 356,00р.

Вариант 5

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

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

Ввести количество детей в столбец число детей .

Фамилия

Сумма к

число

Сумма за год

Оклад

Налог

выдаче

выплат

1

Морыженков

15000

2

Соседов

14900

3

Семёнов

13780

4

Короленко

16200

5

Стенбок

17560

6

Мускатин

12870

7

Гераськин

18430

8

Кочеев

15555

В отдельной от таблице ячейке оформить значение Начисление на детей.

Начисление на детей

153р

6. На лист2 оформить следующую таблицу, осуществив связь с таблицей, расположенной на первом листе через столбец Фамилия .

Фамилия,

имя отчество

Сумма

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

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

Как называется документ в Excel и из чего он состоит?

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

Что такое ячейка?

Из чего состоит адрес ячейки?

Что такое активная ячейка?

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

С какого символа начинают ввод формулы в ячейку?

Что такое Мастер функций, как он работает?

Как скопировать или переместить ячейку?

Как отредактировать содержимое ячейки?

Что такое автозаполнение и как оно выполняется?

Как удалить (вставить) строку (столбец)?

Что такое автосуммирование?

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

Глушаков, С. В. Microsoft Office 2007. Лучший самоучитель / С.В. Глушаков, А.С. Сурядный. – изд. 3-е доп. и переработ. – М.: АСТ: АСТ МОСКВА: Владимир: ВКТ, 2008. -446. c . (Учебный курс).

Глушаков, С. В. Microsoft Excel 2007. Лучший самоучитель / С.В. Глушаков, А.С. Сурядный. – изд. 2-е доп. и переработ. – М.: АСТ: АСТ МОСКВА:, 2008. -416 c . - (Учебный курс).

С. Симонович, В. Мураховский. Популярный самоучитель работы на компьютере – М.: “Техбук”, 2006. – 576 с.

Выберите документ из архива для просмотра:

Лабораторная работа по Excel №1.doc

Библиотека
материалов

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

Упражнение 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделите ячейку D 5; F 2; А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 -го члена и суммы арифметической про­грессии

Рис. 1.3.

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

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

Сформатируйте строку заголовков таблицы. В ячейку A3 введите "d", в ячейку ВЗ - "n ", в СЗ - "a n ". в D 3 - "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 до D 1 и выполните команду Формат  Ячейки..., выберите закладку Выравнивание и устано­вите переключатели в положение "Центрировать по выделению" (Горизонтальное выравнивание) и "Переносить по словам" (рис. 1.5). Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.

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

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

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

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

Выбранный для просмотра документ Лабораторная работа по Excel №2.doc

Библиотека
материалов

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

Упражнение 1

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

Грузоотправитель и его адрес

Грузополучатель и его адрес

К Реестру № Дата получения «___»___________200__г.

СЧЕТ № 123 от 15.11.2000

Поставщик Торговый Дом Рога и Копыта

Адрес 243100, Клинцы, ул. Пушкина, 23

Р/счет № 45638078 в МММ-банке, МФО 985435

Дополнения:

Наименование

Ед.измерения

Руководитель предприятия Сидоркин А.Ю.

Главный бухгалтер Иванова А.Н.

Упражнение заключаете в создания и заполнении бланка то­варного счета.

Выполнение упражнения лучше всего разбить на три этапа:

1-и этап. Создание таблицы бланка счета.

2-й этап. Заполнение таблицы.

3-й этап. Оформление бланки.

1-й этап.

З
аключается в создании таблицы.

Основная задача уместить таблицу по ширине листа. Для этого:

предварительно установите поля, размер и ориентацию бу­маги (Файл  Параметры страницы… ),

выполнив команду Сервис  Параметры..., в группе пере­ключателей Параметры окна активизируйте переключатель Авто-разбиение на страницы (рис. 2.1).

Рис. 2.1.

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

Наименование

Ед.измерения

Создайте таблицу по предлагаемому образцу с таким же числом строк и столбцов.

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

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

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

Проще всего добиться этого следующим путем:

выделить всю таблицу и установить рамку - "Контур" жирной линией;

затем выделить все строки, кроме последней и установить рамку тонкой линией "Справа", "Слева", "Сверху", "Снизу";

после этого выделить отдельно самую правую ячейку ниж­ней строки и установить для нее рамку "Слева" тонкой линией;

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

Хотя можно действовать и наоборот. Сначала "разлиновать" всю таблицу, а затем снять лишние линии обрамления,

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

2-й этап

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

Заполните столбцы "Наименование", "Кол-во" и "Цена" по своему усмотрению.

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

В нашем случае это пустые ячейки столбцов "Цена" и "Сумма". Их нужно выделить и выполнить команду Формат  Ячейки..., выбрать вкладку Число и выбрать категорию Денеж­ный (рис. 2.2). Это даст вам разделение на тысячи, чтобы удоб­нее было ориентироваться в крупных суммах.

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

Отсортируйте записи по алфавиту.

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

Выполните команду Данные  Сортировка... (рис. 2.3), выбе­рите столбец, по которому нужно отсортировать данные (в на­шем случае это столбец В, так как именно он содержит перечень товаров, подлежащих сортировке), и установите переключатель в положение "По возрастанию".

3-й этап

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

Для этого выделите несколько первых строк таблицы и вы­полните команду Вставка  Строки. Вставится столько же строк, сколько вы выделили.

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

Рис. 2.3 .

Братите внимание, что текст "Дата получения "__"_______200_г." и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы "Сумма" (самый правый столбец нашей таблички), только при­менено выравнивание вправо.

Текст "СЧЕТ №" внесен в ячейку самого левого столбца, и применено выравнивание по центру выделения (предварительно выделены ячейки одной строки по всей ширине таблицы счета). Применена рамка для этих ячеек сверху и снизу.

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

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

Упражнение 2

ТАБЛИЦА КВАДРАТОВ

1024

1089

1156

1225

1296

1369

1444

1521

1600

1681

1764

1849

1936

2025

2116

2209

2304

2401

2500

2601

2704

2809

2916

3025

3136

3249

3364

3481

3600

3721

3844

3969

4096

4225

4356

4489

4624

4761

4900

5041

5184

5329

5476

5625

5776

5929

6084

6241

6400

6561

6724

6889

7056

7225

7396

7569

7744

7921

8100

8281

8464

8649

8836

9025

9216

9409

9604

9801

Рис. 2.4

В ячейку A3 введите число 1, в ячейку А4 - число 2, выде­лите обе ячейки и протащите маркер выделения вниз, чтобы за­полнить столбец числами от 1 до 9.

Аналогично заполните ячейки В2 - К2 числами от 0 до 9.

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

Для этого выделите столбцы от А до К и выполните ко­манду Формат  Столбец Ширина..., в поле ввода Ширина столб­ца введите значение, например, 5.

Разумеется, каждому понятно, что в ячейку ВЗ нужно по­местить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце А и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке ВЗ можно задать формулой =АЗ*10+В2 (число десятков, умноженное на десять плюс число единиц). Остается возвести это число в квадрат.

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

Для этого выделите ячейку, в которой должен разместиться результат вычислений (ВЗ), и выполните команду Вставка  функция...] (рис. 2.5.).

Рис. 2.5.

Следующем диалоговом окне введите число (основание сте­пени) - АЗ*10+В2 и показатель степени - 2. Так же, как и при наборе формулы непосредственно в ячейке электрон­ной таблицы, нет необходимости вводить адрес каждой ячейки, на которую ссылается формула, с клавиатуры. Работая с Масте­ром функций, достаточно указать мышью на соответствующую ячейку электронной таблицы, и ее адрес появится в поле ввода "Число" диалогового окна. Вам останется ввести только арифме­тические знаки (*, +) и число 10.

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

Остается только нажать кнопку Закончить.

В ячейке ВЗ появился результат вычислений.

Хотелось бы распространить эту формулу и на остальные ячейки таблицы. Выделите ячейку ВЗ и заполните, протянув маркер выделения вправо, соседние ячейки. Что произошло (рис. 2.6)?

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

Расширьте мышью столбец С. Число появилось на экране, но оно явно не соответствует квадрату числа 11 (рис. 2.7).

Рис. 2.6 Рис. 2.7

Почему? Дело в том, что когда мы распространили форму­лу вправо. Excel автоматически изменил с учетом нашего смеще­ния адреса ячеек, на которые ссылается формула, и в ячейке СЗ возводится в квадрат не число 11, а число, вычисленное по фор­муле = ВЗ*10+С2.

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

Для фиксирования любой позиции адреса ячейки перед ней ставят знак $.

Таким образом, верните ширину столбца С в исходное по­ложение и выполните следующие действия-

Выделите ячейку ВЗ и, установив текстовый курсор в Строку формул, исправьте имеющуюся формулу =СТЕПЕНЬ(АЗ*10+В2;2) на правильную =СТЕПЕНЬ($АЗ*10+В$2,2).

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

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

Упражнение 3

Введение понятия "имя ячейки ".

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

Подготовьте таблицу, состоящую из столбцов:

"Наименование товара", "Эквивалент $ US ", "Цена в р.". За­полните все столбцы, креме "Цена в р." Столбец "Наименование товара” заполните текстовыми данными (перечень товаров по вашему усмотрению), а столбец "Эквивалент $ US " числами (цены в долл.).

Понятно, что а столбце "Цена в р." должна разместиться формула: "Эквивалент $ US "*Kypc доллара".

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

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

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

Выделите ячейку, в которую будет вводиться курс доллара (выше таблицы), введите в нее значение курса доллара на сего­дняшний день и выполните команду Вставка  Имя  Присвоить... (рис. 2.8).

Примечание: Имя может иметь в длину до 255 символов и содержать буквы, цифры, подчерки (_), символы: обратная косая черта (\), точки и вопроси­тельные знаки. Однако первый символ должен быть буквой, подчерком (_) или символом обратная косая черта (\). Не допускаются имена, которые воспринимаются как числа или ссылки на ячейки.

Рис. 2.8.

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

В ячейку, расположенную левее ячейки "Курс_доллара", можно ввести текст "Курс доллара".

Теперь остается ввести формулу для подсчета цены в руб­лях.

Для этого выделите самую верхнюю пустую ячейку столбца "Цена в рублях" и введите формулу следующим образом: введите знак "=", затем щелкните мышью по ячейке, расположенной ле­вее (в которой размещена цена в долл.), после этого введите знак "*" и в раскрывающемся списке Поля имени выберите мышью имя ячейки "Курс доллара". Формула должна выглядеть приблизительно так: =В7*Курс_доллара.

Заполните формулу вниз, воспользовавшись услугами мар­кера заполнения.

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

Оформите заголовок таблицы: выровняйте по центру, при­мените полужирный стиль начертания шрифта, расширьте стро­ку и примените вертикальное выравнивание по центру, восполь­зовавшись командой Формат  Ячейки..., выберите вкладку Вы­равнивание и в группе выбора Вертикальное выберите По центру. В этом же диалоговом окне активизируйте переключатель Пере­носить по словам на случай, если какой-то заголовок не помес­тится в одну строчку.

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

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

Выбранный для просмотра документ Лабораторная работа по Excel №3.doc

Библиотека
материалов

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

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

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

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

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

Заполнение таблицы данными традиционным способом и с применением формы;

Подбор данных по определенному признаку.

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

Введите заголовки таблицы в соответствии с предложен­ным образцом. Учтите, что заголовок располагается в двух стро­ках таблицы: в верхней строке "Приход", "Расход", "Остаток", а строкой ниже остальные пункты заголовка.

Приход

Расход

Остаток

Отдел

Наименование товара

Единица измерения

Цена прихода

Кол-во прихода

Цена расхода

Кол-во расхода

Кол-во остатка

Сумма остатка

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

Так, для форматирования ячеек их достаточно выделить, щелкнуть правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения и выбрать команду Формат  Ячеек... , вы перейдете к тому же диалоговому окну Формат ячеек (рис. 3.1). Да и редактировать содержимое ячейки (исправлять, изменять данные) совсем не обязательно в Строке формул. Если дважды щелкнуть мышью по ячейке, в ней появится тек­стовый курсор, и можно произвести все необходимые исправления.

Заполнение таблицы

Определитесь, каким видом товаров вы собираетесь торго­вать и какие отделы будут в вашем магазине.

Вносите данные в таблицу не по отделам, а вперемешку (в порядке поступления товаров).

Заполните все ячейки, кроме тех, которые содержат формулы ("Остаток").

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

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

Приход

Расход

Остаток

Отдел

Наименование товара

Единица измерения

Цена прихода

Кол-во прихода

Цена расхода

Кол-во расхода

Кол-во остатка

Сумма остатка

Кондитерский

Зефир в шоколаде

упак.

20 р.

25р.

0 р.

Молочный

Сыр

кг.

65 р.

85 р.

170 р.

Мясной

Колбаса Московская

кг.

110 р.

120р.

600 р.

Мясной

Балык

кг.

120 р.

140 р.

700 р.

Вино-водочный

Водка «Абсолют»

бут. 2 л.

400 р.

450 р.

450 р.

0 р.

Вычисляемые поля (в которых размещены формулы) выводят­ся на экран без окон редактирования ("Кол-во Остатка" и "Сумма Остатка").

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

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

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

Перемещаться между окнами редактирования (в которые вно­сятся данные) удобно клавишей (Tab }.

Когда заполните всю запись, нажмите клавишу {Enter }, и вы автоматически перейдете к новой чистой карточке-записи

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

Заполните несколько новых записей и затем нажмите кнопку Закрыть.

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

Оперирование данными

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

Выделите таблицу без заголовка и выберите команду Данные-  Сортировка... (Рис. 3.3).

Выберите первый ключ сортировки: в раскрывающемся списке "Сортировать" выберите "Отдел" 5 и установите переклю­чатель в положение "По возрастанию" (все отделы в таблице расположатся по алфавиту).

Если же вы хотите, чтобы внутри отдела все товары размеща­лись по алфавиту, то выберите второй ключ сортировки: в рас­крывающемся списке "Затем по" выберите "Наименование товара", уста­новите переключатель в положение "По возрастанию". Теперь вы имеете полный список товаров по отделам.

Продолжим знакомство с возможностями баз данных Excel .

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

Выделите таблицу со второй строкой заголовка (как перед созданием формы данных).

Выберите команду меню Данные  Фильтр...  Автофильтр.

Снимите выделение с таблицы.

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

Раскройте список ячейки "Кол-во Остатка", выберите команду Настройка... и, в появившемся диалоговом окне установите соответствующие параметры (>0).

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

Фильтр можно усилить. Если дополнительно выбрать ка­кой-нибудь конкретный отдел, то можно получить список не­проданных товаров по отделу.

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

Но и это еще не все возможности баз данных Excel . Разу­меется ежедневно нет необходимости распечатывать все сведе­ния о непроданных товарах, нас интересует только "Отдел", "Наименование" и "Кол-во Остатка".

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

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

Вместо команды контекстного меню можно воспользоваться командой горизонтального меню Формат  Столбец  Скрыть.

Чтобы не запутаться в своих распечатках вставьте дату, ко­торая автоматически будет изменяться в соответствии с установ­ленным на вашем компьютере временем Вставка  Функция..., имя функции - "Сегодня").

Теперь уже точно можно распечатать и иметь подшивку ежедневных сведений о наличии товара.

Как вернуть скрытые столбцы? Проще всего выделить таб­лицу Формат  Столбец  Показать.

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

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

В верхней части листа появилась запись "Лист I". Нужно ее уда­лить.

Страница...;

Колонтитулы ;

в поле выбора Верхние колонтитулы ус­тановите Нет

В нижней части листа появилась запись "СТР. I". Нужно ее уда­лить.

Находясь в режиме просмотра, выбери­те кнопку Страница...;

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

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

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

Находясь в режиме просмотра, выберите кнопку Страница..., в появившемся диалого­вом окне вкладку Лист и отключите пере­ключатель Печатать сетку .

Таблица не по­мещается по ширине на странице, хоте­лось бы умень­шить левое и правое поля.

1. Находясь в режиме просмотра, выбери­те кнопку Страница..., в появившемся диа­логовом окне вкладку Поля и установите же­лаемые поля.

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

Размер полей уменьшен, а таблица так и не помещается по ширине на странице. Хоте­лось бы изме­нить ориента­цию листа.

Находясь в режиме просмотра, выберите кнопку Страница..., в появившемся диалого­вом окне вкладку Страница и измените ори­ентацию листа на Альбомная. Здесь же можно задать размер бумаги.

Диалоговое окно <Параметры страницы> можно вызвать, на­ходясь в режиме таблицы (не выходя в режим просмотра), вы­полнив команду Файл  Параметры страницы....

Выбранный для просмотра документ Лабораторная работа по Excel №4.doc

Библиотека
материалов

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

Проверка уровня сформированности основных навыков работы с электронными таблицами. Знакомство с общими сведениями об управлении листами рабочей книги, удалении, переименовании лис­тов. формулы, имеющие ссылки на ячейки другого листа рабочей книги. Мастер диаграмм. Выделение ячеек таблицы, не являющихся соседними.

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

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

По умолчанию рабочая книга открывается с 16-ю рабочими листами, имена которых Лист1, ..., Лист16. Имена листов выве­дены на ярлычках в нижней части окна рабочей книги.

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

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

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

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

на первом разместим сведения о начислениях,

на втором - диаграмму, .

на третьем - ведомость на выдачу заработной платы,

а на четвертом - ведомость на выдачу компенсаций на детей.

Остальные листы будут только мешать, поэтому их лучше удалить.

Выделите листы с 5 по 16. Для этого щелкните мышью по ярлычку листа 5, затем, воспользовавшись кнопкой перей­дите к ярлычку листа 16 и, удерживая клавишу (Shift }, щелкните по нему мышью. Ярлычки листов с 5 по 16 выделятся цветом.

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

Теперь выглядывают ярлычки только четырех листов.

Активен (ярлычок выделен цветом) Лист 1. Именно на нем мы и начнем создавать таблицу.

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

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

запуск Excel ;

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

изменение ширины столбца (в зависимости от объема вво­димой информации);

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

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

заполнение ячеек столбца последовательностью чисел 1, 2, ...;

ввод формулы в верхнюю ячейку столбца;

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

заполнение таблицы текстовой и фиксированной числовой информацией (столбцы "ФИО", "Оклад", "Число детей");

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

Фамилия, имя отчество

Оклад

Налоги

Сумма к выдаче

Число детей

профс.

пенс.

подох.

1

2

3

4

5

6

7

8

Для форматирования формул вам наверняка понадобится до­полнительная информация. Примем профсоюзный и пенсионный налоги, составляющими по 1% от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столб­ца. Самое важное не забыть про абсолютные ссылки, так как и профсоюзный и пенсионный налоги нужно брать от оклада, т. е. ссылаться только на столбец "Оклад". Примерный вид формулы:

=$СЗ*1 % или =$СЗ*0,01 или =$СЗ*1/100. После ввода формулы в ячейку D 3 ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец.

Подоходный налог подсчитаем по формуле: 12% от Оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(СЗ-ЕЗ-86)*12% или =(СЗ-ЕЗ-86)*12/100 или =(СЗ-ЕЗ-86)*0,12. После ввода формулы в ячейку F 3, ее нужно распространить вниз.

Для подсчета Суммы к выдаче примените формулу, вычисляю­щую разность оклада и налогов. Примерный вид формулы: ==СЗ-D 3-E 3-F 3, размещенной в ячейке G 3 и распространенной вниз.

Заполняйте столбцы "Фамилия, имя, отчество", "Оклад", и "Число детей" после того, как введены все формулы. Результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполне­ния таблицы.

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

В окончательном виде таблица будет соответствовать образцу:

Фамилия, имя отчество

Оклад

Налоги

Сумма к выдаче

Число

профс.

подох.

Иванов А-Ф.

230000

2300

2300

18216

207184

Иванова Е.П.

450 000

4500

4500

44352

396 648

Китов а В. К

430 000

4300

4300

41 976

379 424

Котов И.П

378000

3780

3780

35 798

334642

Кругло ва АД

230000

2300

2300

18 216

207184

Леонов И И

560 000

560D

5600

57 420

491 380

Петров М.В.

348 000

3490

3490

32353

309667

Сидоров И.В.

450000

4500

4500

44352

396 648

Симонов К.Е

349 000

3490

3490

32 353

309667

Храмов А.К

430 000

4300

4300

41 Э76

379 424

Чудов АН,

673 000

6730

6730

70844

588 696

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

Поскольку мы собираемся в дальнейшем работать сразу с не­сколькими листами, имеет смысл переименовать их ярлычки в соответствии с содержимым. Переименуем активный в настоя­щий момент лист. Для этого выполните команду Формат  Лист  Переименовать... и в поле ввода Имя листа введите новое название листа, например, "Начисления".

Построение диаграммы на основе готовой таблицы и размещение ее на новом листе рабочей книги

Построим диаграмму, отражающую начисления каждого со­трудника. Понятно, что требуется выделить два столбца таблицы: "Фамилия, имя, отчество" и "Сумма к выдаче". Но эти столбцы не расположены рядом, и традиционным способом мы не смо­жем их выделить. Для Excel это не проблема.

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

Выделите заполненные данными ячейки таблицы, относя­щиеся к столбцам "Фамилия, имя, отчество" и "Сумма к выдаче".

Запустите Мастер диаграмм одним из способов: либо вы­брав кнопку Мастер диаграмм панели инструментов, либо команду меню Вставка  Диаграмма….

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

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

Перейдите к Листу 3. Сразу же переименуйте его в "Детские".

ФИО

Сумма

Подпись

Иванов А.Ф.

53 130

Иванова Е.П.

106260

Кругло ва А.Д.

53130

Леонов И.И.

159390

Петров М.В.

53 130

Сидоров И.В.

53 130

Чудов А.Н.

106260

Мы хотим подготовить ведомость, поэтому в ней будут три столбца: "ФИО", "Сумма" и "Подпись". Сформатируйте заго­ловки таблицы.

В графу "ФИО" нужно поместить список сотрудников, ко­торый мы имеем на листе "Начисления". Можно скопировать на одном листе и вставить на другой, но хотелось бы установить связь между листами (как это выполняется для диаграммы и листа начислений). Для этого на листе "Детские" поместим формулу, по которой данные будут вставляться из листа "Начисления".

Выделите ячейку А2 листа "Детские" и введите формулу: =Начисления!ВЗ, где имя листа определяется восклицательным знаком, а ВЗ - адрес ячейки, в которой размещена первая фами­лия сотрудника на листе "Начисления". Можно набрать форму­лу с клавиатуры, а можно после набора знака равенства перейти на лист "Начисления", выделить ячейку, содержащую первую фамилию и нажать (Enter ) (не возвращаясь к листу "Детские").

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

В графе "Сумма" аналогичным образом нужно разместить формулу =Начисления!НЗ*53130, где НЗ адрес первой ячейки на листе "Начисления", содержащей число детей. Заполните эту формулу вниз и примените денежный формат числа.

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

Для того, чтобы список состоял только из сотрудников, имеющих детей, установите фильтр по наличию детей (Даииые  фильтр  Автофильтр, в раскрывающемся списке "Сумма" выбе­рите "Настройка..." и установите критерий >0). Приблизитель­ный вид ведомости приведен ниже.

Создание шаблона. Работа с шаблонами документов. Совместное использование Word и Excel .

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

Создание бланка-шаблона

1. Оставьте в рабочей книге только один лист.

2
. Сформатируйте заголовок табеля учета рабочего времени за текущий месяц и подготовьте таблицу-бланк по образцу, приве­денному на рис. 1

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

Введите числа месяца с 1-го по 31-е. Для столбцов, содержа­щих даты, установите ширину столбца, равную 2.

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

3. Для сохранения подготовленного файла в качестве шаблона:

Введите имя сохраняемого файла в поле ввода Имя файла : Табель;

В списке типов файлов выберите Шаблон, расширение файла сменится на.xlt ;

Нажмите ОК;

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

Применение шаблона

Для создания нового файла с применением шаблона выпол­ните следующие действия:

В меню Файл выберите Создать.

В списке Общие диалогового окна <Создание документа> выделите шаблон, на основе которого хотите создать новую рабочую книгу (рис.2).

Выберите кнопку ОК.

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

1. Введите название текущего месяца в заголовок табеля.

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

3. Проставьте для каждого сотрудника:

Количество часов, отработанных за день, или

о, если он находится в отпуске, или

б, если в этот день сотрудник болеет, или

п, если прогуливает.

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

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

Помните, в Microsoft Word существовала возможность зафик­сировать заголовок таблицы, чтобы он автоматически появлялся на каждой новой странице?

Microsoft Excel позволяет зафиксировать заголовок на страни­це, чтобы при перемещении нужные вам столбцы (или строки) оставались на своем месте. Для того, чтобы зафиксировать стол­бец "Фамилия":

Выделите столбец справа от столбца "Фамилия" ("Профессия");

В меню Окно выберите команду Закрепить области;

Работая с большими таблицами, можете пользоваться сле­дующими возможностями фиксации заголовков.

Чтобы зафиксировать горизонтальные заголовки, выделите строку ниже заголовков.

Чтобы зафиксировать вертикальные заголовки, выделите столбец справа от заголовков.

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

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

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

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

4. Самостоятельно вставьте формулу суммирования соответст­вующих ячеек строки для подсчета отработанных часов. Запол­ните формулу вниз.

5. Для подсчета дней явок необходимо в каждой строке (для каждого сотрудника) подсчитать количество ячеек, содержащих числа (не суммируя эти числа). Для этого:

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

Выполните команду Вставка  Функция...;

В списке Имя функции окна диалога <Мастер функций> выберите функцию СЧЕТ (рис. 3). Если вы не знаете, к какой категории относится искомая функция, выберите категорию Полный алфавитный перечень и дальше ищите по алфавиту. Нажмите кнопку Ок .

В следующем окне нужно указать диапазон значений.

Нет необходимости вводить адреса ячеек с клавиатуры.

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

Нажмите кнопку Ок.

Заполните формулу вниз.

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

Заполните формулу вниз по столбцу.

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



Упражнение 2

Совместное использование Word и Excel .

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

Использование кнопок Excel

Панели инструментов Word содержат две кнопки для работы с Excel : одна на стандартной панели инструментов и другая - на панели инструментов Micro ­soft , как показано ниже. Чтобы вывести на экран панель инструментов Microsoft , выберите команду Вид  Панели инструментов и установите флажок Microsoft , после чего щелкните по ОК.







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

Функции этих двух кнопок кратко можно описать следующим образом:

Кнопка Добавить таблицу Excel на стандартной панели инструментов приводит к внедрению в документ Word электронной таблицы - то есть при этом вы сможете редактировать электронную таблицу Excel прямо в доку­менте Word .

Кнопка Microsoft Excel на панели инструментов Microsoft приводит к связыванию электронной таблицы или вставке базы данных из Excel ; щелчок по этой кнопке приводит к запуску Excel или (если он уже запущен) переключению в окно Excel .

Обмен информацией с Excel

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

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

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

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

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

Использование ячеек таблицы Excel

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

Вставка ячеек

Чтобы вставить в документ Word ячейки электронной таблицы Excel , поступайте следующим образом:

2. Либо откройте одну из существующих книг, либо введите нужное содержимое в новую таблицу.

3. Выделите ячейки, которые вы хотите скопировать в документ Word , и выберите команду Правка  Копировать .

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

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

Внедрение ячеек

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

1. Щелкните по кнопке Добавить таблицу Excel на стандартной панели инструментов и протаскиванием в появившейся сетке укажите число строк и столбцов, которые вы хотите вставить в документ Word (точно так же, как при использовании кнопки Вставить таблицу). После этого вы увидите в своем документе объект Excel , который выглядит так:



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

3. Щелкните в документе Word за пределами таблицы, чтобы вернуться к работе с документом. Тех же самых результатов можно добиться, выбрав команду Вставка  Объект , указав вкладку Создание, выбрав из списка Тип объекта пункт Лист Microsoft Excel и щелкнув по ОК.

Связывание ячеек

Чтобы связать ячейки книги Excel с документом Word , поступайте так:

1. Щелкните по кнопке Microsoft Excel на панели инструментов Microsoft , чтобы запустить Excel .

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

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

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

5. Выберите команду Правка  Специальная вставка .

6. В диалоговом окне Специальная вставка установите опцию Форматированный текст (RTF ). Установите флажок Связать и щелкните по ОК.

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

Использование диаграмм Excel

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

Самостоятельно создайте в Excel диаграмму и выполните вставку и внедрение диаграммы в Word .




Найдите материал к любому уроку,

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

Порядок выполнения задания.

Открыть файл с заданиями по Excel. После открытия книги перейти на чистый рабочий лист и переименовать его Лаб3.

Написать название задания, используя технологию объединения нескольких ячеек А1:J1 в одну.

Оформить заголовок таблицы, по аналогии, используя технологию объединения нескольких ячеек A 2: J 2 в одну.

Действия:

Для размещения текста в двух строках (как в примере), установить курсор после слова "Альянс" и нажать комбинацию клавиш < Alt > + < Enter >.

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

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

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

Действия:

Выделить диапазон ячеек A 3: J 3 ,в которых текст нужно расположить в несколько строк. Выбрать Ячейки Формат Формат ячеек.... Выравнивание Переносить по словам ОК .

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

Ввести текст в ячейки <шапки> таблицы и настроить ширину столбцов по образцу.

Действия:

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

Оформить внешний вид <шапки> таблицы, используя опции вкладок диалогового окна Формат ячеек:

Шрифт (Times New Roman, 10, полужирный);

Выравнивание (по горизонтали и по вертикали - по центру);

Выравнивание (для ячейки В3- вертикальная ориентация текста);

Граница (тип линий для оформления границ);

Заливка (выбрать заливку, по собственному усмотрению).

Ввести данные в столбцы А, В, С, D , F , Н и с учетом типов данных, установить соответствующие форматы

Название - текстовый;

Код - текстовый;

Страна поставщик - текстовый;

Дата поставки - дата;

Количество - число;

Единица измерения - текстовый;

Цена в валюте -денежный

Курс валюты - число;

Произвести расчеты в столбце "Цена в рублях". Формат значений столбца Цена в рублях - денежный.

Действия:

I 4 и ввести формулу = G 4* H 4. . В ячейках I 4: I 10 произвести автозаполнение используя маркер заполнения.Должен появиться результат как на образце (рис. 2.18).

3.8 Вычислить значения Суммы (руб.) в ячейке J 4 по формуле =Е4* I 4, затем размножить формулу на ячейки J 5: J 10. Результат сравнить с образцом (рис. 2.18).

3.9 Установить Условный формат для отображения в разном цвете значений столбца "Сумма (руб.)" в соответствии с условиями:

для суммы больше или равно 300000, установить цвет - красный.

для суммы между значениями 100000 и 300000, установить цвет - синий;

Для суммы меньше или равно 100000, установить цвет - зеленый.

Использование отображения информации в таблице в разном цвете удобно при отслеживании, например, границ цен (рост, падение).

Действия:

Выделить диапазон ячеек J 4: J 10. Выбрать Главная Стили Условное форматирование Правила выделения ячеек Другие правила…

Впоявившемся одноименном диалоговом окне (рис. 2.15). В соответствии с указанными номерами шагов на рисунке 2.15 установить указанные параметры.

Рис. 2. 15 Вариант отображения значений для условного формата

В результате значения столбца Сумма (руб.)

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

Возможен другой вид форматирования с применением Набора значков .

Выделить диапазон ячеек J 4: J 10. Выбрать Главная Стили Условное форматирование Создать правило.... Появится одноименное диалоговое окно (рис. 2.16). В соответствии с указанными номерами шагов на рисунке 2.16 установить соответствующие значения.

ОК. В результате значения столбца Сумма (руб.) будут отображены в цвете соответствий условному формату.

Рис. 2. 26 Вариант отображения значений для условного формата

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

Действия:

Выделить диапазон ячеек А4:А10 (содержащий наименования товаров).

Выбрать Главная Стили Условное форматирование Создать условие. Появится одноименное диалоговое окно (рис. 2.17). В появившемся окне замените значение выпадающего списка на формулу и внести формулу по образцу (рис.2.17).

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

Рис. 2. 37 Вариант отображения значений для условного формата

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

3.11 Установить Условный формат. Выделитьжелтым цветом строки с товарами, доставленными на склад позднее 10 августа 2010 г.

Действия:

Скопировать таблицу в ячейки А12: J 20 .

Выделить диапазон ячеек А14: J 20 , т.е. всю таблицу с данными.

Удалите предыдущие правила форматирования Главная Стили Условное форматирование Удалить правила Удалить правила из выделенных ячеек .

Выбрать Главная Стили Условное форматирование Создать правило... Появится одноименное диалоговое окно (рис. 2.18). В появившемся окне заменить значение выпадающего списка на формулу и внести формулу по образцу (рис.2.18).

Нажать кнопку Формат, выбрать вкладку Заливка и установить желтый цвет.

Для завершения действий нажать кнопку ОК. В результате в желтый цвет будут окрашены те строки таблицы, которые содержат дату поступления товара позднее 10 августа 2010 года.

Рис. 2. 48 Вариант отображения значений для условного формата

3.12 Создать примечание (комментарий) в ячейке В10 столбца "КОД".

Действия:

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

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

3.13 Второй таблице присвоить столбцу: "Цена в валюте" имя "Цена", а столбцу "Курс валюты ЦБ РФ" - "Курс".

Действия:

Выделить диапазон ячеек G 14: G 20 для присвоения имени.

На вкладке Формула , в группе Определенные имена , выбрать Присвоить имя . Ввести любое имя (не совпадающее с адресами ячеек), и не имеющее пробелов, например, имя "Цена".

По аналогии, присвоить диапазону ячеек Н14:Н20 имя Курс.

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

Действия:

Установить курсор ввода в ячейку I 14 и ввести формулу = Цена * Курс, используя меню Вставка Имя Вставить.

Скопировать формулу ячейки I 14 в ячейки I 15: I 20 с помощью маркера заполнения.

Присвоить имена данным Е14:Е20 столбца Кол-во и данным I 14: I 20 столбца Цена в рублях. Затем использовать присвоенные имена для вычисления значений в столбце Сумма (руб.).

3.15 Отобразить примечание ячейки В20 на экране. Отобразить все имена электронных таблиц книги.

Действия:

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

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

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

Действия:

Добавить в ячейку К3 название колонки Кол-во товара на 31.12.2010 .

Заполнить ячейки К4:К10 случайными числами в диапазоне от 10 до 500. Воспользуйтесь функцией СЛУЧМЕЖДУ из категории МАТЕМАТИЧЕСКИЕ.

Добавить в ячейку K 2 дату 31.12.2010

Добавить в ячейку L 3 название колонки Заказ на поставку товара.

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

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

если товара на складе осталось больше, чем продаётся в среднем в месяц и меньше или ровно столько, сколько продаётся в среднем за три месяца,напечатать - в следующем месяце;

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

Внести в ячейку L 4 формулу:

ЕСЛИ((E4-K4)/(МЕСЯЦ($K$2)-МЕСЯЦ(D4))<=K4;"срочный заказ"; ЕСЛИ((E4-K4)/ (МЕСЯЦ($K$2)-МЕСЯЦ(D4))<=3*K4;"в следующем месяце";""))

Размножить внесённую формулу на диапазон L 4: L 10 .

3.17 Отобразить влияющие ячейки.

Действия:

Установить курсор в ячейку L 4. На вкладке Формулы , в группе Зависимости формул , выбрать команду Влияющие ячейки. Появятся стрелки, указывающие от каких ячеек зависит значение в ячейке L 4.

Эта информация удобна при поиске ошибок при расчетах.

3.18 Создать автоматически диаграмму для данных двух столбцов таблицы: А4:А10 и J 4: J 10 по образцу (рис. 2.20).

Действия:

Выделить диапазон данных А4:А10.

Нажать клавишу < Ctrl > и, не отпуская ее, выделить следующий диапазон данных J 4: J 10. Будут выделены два несмежных диапазона ячеек.

Нажать клавишу < F 11>. На рис. 2.20 показана гистограмма, построенная описанным только что способом.

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

В поле окна Название диаграммы: ввести заголовок по образцу (рис.2. 20). Нажать кнопку ОК .

Используя вкладку Работа с диаграммами , отформатируйте построенную диаграмму по образцу рисунка 2.20.

Рис. 2. 59 Вид электронной таблицы к заданию №3 по Excel

Рис. 2. 20 Диаграмма, созданная автоматически на листе диаграмм