«Microsoft Excel 97. Работа № 1 Знакомство с электронными таблицами Excel. Цель работы: Знакомство с основными приемами работы в Excel и встроенными функциями этой программы на примере таблицы выручки от продаж в ...»

Microsoft Excel 97. Работа № 1

Знакомство с электронными таблицами Excel.

Цель работы: Знакомство с основными приемами работы в Excel и встроенными

функциями этой программы на примере таблицы выручки от продаж в

различных магазинах (рис. 1).

Электронная таблица – компьютерный эквивалент обычной таблицы, в клетках

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

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

Отличительной чертой Excel является то, что рабочее поле имеет вид таблицы, где имена столбцов – латинские буквы, а строки имеют нумерацию. Также курсор мыши имеет совершенно иной вид –, чем в текстовом редакторе Word, где он выглядит мигающей чертой – |.

На рис. 1 приведена только часть окна Excel.

Проанализируем содержание таблицы.

Таблица находится в диапазоне ячеек А1:F7. В строках 1 и 2 находятся заголовки таблицы, выровненные по центру всех соответствующих столбцов таблицы. Столбец А расширен, так как записи в столбце А превышают стандартную ширину столбца (т.е.

ширину столбца по умолчанию). В таблице применено обрамление снизу в строках 3, (тонкая линия) и 7 (полужирная линия). В строках 4, 5 и 6 по столбцам B, C, D, E и F вводятся значения выручки, полученной от каждого магазина. В строке 7 отображается сумма выручки, полученная магазинами в каждом месяце.

Рис. 1. Таблица "Выручка от реализации книжной продукции" 1. Запустите программу Excel (Пуск–Программы–Microsoft Excel).

2. Ввод текста. В Excel существует несколько различных способов подтверждения ввода текста, формул и т.п. и очистки содержимого ячейки. Установите курсор мыши на ячейку А1 и щелкните левой кнопкой: ячейка будет окружена темной рамкой с точкой в правом нижнем углу, а в поле имени отображается адрес этой ячейки – А1. Ячейка А1 стала активной.

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

Перед текстом появились три кнопки:

кнопка с крестиком – "отмена", эквивалентна клавише Esc кнопка с "галочкой" – "Ввод", эквивалентна клавише Enter кнопка со знаком = – изменить формулу строка формул – отражает содержимое активной ячейки Для сохранения внесенного текста в текущей ячейке рабочего листа необходимо дать подтверждение.

Это можно сделать разными способами:

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

нажать кнопку с галочкой слева от строки формул;

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

покинуть ячейку с помощью клавиш управления курсором (стрелки).

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

нажать на клавиатуре клавишу Delete;

выбрать команду Правка–Очистить–Все;

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

! Если вы случайно удалили содержимое ячейки, выберите команду Правка–Отменить очистку либо нажмите левой кнопкой мыши на панели инструментов "Стандартная" кнопку.

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

3. Центрирование заголовка по столбцам таблицы.

! Введенный текст по длине превышает ширину столбца. Так как это заголовок, то нам следует объединить ячейки от А1 до F1 включительно и выровнять текст по центру.

Сделайте активной ячейку А1. Курсор мыши должен находиться на этой ячейке.

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

Нажмите на панели инструментов Форматирование кнопку Объединить и поместить в центре 4. Введите текст в ячейки столбца А.

! Обратите внимание, что после каждого подтверждения ввода (Enter), активной становится следующая ячейка.

А2: Осень А3: (пропустить) А4: Книжный мир А5: Книга А6: Магазин № А7: Всего ! Длина текста в А4 больше ширины столбца и закрывает ячейку B4. Рассмотрим способы изменения ширины столбца:

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

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

5.2. Выберите команду Формат–Столбец–Ширина и задайте числовое значение ширины столбца (в мм), затем нажмите OK.

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

5.4. Можно изменить ширину столбцов при помощи перетаскивания границы столбца.

Для этого поместите курсор мыши в строку имен столбцов на границу между столбцами. Курсор изменится и примет вид. Нажмите левую кнопку мыши и (В Excel это действие аналогично действию при работе с таблицей в Word).

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

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

6. Форматирование текста в ячейках.

Названия месяцев надо выровнять по центру. Для этого можно поступить следующим образом:

1-й способ.

1.1. Щелкнуть на имени строки 3. Строка будет выделена цветом.

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

2-й способ.

2.1. Выделить нужный диапазон ячеек A3:F3. Для этого поместите курсор в ячейку A3, нажмите кнопку мыши и, удерживая ее, протащите курсор до ячейки F3. Нужный диапазон ячеек будет выделен.

2.2. Поступите аналогично п. 1.2.

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

7. Простые вычисления. Ввод формулы. Сумма. Автосуммирование.

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

Ввод формулы сложения. Сумма.

Поместите курсор в ячейку В7. Наберите знак равенства = ! Формула в ячейке всегда должна начинаться со знака =.

В ячейке В7 должна быть задана операция – формула сложения нескольких данных:

= содержимое ячейки В4 + содержимое ячейки В5 + содержимое ячейки В6. Эту операцию можно выполнить двумя способами.

1-й способ: Ввод формулы.

1.1. За знаком равенства надо непосредственно напечатать В4+В5+В6 в строке формул появится запись: =В4+В5+В6. Для подтверждения нажмите Enter.

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

названия столбцов являются латинскими буквами, а не русскими.

1.2. После того, как Вы ввели знак равенства, щелкните левой кнопкой мыши на ячейке В4, напечатайте +, затем щелкните на ячейке В6, еще раз напечатайте + и щелкните на ячейке В7. Если Вы подтвердите формулу, нажав Enter, то в ! Если Вы не ввели знак равенства, то в ячейке появится не результат, а текст: В4+В5+В6.

2-й способ: Автосуммирование.

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

Поместите курсор в ячейку С7, и нажмите кнопку автосуммирования.

!Обратите внимание: в ячейке С7 и в строке формул автоматически появилась формула =СУММ(С4:С6), где СУММ – функция суммирования;

С4:С6 – диапазон суммируемых ячеек (обозначен пунктирным мерцающим прямоугольником).

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

Задание. Любым способом найдите сумму выручки за Сентябрь и Октябрь.

!! Если Вы используете функцию автосуммирования при подсчете выручки за Октябрь, то Вам будет предложена сумма по 7-й строке – =СУММ(В7:Е7), а пунктирный прямоугольник будет охватывать соответствующие ячейки этой строки. Что нужно сделать?

1-й вариант:

Вручную с клавиатуры ввести правильную формулу, т.е. набрать адреса первой и последней ячеек через двоеточие: =СУММ(F4:F6).

2-й вариант.

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

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

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

1-й способ:

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

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

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

Для того чтобы снять команду копирования с ячейки С7 нажмите клавишу Esc.

2-й способ:

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

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

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

3-й способ: Автозаполнение ячеек.

Щелкните в ячейку С7. Появится обрамление с небольшим квадратом в правом нижнем углу.

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

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

Формула стала иметь следующий вид: =СУММ(D4:D6).

Задание. Попробуйте различные способы копирования. Перед каждым способом очистите содержимое соседних ячеек по строке 7. Что происходит с формулой при копировании ячейки С7 в ячейку F7?

9. Оформление таблицы - Форматирование.

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

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

Остановимся подробнее на обрамлении таблицы и заполнении цветом.

Обрамление.

Выделите нужный диапазон ячеек: A3:F3.

1-й вариант:

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

2-й вариант:

Выберите команду Формат–Ячейки–Граница. Из диалогового окна выберите нужный тип границы (обрамления), укажите к какой части ячейки или всего диапазона ячеек его надо применить и нажмите ОК.

Заполнение цветом.

Выделите нужный диапазон ячеек.

1-й вариант.

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

2-й вариант.

Выберите команду Формат–Ячейки–Вид. В диалоговом окне выберите нужный цвет и нажмите ОК.

10. Сохранение файла.

Эта операция ничем не отличается от аналогичной операции в текстовом редакторе Word. Обратите внимание, что файлы рабочих книг в Excel имеют расширение *.xls.

(В текстовом редакторе Word – файлы имеют расширение *.doc).

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

! Проверьте в диалоговом Окне сохранения все параметры сохраняемого файла:

Папку в которой будет сохранен файл;

Расширение файла (должно быть Книга Microsoft Excel (*.xls)) 11. Совместное использование приложений Windows.

Как Вы уже знаете, в ОС Windows имеется возможность одновременной работы с несколькими приложениями.

Не закрывая приложение Excel, запустите другое приложение – текстовый редактор Word. (Пуск–Программы– Microsoft Word).

Откройте файл Таблицы для работы № 1 Excel.doc, который находится в папке:

С:\ Мои документы \ Заготовки для Word.

Сразу же сохраните этот файл в свою папку под новым именем – "Панели инструментов в Excel" и добавьте свою фамилию.

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

Указание.

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

Таблица 1. Назначение кнопок панели инструментов Стандартная Заполните таблицу по образцу, используя информацию из контекстной подсказки и текста Предварительный Предварительный просмотр файла в том виде, в котором он Таблица 2. Назначение кнопок панели инструментов Форматирование Заполните таблицу, используя информацию из контекстной подсказки и текста Помощи.

Форматирование ячеек. Рабочие листы Excel.

Цель работы: Изучение свойств и форматов ячеек, определение форматов чисел, времени и даты. Основные приемы работы с рабочими листами в Excel:

переименование, добавление, удаление и перемещение листов.

1. Форматы ячеек.

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

Основные свойства диалогового окна Формат ячеек:

Число – закладка для выбора числового формата, определения количества десятичных знаков, выбора обозначения введенного числа;

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

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

Граница – закладка для выбора типа линии ее цвета и места расположения на сторонах ячейки (возможно проведение диагоналей в ячейке);

Вид – закладка для выбора фонового цвета и типа узора при заливке ячейки;

Защита – закладка для установки защиты на одну или несколько ячеек от несанкционированного изменения или удаления данных.

ВНИМАНИЕ !!! В случае задания пароля на защищаемую ячейку – ПОМНИТЕ ЕГО!!! Иначе придется удалить весь рабочий лист или весь файл !!!

!!! Совет начинающим: не используйте пароли в начале освоения Excel, да и потом с большой осторожностью.

По умолчанию все числа представляются в общем формате. В этом формате число может состоять максимум из 11 цифр, если позволяет ширина ячейки. Числа отображаются как целые (например, 21947 или 12387), или с десятичной точкой (запятой) (например, 12,57 или 3+14159), или в научной (экспоненциальной) форме (например, 9,43Е+7 или 21, 212Е-5).

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

Если число содержит более 8 знаков, то Excel выводит число в научном (экспоненциальном) формате. Например, число 123456789012 запишется в виде 1,23E+11, что означает 1, 23 е11, где е=2,7.

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

13,50 Числовой, число десятичных знаков = 13,500 Числовой, число десятичных знаков = 13,50 р. Денежный, обозначение валюты – р.

1 Январь, 1998 Дата 1350,00% Процентный, число десятичных знаков = 13 50/100 Дробный с сотыми долями 1,35E+01 Экспоненциальный Текстовый (число воспринимается как текст и не участвует в расчетах Раздел содержит все типы перечисленных числовых форматов по порядку !!! Прежде чем нажимать на кнопки, сначала прочитайте задание и указания к его выполнению.

Задание. Для изучения форматов представления чисел заполните таблицу, показанную на рис. 2. В ячейки столбцов, форматы которых приведены в заголовках таблицы, поразрядно вводятся целые числа от 1 до 12345678901 – см. числа в Указания:

1) Для оформления общего заголовка таблицы "Форматы чисел" – воспользуйтесь кнопкой "Объединить и поместить в центре" (работа № 1). Аналогично оформите заголовок таблицы в столбцах В и С.

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

3) В диалоговом окне Формат ячеек откройте закладку Выравнивание, установите флажок в поле Переносить по словам, выберите в поле По вертикали – По центру, По горизонтали – По центру, затем нажмите OK.

4) Внимательно изучите содержимое рисунка 2.

5) Введите числа в ячейки столбца А, основываясь на содержимом столбца С.

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

7) После заполнения таблицы задайте формат ячеек в каждом столбце.

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

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

Ширину столбца С установите такой, чтобы число в 13-й строке умещалось полностью.

!!! Если в ячейке вместо числа появляется решетка ( ######## ), то это означает, что число в данном формате не умещается в этой ячейке.

Задание. Проследите за отображением чисел различных форматов при изменении ширины столбца. В столбец с форматом дробный введите дробные числа (из школьного курса математики известно, что 1,4 = 1 2/5).

2. Рабочие листы в Excel.

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

Поэтому не путайте Рабочий лист с обычным листом бумаги.

В Excel рабочие листы по умолчанию имеют названия: Лист1, Лист2, Лист3,...и т.д.

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

Слева от них имеются кнопки прокрутки листов.

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

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

Существует два способа выполнения данной операции.

1-й способ:

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

2-й способ:

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

Введите новое имя поверх старого и нажмите Enter.

Задание. Переименуйте лист на котором находится таблица Форматы чисел с прежнего названия "Лист1" на новое "Форматы чисел".

Добавление рабочих листов.

При работе в Excel часто требуется добавить один или несколько новых рабочих листов. Существует два способа добавления рабочих листов в рабочую книгу Excel.

1-й способ:

В меню Вставка выберите команду Лист;

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

2-й способ:

Щелкните правой кнопкой мыши на ярлычке листа из контекстного меню и выберите команду Добавить...–Лист–OK;

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

Примечание: Обратите внимание, куда вставились новые листы. Какие у них имена?

Удаление рабочих листов.

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

При удалении рабочих листов программа Excel выдает предупреждение о невозможности отмены данной операции и два варианта выхода из этой ситуации:

кнопка OK – процедура удаления будет продолжена и листы удалятся;

кнопка Отмена – процедура удаления будет остановлена (отменена), Существует два способа удаления рабочих листов.

1-й способ:

Активизируйте лист;

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

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

2-й способ:

Активизируйте лист;

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

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

Перемещение рабочих листов.

В Excel существует три способа перемещения рабочих листов.

1-й способ:

Активизируйте лист;

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

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

2-й способ:

Активизируйте лист;

Правой кнопкой мыши вызовите контекстное меню и выберите команду Переместить/Скопировать...;

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

3-й способ:

Левой кнопкой мыши "захватите" ярлычок листа и перенесите его в другое место.

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

Относительные и абсолютные адреса ячеек.

Графическое представление числовых данных.

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

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

Ссылки на ячейки других книг называются внешними ссылками.

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

По умолчанию в Excel используются ссылки типа A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от до 65 536). Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона. В следующей таблице представлены варианты возможных ссылок.

Диапазон ячеек в 15-й строке со столбца B по столбец E B15:E В зависимости от поставленной задачи можно использовать:

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

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

Различия между относительными и абсолютными ссылками.

При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. Например, ячейка B6 содержит формулу =A5; искомое значение находится на одну ячейку выше и левее ячейки B6.

При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место, ссылки будут указывать на другие ячейки. Например, если формула из ячейки B6 копируется в ячейку B7, то в ячейке B7 будет содержаться формула =A6, которая ссылается на одну ячейку выше и левее ячейки B7.

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

Такой тип ссылок называется относительным.

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

Абсолютная ссылка бывает двух типов: частичная и полная.

Частичная абсолютная ссылка при копировании или перемещении ячейки не изменяет тот параметр адреса ячейки, перед которым стоит знак $ – это либо столбец, либо строка.

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

Чтобы создать полную абсолютную ссылку на ячейку C1, впечатайте знак доллара перед именем столбца и перед номером строки в адресе ячейки, например, =A5*$C$1.

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

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

Создайте таблицу по образцу (рис. 1). Эта операция аналогична заданию в Работе № 1 Ячейки в столбце F имеют формат Процентный, а количество десятичных знаков = 0.

В ячейке F5 формула должна содержать абсолютную ссылку на ячейку Е9:

=Е5/$E$9. В этом случае при копировании ячейки F5 в ячейки F6, F7, F8, абсолютная ссылка $E$9 останется неизменной, а будет меняться только относительная ссылка по столбцу Е (Е6, Е7, Е8).

Для оформления таблицы используйте команду Формат–Автоформат. Выберите стиль оформления Простой.

2. Графическое представление числовых данных. Диаграммы и графики.

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

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

На рис. 2 показаны два типа диаграмм: столбчатая гистограмма и круговая диаграмма.

Основные параметры диаграмм.

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

Диаграмма может содержать такие элементы, как: сетка, заголовки и расшифровка условных обозначений – легенда.

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

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

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

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

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

Диаграмма может размещаться и на отдельном листе диаграммы.

Самое интересное состоит в том, что диаграмма всегда связана с данными ячеек.

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

Задание. Постройте внедренную диаграмму по данным ячеек F5, F6, F7, F8, как сказано Создание диаграммы.

В Excel построение диаграмм происходит по шагам, где на каждом этапе выбираются или задаются необходимые параметры для оформления диаграммы.

Выделите в таблице ячейки F5:F8.

Нажмите кнопку Мастер диаграмм на панели инструментов Стандартная или выберите команду Вставка – Диаграмма.

В открывшемся окне Мастер диаграмм Тип диаграммы выберите Простую гистограмму и нажмите Далее >. В следующем окне: Источник данных диаграммы диапазон данных и показан образец диаграммы. Нажмите Далее >. В следующем шаге:

Параметры диаграммы Вы можете ввести Заголовки, Оси, Линии сетки, Легенду, Подписи данных и Таблицу данных. На этом этапе Вы можете поэкспериментировать с оформлением диаграммы и затем нажать Далее >. В последнем окне: Размещение диаграммы предлагается поместить диаграмму на имеющемся листе, поэтому нажмите Готово (так же возможно размещение диаграммы на отдельном листе).

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

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

Для изменения вида диаграммы или внесения дополнений (названия диаграммы, определения ряда для подписей по оси Х и т.п.) необходимо активизировать область диаграммы и вызвать Мастер диаграмм.

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

Указания.

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

Задание 3. Отредактируйте диаграмму:

– внесите название диаграммы Продажа мороженного за лето 1999 года;

– задайте подписи оси Х, используя данные диапазона ячеек А5:А8;

– задайте Объемный вариант Круговой диаграммы используя те же данные таблицы;

– измените имя текущего листа на новое – Мороженое.

– сравните полученный результат с рис. Расчет арифметической прогрессии и построение диаграммы.

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

Сумма n первых членов – Sn арифметической прогрессии равна где a1 – первый член прогрессии, d – разность арифметической прогрессии.

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

Задание. Составить таблицу, вычисляющую n-й член и сумму арифметической прогрессии. Первый член прогрессии n1 равен –2, разность d равна 0,725.

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

Рис. 5. Таблица с вычислениями n-го члена и суммы арифметической прогрессии Этапы выполнения задания.

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

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

При оформлении строки заголовка в ячейках C2 и D2 потребуется использовать нижний индекс. В строке формул выделите нужный символ и задайте ему способ отображения, для этого выберите команду Формат–Ячейки–Шрифт–Нижний индекс.

Заголовки должны быть выровнены по центру, шрифт полужирный. Размер шрифта равен 12 (см. рис. 5).

Разместите последовательность чисел от 1 до 10. Для этого введите в А3 число 1 и затем в А4 ячейку число 2. Выделите обе ячейки А3 и А4. В правом нижнем углу прямоугольника выделения находится небольшой квадратик – маркер автозаполнения.

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

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

Введите в ячейку В4 значение разности арифметической прогрессии d: 0,725.

Зацепите маркер автозаполнения и заполните нижние ячейки.

В ячейку С3 введите значение первого члена арифметической прогрессии: –2.

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

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

В ячейку D3 введите формулу, вычисляющую сумму n первых членов арифметической прогрессии, которая для записи в Excel имеет вид: =(-2+C3)*A3/2 или =($C$3+C3)*A3/2, что соответствует формуле нахождения суммы арифметической прогрессии.

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

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

Выделите диапазон ячеек С3:D12 со значениями членов арифметической прогрессии и суммы членов арифметической прогрессии.

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

После выполнения задания измените в ячейке С3 значение первого члена прогрессии на -3 и после этого придется в ячейках D3:D12 изменить формулы расчета суммы прогрессии, если была использована формула =(-2+C3)*A3/2, если же использовалась формула =($C$3+C3)*A3/2 изменения вносить не потребуется, т.к. она не содержит числового значения первого члена прогрессии.

Обратите внимание на изменения точечной диаграммы.

В завершении переименуйте Лист2 на Арифм. прогрессия.

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

Задание. На Листе3 составьте универсальную таблицу для расчета арифметической прогрессии и анализа ее с помощью диаграмм – рис. 6.

Указания.

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

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

ячейка В7: =А3;

ячейка В8: = В7+$А$4 (используется формула с абсолютной ссылкой на ячейку А4).

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

ячейка С7: =($А$3+В7)*А7/2 (содержит абсолютную ссылку на ячейку А3).

Переименуйте Лист3 и дайте ему новое имя – Универс.таб.арифм.прогрес.

Не забудьте построить диаграмму!

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

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

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

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

Данные делятся на две категории: исходные и расчетные.

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

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

Формирование рабочей книги.

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

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

Задание. Переименуйте рабочие листы:

Лист1 – Закупка, Лист2 – Реализация, Лист3 – Цена, Лист4 – Выручка, Лист5 – Доход.

Для этого, нажмите правой кнопкой мыши на ярлычке рабочего листа и выберите в контекстно-зависимом меню команду Переименовать, (см. рис. 1) наберите с клавиатуры новое имя листа – Закупка, после окончания ввода нового имени нажмите клавишу ENTER.

Аналогичным образом переименуйте остальные листы рабочей книги.

Указание.

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

После выполнения этих операции рабочая книга примет вид:

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

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

Создание и заполнение таблиц данными. Ввод формул.

Начнем заполнение данными имеющихся рабочих листов: Закупка, Реализация, Цена, Выручка, Доход.

Задание. На рабочем листе "Закупка" создайте таблицу и внесите данные, как Указание.

! Для оформления общего заголовка таблицы воспользуйтесь кнопкой "Объединить и поместить в центре", для размещения заголовков таблицы во всех столбцах следует выделить всю 3-ю строку, затем щелкнуть на ней правой кнопкой мыши, из контекстного меню выберите команду Формат ячеек, откройте закладку Выравнивание, установите флажок в поле Переносить по словам, выберите в полях По вертикали – По центру, По горизонтали – По центру, затем нажмите OK.

! Перед вводом данных задайте форматы ячеек таблицы – Числовой, число десятичных знаков 0, выравнивание – по центру. Наименование предметов – формат Текстовый.

Выравнивание – по левому краю (отступ) – 1.

! В ячейках H4:Н8 – введите формулы суммирования по строкам (обратите внимание на предлагаемый диапазон суммируемых ячеек, если диапазон не захватывает все исходные данные – укажите мышью весь необходимый диапазон, затем нажмите Enter).

Рис. 3. Общий вид исходной таблицы на рабочем листе Закупка.

Задание. На рабочем листе "Реализация" внесите исходные данные в таблицу и оформите, как показано на рисунке 4.

Рис. 4. Общий вид исходной таблицы на рабочем листе Реализация.

Сохранение рабочей книги (файла).

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

Для этого выберите команду Сохранить как... в меню Файл, затем в диалоговом окне "Сохранение документа" найдите и откройте свою папку, присвойте файлу имя – Расчет дохода и добавьте свою фамилию. Нажмите кнопку Сохранить.

Задание. На рабочем листе "Цена" создайте и заполните две таблицы – Расходы на закупку и Расчет цен как показано на рисунке 5.

Указание.

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

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

А4:А9 – текстовый;

В4:В8 – денежный, число десятичных знаков – 2, обозначение – р.

C4:C8 – числовой, число десятичных знаков – 0.

D4:D9 – денежный, число десятичных знаков – 2, обозначение – р.

В ячейки А4:С8 внесите данные с клавиатуры.

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

Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В4, нажмите клавишу "знак умножения – * на дополнительной клавиатуре и щелкните мышью на ячейке С4, подтвердите формулу – нажмите клавишу Enter.

Чтобы не повторять набор формулы в ячейках D5, D6, D7, D8. Скопируйте в эти ячейки содержимое ячейки D4 вместе с формулой.

Для снятия команды копирования с ячейки D4 нажмите клавишу Esc.

Обратите внимание на изменение ссылок в формулах суммирования, т.к. они относительные.

Задайте форматы ячеек в таблице Расчет цен:

А14:А18 – текстовый;

В14:В18 – денежный, число десятичных знаков – 2, обозначение – р.

C14:C18 – процентный, число десятичных знаков – 0.

D14:D18 – денежный, число десятичных знаков – 2, обозначение – р.

В ячейки А14:С18, В14:В18 и С14:С18 внесите данные с клавиатуры.

В ячейку D14 введите формулу = В14*С14+В Для этого наберите с клавиатуры знак =, затем щелкните левой кнопкой мыши на ячейке В14, нажмите "знак умножения – *, щелкните мышью на ячейке С14, затем нажмите "знак сложение – + и подтвердите формулу и нажатием клавиши Enter.

Аналогично предыдущей таблице скопируйте содержимое ячейки D14 с формулой в ячейки D15, D16, D17, D18.

Примените к таблицам обрамление, как показано на рисунке 5.

Задание. Создайте и заполните таблицы на листе Выручка, как показано на рисунке 6.

Указание.

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

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

Задание. Заполните и оформите таблицы на листе Доход (рис. 7).

Указание.

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

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

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

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

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

В ячейках E4:E8 находится формула =D4*(B4-C4).

В строке ИТОГО в ячейке E9 находится формула, суммирующая ячейки E4:E8.

В ячейках E14:E18 находится формула =D14*(B14-C14).

В строке ИТОГО в ячейке E19 находится формула, суммирующая ячейки E14:E18.

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

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

Убедитесь в этом на простых примерах:

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

2. Измените на листе Реализация количество проданных стульев в феврале с 18 на 50. Проверьте, произошли соответствующие изменения на других рабочих 3. Измените на листе Цена в таблице Расходы на закупку в графе Цена закупки (ячейка В4) число 2 000,00 р. на новую цену закупки – 5 000,00 р. Проверьте, произошли соответствующие изменения в соседней таблице Расчет цен, и на других рабочих листах где используется данный показатель?

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

Изменения происходят только в тех ячейках, которые имеют прямую связь с исходной ячейкой например, при выполнении п.3 на листе Цена изменяется ячейка D4, т.к.

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

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

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

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

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

В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

1 способ – "Прямое связывание ячеек" Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”.

Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "!".

Примеры формул: = C5*Лист1! A Примечание.

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

Например: = 'C:\Мои документы\[Расчет дохода.xls]Закупка'!H Имя диска 2 способ – Связывание ячеек через команду "Специальная вставка" Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.

Чтобы отразить в ячейке С4 на листе Цена значение ячейки Н4 на исходном листе Закупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка–Копировать. На листе Цена поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка–Специальная вставка– Вставить связь (см рис. 8). Тогда на листе Цена появится указание на ячейку исходного листа Закупка, например: = Закупка!$Н$ При таком связывании EXCEL автоматически использует абсолютный адрес на ячейку, т.к. относительный адрес обращения может привести к ошибкам, особенно если обращаться к незагруженным файлам (рабочим книгам).

Задание. Свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена с соответствующими ячейками на листе Закупка, используя различные способы связывания ячеек (рис. 8).

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

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

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

Например:

1. на листе Цена в таблице Расходы на закупку ячейки А4:А8 связаны с ячейками таблицы Количество закупленной продукции на листе Закупка;

2. ячейки В4:В8 являются исходными, т.к. содержат первоначальные сведения о ценах закупленного товара;

3. ячейки С4:С8 связаны с ячейками Н4:Н8 на листе Закупка;

4. ячейки D4:D8 содержат формулы подсчета затраченных средств на приобретенный товар и ссылаются на ячейки собственной таблицы (например, формула в ячейке D имеет вид =В4*С4, что означает умножение цены товара на его количество);

5. ячейка D9 является суммой ячеек D4:D8;

6. во второй таблице Расчет цен на этом же листе ячейки А14:А18 связаны аналогично 7. ячейки В14:В18 являются связанными с исходными ячейками текущего листа В4:В8;

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

9. ячейки D14:D18 содержат формулы расчета цены продажи товара и ссылаются на ячейки собственной таблицы (например, формула в ячейке D14 имеет вид =В14*С14+В14, что означает умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую надо прибавить к закупочной цене);

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

Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – например Софа.

Измените количество закупленного товара Софа в июне (в ячейке G4 на листе Закупка введите число 11).

Измените цену закупки Софы в ячейке В4 на листе Цена на другую – 2500,00 р.

Измените процент наценки Софы в ячейке С14 на листе Цена с 50% на 32%.

Проверьте, произошли изменения в связанных таблицах или нет?

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

Внимание! При связывании ячеек через СПЕЦИАЛЬНУЮ ВСТАВКУ...

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

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

Задание 2. Создайте на листах Выручка и Доход таблицы по расчету за 2 квартал.

Свяжите эти таблицы с соответствующими исходными данными.

Указание. В таблицах по расчету выручки и дохода за 2 квартал используйте исходные Задание 3. Постройте круговую диаграмму на листе Доход и проанализируйте распределение дохода по видам продукции.

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

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

Цель работы: создание автоматизированного документа c использованием "собственных" имен ячеек, текстовых и логических функций, Мастера В этой работе мы создадим прайс-лист фирмы "МультиМедиа Технологии".

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

С:\ Мои документы \ Заготовки для Excel \ price.xls (рис.1).

Откройте его. Вам нужно оформить имеющийся документ в конечный проект (см. листы приложений 1 и 2).

Рис. 1 Исходный вид документа прайс-лист (из файла price.xls).

Проанализируйте содержание документа: название фирмы, название прайс-листа, почтовый адрес, телефон, факс и два адреса электронной почты.

Также обратите внимание на расположение текста в ячейках, на ширину столбцов, имена Рабочих листов.

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

1. Переместите заголовок на столбец вправо. Для этого выделите ячейки с заголовками и адресами (шапку документа) и левой кнопкой мыши протяните боковую границу выделенных ячеек на два столбца вправо (не надо тащить за маркер автозаполнения!!!). Заголовок переместился. Сейчас будет понятно, 2. Настройте ширину столбца А. Для этого выделите весь столбец А и выберите команду Формат–Столбец–Автоподбор ширины.

Если бы не переместили заголовки, то ширина столбца А была бы совсем 3. Создайте шапку самой таблицы: Наименование и Цена продукта ячейки А7 и В7.

Так как цены будут выражены в долларах и в рублях (цены в рублях будем рассчитывать по текущему курсу ММВБ!), то ячейку В7 в заголовке объединим с ячейкой С7, а в ячейках В8 и С8 обозначим цены в USD и руб. соответственно.

Выполните центрирование текста заголовка таблицы. Если нужно, измените 4. Оформите шапку документа:

в строке 1 – Мультимедиа Технологии (шрифт Arial, полужирный, курсив, размер – 14 пт, выравнивание – по центру трех ячеек);

в строке 2 – Прайс-лист программной продукции (шрифт Arial, полужирный, размер – 18 пт, выравнивание – по центру трех ячеек);

в строках 3, 4, 5 – почтовый адрес, телефоны, факс и электронный адрес фирмы (шрифт Arial, обычный, размер – 9 пт, выравнивание – по правому краю ячеек).

5. На каждый продукт отведено три строки: название продукта, краткий комментарий содержания курса и требуемые ресурсы компьютера.

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

7. Убедитесь, что прайс-лист не выходит за пределы формата бумаги А4.

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

Если в колонтитулах (верхнем и/или нижнем) находится текст, например Лист или номер страницы и т.д., то выберите команду Файл – Параметры страницы и в окне диалога Колонтитулы удалите предлагаемый текст или в режиме Предварительного просмотра эту операцию можно сделать через команду 8. Задайте форматы ячеек с ценами:

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

9. Введите числа в ячейки долларовой цены: 40, 80, 60, 90, 90, 35, 35, 35.

10. Присвойте рабочему листу новое название – Прайс-лист.

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

Итак прайс-лист принял привычный вид, но только с ценами в долларах.

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

Цена в рублях это не просто перемножение цены в долларах на текущий курс рубля!!!

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

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

В А2 запишите: Текущий курс рубля;

В А3 запишите: Дополнительный процент;

В В2 введите текущий курс рубля – 6,02;

В В3 введите размер дополнительного процента – 3%.

Для дальнейшей работы удобно ввести "собственные имена" (абсолютный адрес) для переменных.

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

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

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

В ячейке С9 должна быть формула: =В9*Текущий_курс_рубля.

Мышью щелкните на ячейке В9, затем на дополнительной клавиатуре нажмите клавишу умножения – *, затем перейдите на второй лист, щелкните на ячейке В2, нажмите ENTER (если все сделано правильно, то в ячейке С9 будет результат расчета – 240,80).

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

Для этого щелкните мышью в строке формул и добавьте к имеющейся формуле дополнительные множители: *(1+ Дополнительный_процент).

С клавиатуры наберите знак умножения – *, затем круглую скобку и 1 со знаком +, после чего перейдите на второй лист и щелкните на ячейке В3, затем ENTER.

В ячейке С9 формула приняла вид:

=В9*Текущий_курс_рубля*(1+Дополнительный_процент), а в самой ячейке появилась рублевая цена – 248,02.

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

Присвойте второму рабочему листу новое название – Переменные.

Итак, мы практически получили прайс-лист для конечного потребителя.

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

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

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

Для этого нужно создать таблицу цен для дилеров (она будет находиться под основной таблицей).

Добавьте две строки: поместить курсор в строку перед которой будет вставлена новая, в меню выберите Вставка–Строки.

Теперь отступите одну строку и введите название таблицы Цены для дилеров.

Название таблицы: шрифт – полужирный курсив, размер шрифта 14 пт (центрировать на три ячейки).

Сама таблица для дилеров похожа на первую только цены будут в долларах и отличие их в том сколько экземпляров приобретает дилер следовательно и размер скидки будет разный. Поэтому таблица будет иметь три столбца: Наименование товара, Цена продукта: от 5 до 20 и свыше 20, т.к. при заключении контракта с дилерами нужно будет учитывать курс рубля (см. образец приложения к работе).

Шапку таблицы оформите также как и в первой таблице и сделайте обрамление.

Чтобы не набирать снова наименования продуктов, в ячейку А38 введем ссылку на соответствующую ячейку первой таблицы и запишем ее в виде формулы: =А9, т.к. каждый продукт содержит три строки, то номера ячеек будут увеличиваться на 3.

Аналогично введите ссылки на остальные ячейки прайс-листа с наименованием товара.

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

Для оформления ячеек с ценами для дилеров надо внести дополнительные переменные на втором листе. В ячейку А5 введите Скидки для дилеров, в ячейки А6 и А введите текст: от 5 до 20 и свыше 20. Т.к. скидки даются в процентах, поэтому в ячейки В6 и В7 введите соответственно 25% и 50%.

Аналогичным способом создайте два новых имени для ячеек В6 и В7.

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

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

Введем формулу для расчета цены первого продукта с учетом существующих скидок для категории дилеров от 5 до 20.

Примечание: цены для дилеров должны быть меньше на величину скидки, т.е. при цене продукта в 40$, дилер от 5 до 20 получит скидку 25%, что составит 10$, следовательно цена для него получится 40$–10$=30$.

Исходные данные для первого продукта находятся в ячейке В9, поэтому в ячейку В38 введем формулу: =В9*(1-от_5_до_20). После нажатия клавиши ENTER вы увидите цену $ 30,00 (не забудьте задать формат этой ячейке – Финансовый с двумя десятичными знаками и обозначением валюты – $ США).

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

Сделайте то же самое для столбца со скидкой «свыше 20».

Использование функций в Excel. Мастер функций.

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

В ячейку А47 введите текст: Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3% В ячейку А48 введите текст: Текущий курс рубля = 6,02 рублей за 1$ на день 29/03/ Первая фраза отражает принцип расчета рублевых цен в прайс-листе, а вторая содержит информацию для покупателя о текущем курсе рубля на определенный день.

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

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

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

Рассмотрим строку текста: Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3%.

В ней текстовая (неизменяемая) часть – "Цены в рублях рассчитываются по курсу ММВБ на день продажи + ", а вторая (переменная) часть ссылается на ячейку второго листа Дополнительный_процент, потом опять текстовая часть которая содержит знак %.

Начнем редактирование (можно нажать клавишу F2 для редактирования).

Преобразуйте строку текста в формулу, т.е. поставьте перед текстом знак равенства (=) и текстовую часть возьмите в кавычки, затем с клавиатуры наберите знак & перейдите на лист Переменные щелкните на ячейку Дополнительный_процент поставьте знак умножения * и наберите 100, опять наберите знак & поставьте в кавычках знак %.

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

="Цены в рублях рассчитываются по курсу ММВБ на день продажи + " &Дополнительный_процент*100&"%" где & - оператор текстовой функции СЦЕПИТЬ (см. приложение к работе), дополнительный_процент – аргумент со второго листа.

Внешне фраза не изменилась, но она стала формулой, которая "следит" за переменной.

Проверьте, работает ли ваша формула. Перейдите на второй лист и введите дополнительный процент, например 10%. Верните исходный процент 3%.

Аналогично измените в формулу следующую фразу.

Она должна принять следующий вид:

="Текущий курс рубля = "&Текущий_курс_рубля & " рублей за 1 $ на день 29/03/1998" Измените курс доллара, например 31,27. Проверьте работу формулы.

В этой формуле имеется один недостаток – она не изменяет дату. Почему? Потому что у нас нет двух переменных отражающих текущую дату и дату изменения курса.

Теперь введем в эту формулу функцию даты: СЕГОДНЯ():

="Текущий курс рубля = "&Текущий_курс_рубля&" рублей за 1$ на день " &СЕГОДНЯ() Вместо даты у вас, наверное, получилось число. Изменим формулу с помощью Мастера функций. Удалите из формулы функцию СЕГОДНЯ() и после знака & вызовите Мастера функций, нажав на панели инструментов кнопку fx, затем в окне Мастер функций выберите Категорию: – Текстовые, Функция: – Текст (рис. 2).

Функция ТЕКСТ форматирует число и преобразует его в текст. Синтаксис функции: ТЕКСТ(значение;строка_формат). (Более подробно: см. приложение). Окно диалога на следующем шаге Мастера функций имеет вид, показанный на рис. 2.

В поле Значение введите первый аргумент – функцию СЕГОДНЯ().

В поле Строка_формат введите второй аргумент – ДД/ММ/ГГГГ и нажмите ОК.

Формула теперь имеет вид:

="Текущий курс рубля = "&Текущий_курс_рубля & " рублей за 1 $"&" на день " &ТЕКСТ(СЕГОДНЯ();"ДД/ММ/ГГГГ") В прайс-листе будет выведен текст:

Текущий курс рубля = 6,02 рублей за 1$ на день 29/03/ Если все формулы работают, то Вы получили грамотно работающий прайс-лист.

На этом наверное, можно было бы и закончить. НО!

Мы можем при работе с реальным прайс-листом забыть изменить текущий курс рубля.

Добавим перед нашими переменными на втором листе три строки. Выделите три строки и в меню Вставка выберите Строки.

В ячейку А2 введите текст – Текущая дата;

в ячейку А3 – Дата изменения курса;

в ячейку В2 введите формулу: =СЕГОДНЯ().

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

В этом случае формат ячейкам присвоится автоматически.

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

В ячейку С5 введем с помощью Мастера функций логическую функцию ЕСЛИ (информацию о функции вы найдете в приложении к работе). Окно диалога Мастера функций (2-й шаг) показано на рис.3.

В поле Логическое_выражение введите В2< >В3 (без пробелов);

в поле Значение_если_истина – Дата изменилась. Возможно изменился курс рубля;

в поле Значение_если_ложь ничего вводить не будем, поэтому поставьте просто две кавычки. Нажмите ОК.

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

Проверим работу условия. Введите совпадающую дату изменения курса: надпись пропадет. Снова введите вчерашнюю дату. Итак, Excel напоминает автоматически о возможном изменении курса рубля!

Дальнейшая автоматизация прайс-листа возможна при использовании макроса.

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

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

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

Создадим и запишем для автоматизации этой процедуры макрос. Выберите команду Сервис–Макрос–Начать запись. В окне диалога запишем имя макроса: Изменить_дату.

!(Не забудьте знак подчеркивания). В Описание запишем: Изменить дату на текущую.

Нажмите ОК.

Начинаем записывать макрос. В окне программы появилась панель с кнопкой Остановить запись макроса.

Копируем содержимое ячейки В2 – это текущая дата. Так как в ячейку В3 нужно вставить только значение, а не формулу, то в меню выберите команду Правка– Специальная вставка–Значение. Нажмите ОК и затем Esc, чтобы убрать мерцающую рамку.

Теперь запись макроса можно остановить кнопкой Остановить запись.

Создание и графическое оформление макроса в Excel.

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

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

Из контекстного меню выберите Формат надписи, затем в открывшемся диалоговом окне задайте параметры кнопки:

на закладке Цвета и линии цвет заливки – красный, линя – Нет линий;

на закладке Шрифт – полужирный, размер 12 пт, цвет желтый. Нажмите ОК.

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

В завершении из контекстного меню выберите Назначить макрос… и выберите свой макрос Изменить_дату, затем нажмите ОК.

Осталось только связать ячейку А6 на листе Прайс-лист с ячейкой С5 на листе Переменные. Введите формулу в ячейку А6: =Переменные!C5.

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

Итак, работа завершена и Вы получили автоматизированный коммерческий прайс-лист.

Сохраните свой файл под именем Прайс-лист и добавьте свою фамилию.

Дата изменения курса 19.01. Скидки для дилеров:

г.Москва, ул. Энергетическая, д.8, корп.2, 4-й этаж, "МультиМедиа Технологии - отдел торговли" English Gold Курс английского языка для русскоязычного пользователя (500 МВ), 12000 слов, TOEFL) Fra ncais d'Or Курс французского языка для русскоязычного пользователя (600 МВ, 12000 слов, слайд-фильм) EspaNol de Oro Курс испанского языка для русскогоязычного пользователя (W indows 3.1/95, 500 МВ, 12000 слов) English Platinum Углубленный курс английского языка для русскоязычного пользователя (W indows 3.1/95, 2 CD-ROM, 1100 МВ, 15000 слов, видеофильм) Golden Russian Курс русского языка для англоязычного пользователя (W indows 3.1/95, 500 МВ, 12000 слов) W inTeachPro "MS Excel 7.0" Комплексные мультимедийные курсы для самостоятельного изучения MS Excel 7.0 for W indows W inTeachPro "MS W ord 7.0" Комплексные мультимедийные курсы для самостоятельного изучения MS W ord 7.0 for W indows W inTeachPro "MS W indow s 95" Комплексные мультимедийные курсы для самостоятельного изучения операционной системы MS W indows English Gold Fra ncais d'Or EspaNol de Oro English Platinum Golden Russian W inTeachPro "MS Excel 7.0" W inTeachPro "MS W ord 7.0" W inTeachPro "MS W indow s 95" Цены в рублях рассчитываются по курсу ММВБ на день продажи + 3% Текущ ий курс рубля = 6,02 рублей за 1$ на день 19/01/ Применение операторов в формулах Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, а также операторы сравнения и адресные операторы.

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

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Составим таблицу для решения задачи и решим ее средствами Excel.

Будьте внимательны: имена столбцов набираются латинскими буквами!

Ввод данных Введите текст: в ячейку А1 – Целевая функция F;

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

В ячейку B1 введем для целевой функции F формулу, которая будет иметь следующий вид =2*(D1*D2+(D1+D2)*D3) В ячейки B4, B5 и B6 последовательно введем формулу со ссылкой на ячейки D1, D2, D3 (в B4 =D1, в B5 =D2 и в B6 =D3), для дальнейшего ввода ограничений на параметры бака.

В ячейку B7 введите формулу =D1*D2*D3 для расчета объема бака, т.е. ограничения по объему, который должен быть равен 2000.

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

Выберите команду Сервис–Поиск решения. Появится окно диалога «Поиск решения» – рис. 1.

Рис. 1 Окно диалога «Поиск решения». Указатель находится в ячейке В1.

Так как формула для целевой функции находится в ячейке B1, то нужно щелкнуть мышью на этой ячейке. В поле окна диалога «Установить целевую ячейку» появится автоматически абсолютная ссылка $B$1. (Примечание: можно набрать значение абсолютной ссылки с клавиатуры, но щелкнуть мышью проще!) В поле «Изменяя ячейки» укажите ссылку на диапазон ячеек D1:D3.

Установите переключатель «Минимальное значение».

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

В поле Ссылка на ячейку введите B4. В списке неравенств выберите >= – больше или равно. В поле Ограничение введите число 0.

Нажмите кнопку Добавить.

Введите аналогичные ограничения для ячеек B5 и B6.

Введите ограничение для ячейки B7: =2000.

После ввода последнего ограничения нажмите клавишу Enter или кнопку OK.

Окно диалога «Поиск решения» примет следующий вид – рис. 3.

Рис. 3 Окно диалога «Поиск решения» после ввода ограничений Решение задачи.

Теперь все параметры для поиска решения введены и, можно дать команду к выполнению поиска решения. Нажмите кнопку Выполнить.

В окне Результаты поиска решения выведено сообщение «Поиск не может найти подходящего решения», т.к. в ячейках D1, D2, D3 были заданы нулевые начальные значения (рис. 4).

Рис. 4 Окно Результаты поиска решения (при нулевых начальных данных!) При решении задач линейного программирования достаточно часто не удается получить оптимального решения. Это происходит по следующим причинам.

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

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

Измените начальные условия в ячейках D1:D3 на 1. Снова выберите команду Сервис – Поиск решения и нажмите кнопку Выполнить. Теперь в окне поиска решения появилось сообщение: «Решение найдено. Все ограничения и условия оптимальности выполнены». (Примечание: если в эти ячейки ввести отрицательные числа, то согласно заданным ограничениям, решение все равно будет найдено).

Рис. 5 Окно Результаты поиска решения (при ненулевых начальных данных!) Чтобы получить отчет по найденному решению в окне Результаты поиска решения выберите Тип отчета: Результаты, нажав кнопку OK, получите лист с отчетом по результатам поиска решения. Перед тем листом, где записана постановка задачи автоматически будет вставлен лист Отчет по результатам 1. В этом отчете содержится ответ на поставленную задачу.

Искомый параллелепипед оказался кубом со сторонами 12,6.

Переименуйте рабочий лист и дайте ему название Бак.

Задача № 2. План выгодного производства.

Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C".

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

Известно, что реализация 10-ти килограмм конфет "А" дает прибыль 9 р., "В" – 10 р.

и "С" – 16 р.

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

На другом рабочем листе, назовите его Конфеты, создайте таблицу с данными норм расхода сырья как показано на рис. 6. (ячейки B6, C6, D6 имеют формат Денежный) Создайте и введите формулы в таблицы для нахождения решения поставленной задачи, как указано на рис 7.

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

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

Рис. 7. Таблицы с формулами для Поиска решения максимальной прибыли.

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

В меню Сервис активизируйте команду Поиск решения и опишите его ограничения, как показано на рис 8.

Рис. 8. Вид окна Поиск решения с заданными ограничениями.

Если Вы сделали все верно, то решение будет таким, как на рис 9.

Рис. 9. Вид рабочего листа "Конфеты" после нахождения решения.

Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет "В" и 200 кг конфет "С". Конфеты "А" производить не стоит. Полученная Вами прибыль составит 400 р.

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

Задача № 3 Транспортная задача.

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

Нужно перевезти с минимальными затратами весь груз из трех складов в 2 города:

Озеры и Луховицы. В Озеры надо перевезти 45 контейнеров, в Луховицы – 79. На складах имеется 124 контейнера, которые распределены следующим образом:

склад № 1 – 18 контейнеров, склад № 2 – 75 и склад № 3 – 31.

Задание. Присвойте рабочему листу новое имя – Перевозки.

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

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

стоимости перевозки одного контейнера со склада в города.

В таблице № 2 приведено количество контейнеров на каждом складе и их общее количество. А также общее количество потребности в контейнерах по каждому городу.

Создайте Таблицу № 2 в Excel, как показано на рисунке.

В таблице № 3 приведены обозначения для объемов перевозок "Склад–Город".

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

Распределение контейнеров по перевозкам "Склад–Город" (обозначения для объемов перевозок даны с учетом имен ячеек) Количество контейнеров Создайте на этом же рабочем листе Таблицу № 3, пока без внесения количественных данных по складам и по городам, как показано ниже.

Рис. 11. Таблица для нахождения оптимального распределения Составим математическую модель задачи:

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

Она имеет следующий вид и зависит от 6 переменных:

J(j1, j2, j3, j4, j5, j6) = 17*C18 + 12*C19 + 9*C20 + 6*D18 + 13*D19 + 8*D где: числа 17, 12, 9, 6, 13 и 8 являются содержимым ячеек В4:С6 и соответствуют переменным j1, j2, j3, j4, j5, j6 (таблица №1).

Запишем уравнения для ограничений.

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

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

Еще два ограничения совершенно естественны.

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

Задача поставлена, теперь можно приступить к ее решению.

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

=B4*C18+B5*C19+B6*C20+C4*D18+C5*D19+C6*D Введите формулу целевой функции в ячейку D4.

Введите в ячейку В18 формулу для ограничения =C18+D18.

Введите в ячейку В19 формулу для ограничения =C19+D19.

Введите в ячейку В20 формулу для ограничения =C20+D20.

Введите в ячейку В21 формулу для ограничения =B18+B19+B20.

Введите в ячейку С21 формулу для ограничения =C18+C19+C20.

Введите в ячейку D21 формулу для ограничения =D18+D19+D20.

Введите в ячейки С18:D20 начальные значения переменных. В данном случае положим эти значения нулевыми.

(Или выделите весь диапазон этих ячеек, введите с клавиатуры 0, а затем нажмите Ctrl+Enter.) Выравнивание по вертикали и Выравнивание по горизонтали – По центру.

Если все выполнено верно, должен получиться результат как на рис. 12.

Рис. 12. Вид рабочего листа Перевозки с нулевыми значениями Выполните команду Сервис–Поиск решения. Появится окно диалога “Поиск решения”.

В поле ввода Установить целевую ячейку введите ссылку на ячейку D4.

Установите переключатель Минимальное значение.

В поле ввода Изменяя ячейки укажите ссылки на ячейки C18:D20.

Затем необходимо ввести информацию в поле Ограничения.

Нажмите кнопку Добавить. Появится окно диалога “Добавить ограничения”. В поле ввода Ссылка на ячейку введите ссылку на ячейку В18. В поле ввода Ограничение введите = и число 18. Аналогичную операцию проделайте с ячейками В19, В20, С21 и D21.

Для ввода ограничения на целочисленность переменных. В поле ввода Ограничение введите для ячеек C18:D20: цел.

Для ввода ограничения на неотрицательность переменных возможны два варианта:

1. В окне диалога “Добавить ограничения” в поле ввода Ссылка на ячейку введите ссылку на диапазон ячеек C18:D20. В поле ввода Ограничение введите >= и 2. В окне диалога "Поиск решения" нажмите кнопку Параметры и затем в окне "Параметры поиска решения" поставьте флажки у параметров Неотрицательные значения и Линейная модель.

"Параметры поиска решения" и "Поиск решения".

Рис. 13. Заполненное окно "Параметры поиска решения" Рис. 14. Заполненное окно "Поиск решения" по второму варианту.

Нажмите кнопку Выполнить. Результаты решения показаны на рис. 15. Вы можете вывести результаты расчетов на отдельный лист.

Мы решали транспортную задачу при условии минимума целевой функции – стоимости перевозки всего груза. С помощью Excel мы легко можем оценить преимущество расчетов.

Выберите команду Сервис–Поиск решения и установите переключатель Максимальное значение.

Результат показан на рис. 16. Видно что максимальная стоимость перевозок равна 1556 у.е. Таким образом применение методов линейного программирования в Excel дает возможность уменьшить затраты на перевозки максимум на 270 у.е.

Примечание:

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

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

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

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

Рассмотрим на примере рабочей книги (файла) Продажа быттехники такую таблицу на листе Товары все перечисленные цели работы.

Запустите программу Microsoft Eхcel, затем откройте заготовку файла:

С \ Мои документы \ Шаблон для Eхcel \ Продажа быттехники Сохраните файл в свою папку. Назовите его так: Продажа быттехники затем добавьте свою фамилию.

Упражнение 1. Сортировка данных в электронных таблицах.

Проанализируем содержимое ячеек таблицы.

В столбцах A, B, C, D и E внесены наименования товаров, их модели, присвоены коды по порядку, цена поступления и количество каждого товара, в столбцах F, G и H введены формулы для расчета стоимости приобретенного товара, определения цены реализации с 20% наценкой и вычисления прибыли.

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

Поместите курсор на первую ячейку в столбце Наименование товара. Затем на панели инструментов Стандартная нажмите кнопку Сортировка по возрастанию (рис. 1).

Рис. 1. Вид таблицы Товары перед началом сортировки.

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

Рис. 2. Вид таблицы после сортировки по столбцу Наименование товара.

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

Из полученного результата видно, что первую позицию занял товар с нулевой прибылью (рис. 3).

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

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

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

Для этого необходимо вначале вставить пустой столбец перед столбцом Наименование товара.

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

При этом вся таблица сместилась вправо.

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

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

Затем вырезанный столбец нужно вставить на место столбца A (рис. 6).

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

Теперь первым столбцом в таблице стал столбец Код, а столбец D остался пустой.

Удалите столбец D. Для этого выделите весь столбец D и в контекстном меню выберите пункт Удалить (рис. 7).

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

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

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

Выделите все ячейки с данными, исключая Код (диапазон ячеек В2:Н24), затем вызовите диалоговое окно Сортировка в меню Данные (рис. 8).

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

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

а) по подписям первой строки выделенного диапазона ячеек (рис. 8А);

б) по обозначениям столбцов листа (рис. 8Б).

В нашем случае удобнее идентифицировать поля по обозначениям столбцов листа (по названиям столбцов рабочей книги Excel) и выбрать первый критерий сортировки Столбец В, а направление сортировки – по возрастанию. Установите все параметры для сортировки как показано на рис. 8Б. Нажмите кнопку OK.

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

Упражнение 2. Использование фильтров в электронных таблицах.

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

Отобразим в таблице Товары только Холодильники. Выделите весь столбец B с наименованиями товаров, и в меню Данные выберите пункт Фильтр–Автофильтр (рис. 9).

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

Рис. 10. Список значений для определения критерия фильтрации.

Если мы выберем в списке значение Холодильник, то в результате получим следующую таблицу (рис. 11).

Обратите внимание, что кнопка Автофильтра изменила цвет на синий.

Это означает, что фильтр включен.

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

Фильтрация данных может быть и более сложной.

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

В фильтре Наименование товара выберите значение Газовая плита, а в фильтре Цена поступления – значение (Условие...).

Затем в открывшемся окне Пользовательский автофильтр, настройте тип условия для фильтрации цены (например, меньше или равно) и назначьте предельное значение для этого условия – 3 000 (рис. 12).

Рис. 12. Вид окна Пользовательский автофильтр.

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

Рис. 13. Вид таблицы с двумя включенными фильтрами.

Задания для самостоятельного выполнения.

Задание 1. Проведите сложную фильтрацию по каким-либо полям таблицы Товары.

Задание 2. Для фильтрации поля Количество назначьте два условия в Пользовательском автофильтре – первый тип условия – меньше с предельным значением 30 и второй тип условия – больше с предельным Задание 3. Отключите режим фильтрации для выполнения следующего Упражнения.

Снятие режима Автофильтр с таблицы выполняется так же, как и его назначение, повторным выбором в меню Данные пункта Фильтр– Упражнение 3. Подведение промежуточных итогов.

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

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

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

"При каждом изменении в:", "Операция:" и "Добавить итоги по:".

В поле "При каждом изменении в:" – выберите из списка Наименование товара.

В поле "Операция:" – выберите из списка Сумма.

В поле "Добавить итоги по:" – поставьте флажки напротив Количество, Стоимость, Планируемая прибыль.

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

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

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