Системы табличной обработки данных.

Системы табличной обработки данных.

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

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

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

Общие сведения

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

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

Чтобы отредактировать данные в ячейке, необходимо:

¯ дважды щелкнуть в ячейке мышью;

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

¯ отредактировать данные;

¯ выйти из режима редактирования клавишей Enter.

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

Каждая ячейка в Excel может содержать данные одного из трех типов:

§ текст,

§ число,

§ формула.

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

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

2. Ввести данные с клавиатуры.

3. Нажать клавишу Enter или щелкнуть по любой другой клетке.

Тип данных определяется автоматически при вводе: если вводятся только цифры - это число, если нет - это текст.

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

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

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

Выбрать в меню Формат ®Ячейки ®Число

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

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

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

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

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

Задание №1

Нужно создать простую ЭТ, в которой выполняются определенные действия над числами a и b, приведенную на рисунке.

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

Для создания ЭТ выполните следующие действия:

1. Заполните ячейки в строках 2-5. Используйте те же адреса ячеек, которые используются на рисунке..

2. Текст Число а и Число b из ячеек A4 и A5 скопируйте в ячейки С8 и D8.

Копирование содержимого ячейки выполняется так:

Первый способ (при помощи команд основного меню):

§ выберите копируемую ячейку;

§ выберите меню Правка ® Копировать;

§ выбрать новую ячейку;

§ выбрать меню Правка®Вставка.

Второй способ (буксировка с помощью мыши):

§ выберите копируемую ячейку;

§ Подведите указатель мыши к границе ячейки (указатель должен принять вид стрелки), нажмите и удерживайте клавишу Ctrl, одновременно перетаскивая содержимое ячейки на нужное место

3. Заполните все ячейки столбца А.

4. Введите текст в ячейку Е8.

5. Введите формулы в ячейки С10: Е11.

.

 

Ввод формулы в ячейку:

§ Установить курсор на ячейке, в которую должна быть введена формула;

Ввести знак «=» (перед формулой всегда ставиться знак «=». Это означает, что вводится формула, по которой будет производиться расчет.)

§ Щелкнуть мышью по ячейке, в которой содержится первое значение, используемое в формуле

§ Поставить знак арифметической операции

§ Щелкнуть мышью по ячейке, в которой содержится второе значение, используемое в формуле

§ Повторить, если требуется, действия пунктов 4-5.

§ Нажать клавишу [ENTER].

6. Так как в ЭТ все формулы считаются для одних и тех же исходных данных., скопируйте формулу из ячейки С11 в ячейки С12: С17. Для этого кликнете по ячейке С11. В нижнем правом углу есть маркер заполнения - маленький черный квадрат. Чтобы скопировать содержимое ячейки с формулой в соседние ячейки подведите к нему указатель мыши (попав на маркер заполнения, указатель мыши принимает вид черного креста), нажмите левую кнопку мыши и перемещайте мышь в нужном направлении.

7. Скопируйте аналогичным образом формулу из ячейки D11 в ячейки D12: D17.

8. Введите формулы в ячейки Е12:Е15.

9. Так как в формулах в ячейках Е16:Е17 используются функции МАКС (возвращает максимальное число из множества чисел, находящихся в указанном диапазоне/ диапазонах ячеек) и МИН (возвращает минимальное число), для ввода формул в ячейки Е16:Е17 используйте Мастер функций.

10. Выделите ячейку Е16;

11. Выполните команду Вставка / Функци и или щелкнем кнопку на панели инструментов;

12. в открывшемся окне диалога Мастер функций - шаг 1 из 2
выберите щелчком категорию Полный алфавитный перечень, а в списке Функции щелчком функцию МАКС ;
Нажмите ОК

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

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

14. Кликнете ОК. Нажмите Enter для окончания ввода формулы.

15. Выполните аналогичные действия для ввода функции МИН.

16. Отформатируйте ЭТ, создав рамки для диапазонов ячеек.
Для этого:

§ выделите диапазон А2:В5;

§ воспользуйтесь командой Формат -Ячейки - Граница;

§ Выберите по своему усмотрению цвет линии границы, тип линии, и кликнете по одной из кнопок типа границы на панели Все или Отдельные. Посмотрите, как изменился образец в диалоговом окне. Если границы Вас устраивают, кликнете по кнопке ОК.

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

18. Выполните раскраску для диапазона ячеек.
Для этого:

§ выделите диапазон А2:В5;

§ воспользуйтесь командой Формат - Ячейки -Вид;

§ Выберите по своему усмотрению цвет заливки и кликнете по нему дважды. Посмотрите, как изменился образец в диалоговом окне. Если цвет подходит, кликнете по кнопке ОК

Измените исходные числа a и b. Посмотрите как меняется содержимое ячеек в таблице.

 

Счет № ______ от 08.07.01 Поставщик: АО "Связьсервис" Получатель: ООО "ЛЕКС" № Наименование товара Цена Кол-во Ед. Сумма   Телефонный аппарат Euroset 805 55,85   шт. 223,40   Телефонный аппарат Euroset 815 101,00   шт. 303,00                                                 Всего: 526,40 НДС: 105,28 Итого: 631,68   Всего наименований 2, на сумму 631.68 грн. Сумма: Шестьсот тридцать один рубль 68 копеек

Задание для самостоятельной работы.

Создать ЭТ для подготовки счетов фирмы. Формулы для расчетов используются при вычислении сумм, значений Всего, НДС и Итого. В таблице должны присутствовать данные по 5 наименованиям товара (по Вашему выбору).

 

 

 

 

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

Общие сведения.

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

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

 

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

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

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

Таким образом, ссылка на ячейку возможна в 4-х вариантах:

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

2. $А1 (при копировании меняется обозначение строки

3. А$1 (при копировании меняется обозначение столбца)

4. $А$1 (при копировании обозначение столбца и строки не меняются)

 

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

 

Задание №1

Создать ЭТ для подсчета суммы депозитного вклада в банке через n-ое количество лет, для различных значений годовых процентов по вкладу. При этом Вы хотите знать сумму вклада например, через каждый год, а диапазон предлагаемых банками процентов находится в пределах 10% -21%

Исходные данные

Сумма вклада (грн.)

Минимальное количество лет, на которое размещается вклад

Минимальный годовой процент по вкладу (%)

Шаг изменения по годам.

Шаг изменения годовых %

Подсчет реальной суммы (реальной стоимости денег) по прошествию n лет осуществляется по формуле:

 

Р = S*(1+%)^n,

где S - начальная сумма вклада,

% - ставка по вкладу,

n - количество лет

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

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

1. Заполните ячейки В5: D10 исходными данными

2. В ячейки В16 и С16 введите формулы. Для получения абсолютных адресов воспользуйтесь клавишей F4.

3. Используя маркер заполнения, скопируйте формулу из ячейки С16 в диапазон ячеек D16:K16.

4. Введите формулы в ячейки А17, А18

5. Используя маркер заполнения, скопируйте формулу из ячейки А18 в диапазон ячеек А19:А28.

6. Вводите формулу =$B$6 * (1+$A17) ^B$16 в ячейку В17.

7. Используя маркер заполнения, скопируйте формулу из ячейки В17 в диапазон ячеек
С17:К17.

8. Этот диапазон останется выделенным, а в его правом нижнем углу есть маркер заполнения.

9. Подведите к нему указатель мыши и, нажав ее левую кнопку, перетащите его до ячейки К28. Все формулы диапазона В17:K17 скопируются на диапазон. В18:K28.

10. Просмотрите выборочно несколько ячеек диапазона. В17:K28. Объясните, почему формулы имеют именно такой вид.

11. Проанализируйте полученные результаты.

12. Измените исходные данные, и проследите за изменениями в таблице.

13. Отформатируйте таблицу, приготовьте ее для печати.

Задание для самостоятельной работы.

Составьте таблицу всех значений функции y = kx + b, где k и b любые числа, если х изменяется в диапазоне от -10 до 10 с шагом 0,5.

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

Программирование в ЭТ.

Общие сведения.

Разветвляющиеся алгоритмы в ЭТ Excel реализуются с помощью функции ЕСЛИ. Принцип действия функции приведен на рисунке.

Пример: сравнение двух чисел

Условие: Если два числа в ячейках C27=C28 равны, в ячейке, содержащей функцию ЕСЛИ должно появиться слово "равны", если числа не равны - слово "не равны"

 

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

Задание№1

Рассчитать заработную плату сотрудников фирмы, если известны:

 

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

2. норму выработки в штуках

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

 

Начисленная заработная плата зависит от фактической выработки:

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

b) если выработка превышает норму, за изделия,
изготовленные сверх нормы, расценки повышаются на 30%.

 

Задан также срок выплаты зарплаты. Реально выплачиваемая зарплата зависит от срока ее выплаты:

a) если выплата зарплаты произведена не более, чем на 20 дней
после определенного срока, выплачиваемая зарплата равна расчетной;

b) при задержке выплаты свыше 20 дней заработная плата увеличивается
на 0,5% за каждый день (свыше 20 дней) просрочки.

Реальная заработная плата должна быть определена на день выплаты.

 

 

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

1. Текст заголовка (до слов «на 5.11.98») введите в ячейку В1.
По умолчанию ширина столбца составляет 8,43 символа, а текст первой строки заголовка (с учетом пробелов) содержит 47 символов, следовательно, он не сможет быть отображен в одной ячейке. Однако, если ячейки справа от В1 будут пустыми, то весь текст первой строки заголовка будет виден на экране.

2. Вторая строка заголовка состоит из данных разного типа: «на» - текстовое значение и «5.11.98» - дата. Поэтому их следует ввести в разные ячейки таблицы - соответственно в С2 и D2. Если дату вводить в формате ЧЧ.ММ.ГГ (т.е. в данном случае как 05.11.98), то Ехсе1 воспримет значение как дату. В строке формул появится «05.11.98». Но при выполнении вычислений с этой ячейкой, в расчетах будет участвовать целое число, которое обозначает количество дней между базовой датой (1 января 1900 г.) и введенной датой. Это число можно увидеть в ячейке С2, если установить числовой формат данных ячейки, выделить ее и выполнить команду Формат / Ячейки, а затем в открывшемся диалоговом окне Формат ячеек на вкладке Число в списке Числовые форматы щелкнуть Числовой, а в поле Число десятичных знаков щелкнуть «0» (это количество знаков дробной части числа), далее «ОК»..

3. Введите шапку таблицы в ячейки А4:Н4. Так как заголовки некоторых столбцов таблицы состоят из двух или трех строк, укажите режим ввода текста с переносом на следующие строки. Для этого сначала выделим диапазон ячеек А4:Н4 и выполним команду Формат /Ячейки и в открывшемся окне диалога Формат ячеек щелкните вкладку Выравнивание. В поле по горизонтали выберите вид выравнивания - по центру, в поле по вертикали также - по центру установим флажок Переносить по словам, затем ОК. Снимем выделение щелчком вне зоны выделения.

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

5. Аналогичным образом заполните ячейки А6:А15 числами от 1 до 10.

6. Заключите поля шапки таблицы в рамки. Выполните команду Формат /Ячейки и в открывшемся окне диалога Формат /Ячейки щелкните вкладку Границы

7. Введите фамилии 10 рабочих, а в ячейки С6 - С15 выработку (например, если норму выработки считать 100, то числа в этих ячейках должны быть больше и меньше 100).

8. Норму выработки и расценки для простоты считайте одинаковыми для всех рабочих, поэтому их можно ввести только в первые ячейки (Dб и Е6) соответствующих столбцов и затем скопировать в остальные ячейки столбцов.

9. Содержимое ячеек F6- F15 следует вычислять по формуле:

«Расц-ка» * «Выр-ка», если «Выр-ка»<= «Норма»
«Нач-но» = «Расц-ка»* «Выр-ка» + («Выр-ка» - «Норма»)*0,3 * «Расц-ка»,
если «Выр-ка» > «Норма»,

для чего в ячейку F6 надо ввести формулу

=ЕСЛИ(6<=D6;Е6*С6;E6*С6+(С6-D6)*0,З*Е6)
Формулу в ячейку F6 введите с помощью Мастера функци й, для чего:

§ Выделите ячейку F6;

§ Выполните команду Вставка / Функци и или щелкнем кнопку на панели инструментов;

§ в открывшемся окне диалога Мастер функций - шаг 1 из 2
выберите щелчком категорию Логические, а в списке Функции щелчком функцию ЕСЛИ ;
Нажмите ОК

§ В открывшемся окне диалога Мастерфункций

§ в открывшемся окне диалога Мастер функций по очереди активизируйте щелчком мыши поля для ввода аргументов и введите в первое поле – С6<=D6 (чтобы сделать это, уберите диалоговое окно на время выделения этого диапазона, для чего нажмите кнопку свертывания диалогового окна справа от поля с названием Логическое выражение (с косой красной стрелкой), кликнете по ячейке С6, с клавиатуры введите знак операции <= и сновакликнете по ячейке D6. Кнопка разворачивания окна диалога справа от поля ввода формулы позволяет снова вывести на экран окно Мастера функций. Кликнете по ней.
Так же введите во второе поле – Е6*С6,
в третье – E6*С6+(С6-D6)*0,З*Е6;

§ Кликнете ОК,

§ Нажмите Enter для окончания ввода формулы.

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

10. В ячейкуG6 введем формулу =СЕГОДНЯ(), где СЕГОДНЯ - функция, определяющая текущую дату, и скопируем ее в ячейки G7 – G15.

11. Содержимое ячеек Н6 - Н15 следует вычислять по формуле:

«Начислено», если «Тек. дата»-«Срок» <= 20;

«К выпл»=
«Начислено»+(«Тек. дата»-«Срок»-20)*0,005* «Начислено»,

если «Тек. дата»-«Срок»>20,
для чего в ячейку Н6 надо ввести формулу:

=ЕСЛИ(G6-$D$2<=20;F6;F6+(G6-$D$2-20)*0,005*F6),

а в остальные ячейки этого столбца скопировать ее. Здесь срок выплаты зарплаты (содержимое ячейки D2) остается неизменным для всех сотрудников и поэтому при копировании формулы ссылка на эту ячейку. не должна измениться. Поэтому в формуле использована абсолютная ссылка на ячейку D2 (т.е. $D$2). Формулу в ячейку Н6 введите с помощью Мастера функций:

11. В итоговой строке в ячейку В16 введем текст (Итого:), а в столбцах C,F и H подсчитаем сумму значений постолбцам. Для этого используем функцию СУММ, аргументом которой следует взять соответствующий диапазон ячеек. Например, чтобы просуммировать данные столбца С, надо:

¨ выделить диапазон ячеек С6:С15

¨ щелкнуть кнопку Автосуммирование в панели инструментов
Стандартные. В ячейке С16 появится искомая сумма. Аналогично просуммируем столбцы Е и Н.

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

¨ выделим диапазон ячеек F6:F16

¨ выполним команду Формат/Ячейки

¨ в открывшемся окне диалога Формат ячеек щелкнем вкладку Числовой

¨ в списке Числовые форматы щелкнем Числовой

¨ в поле Число десятичных знаков выберем «2»

¨ щелкнем ОК. Аналогично отформатируем столбец Н.

Отформатируйте таблицу, приготовьте ее для печати.

 

 

Лабораторная работа № 4.1.

Общие сведения.

Все виды графического представления числовых данных в Excel носят название диаграммы

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

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

Для построения диаграмм используют команду меню Вставка - Диаграмма или кнопку Мастер диаграмм в панели инструментов Стандартная

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

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

¨ объем начисленной зарплаты (столбец «Начислено»)

¨ объем выплаченной зарплаты (столбец "К выплате»).

Категорией в этом случае будут фамилии рабочих фирмы.

Чтобы построить диаграмму, необходимо выполнить следующее:

1. Выделить на листе данные, которые требуется включить в диаграмму. Кроме числовых данных, относительные значения которых наглядно представляются на диаграмме, необходимо выделить и текстовые данные, описывающие смысловое содержание чисел. Текстовые данные Ехсе1 использует для введения пояснительных надписей в диаграмму. В нашем примере необходимо выделить столбцы В, F и Н, однако без пятой и шестнадцатой (итоговой) строк, поскольку эти строки содержат данные других типов. При выделении следует удерживать нажатой клавишу Ctrl, поскольку выделяемые области не составляют непрерывного диапазона.. При этом Ехсеl выводит рамку «черные муравьи» вокруг выделенных данных диаграммы.

2. Щелкнуть кнопку Мастер диаграмм в панели инструментов Стандартная
На экране появляется окно диалога Мастер диаграмм (ш аг 1 из 4).
Процесс создания диаграммы с помощью Мастера диаграмм состоит из четырех шагов. После выполнения каждого шага вы можете перейти к следующему, нажав кнопку Далее, иливернуться к предыдущему, нажав кнопку Назад, чтобы внести коррективы. Вы можете в любое время отказаться от построения диаграммы, нажав кнопку Отмена. Можно также нажать кнопку Готово, чтобы пропустить оставшиеся шаги и построить диаграмму, основываясь на введенной к данному моменту информации.

 

Окно Мастер диаграмм(шаг 1 из 4) предоставляет набор рисунков с типами диаграмм. В рассматриваемом примере мы хотим создать столбиковую диаграмму (гистограмму) для выбранных данных, поэтому щелкнем тип Гистограмма (в верхнем ряду), затем щелкнем Далее.

В диалоговом окне Мастер диаграмм(шаг 2 из 4) в поле Диапазон следует задать данные, которые нужно отобразить на диаграмме. Если диапазон данных был выбран заранее, то в верхней части окна мастера появится макет диаграммы. В макете вы можете проверить, правильно ли Мастер диаграмм представляет исходные данные. Так как мы выделили данные перед нажатием кнопки Мастер диаграмм, то поле Диапазон уже содержитссылку на диапазоны с исходными данными для диаграммы. Обязательно следует проверить, правильно ли указана ссылка на диапазоны исходных данных. Если ссылки на диапазоны указаны неверно, следует внести изменения, убрав диалоговое окно на время выделения этого диапазона, для чего нажмите кнопку свертывания диалогового окна справа от поля с названием Диапазон (с косой красной стрелкой), укажите диапазон ячеек, которые следует использовать при построении диаграммы. Кнопка разворачивания окна диалога справа от поля позволяет, выделив диапазон, снова вывести на экран окно Мастера диаграмм. (в нашем случае в поле Диапазон должно быть указано: =В6:В15;F6:F15;Н6:Н15).

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

После указания диапазонов щелкнете по вкладке Ряд и выбрав в списке Ряд1 активизируйте поле Имя, затемщелкните по ячейке F4. Значение из ячейки определит имя ряда («Начислено») Аналогично определите имя Р яда2 («К выплате»).
Щелкнуть кнопку Далее.

В третьем окне диалога Мастера диаграмм (шаг 3 из 4): (параметры диаграммы ) внимательно рассмотрите диалоговые окна на всех вкладках Здесь можно отменить ввод текста легенды в диаграмму, добавить название диаграммы, названия для каждой оси и т.д.. Введите название диаграммы: например «Со отношение начислено - к выплате», название оси Х - ФИО и оси У - гривни И снова на образце диаграммы вы увидите результат своих действий еще до закрытия окна диалога. Если вы не отменили легенду, М астер диаграмм помещает ее справа от диаграммы, как показано на образцеокна диалога.

 

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

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

Обратите внимание на то, что метками точек оси Х (т.е. названиями для этих точек) должны быть десять ФИО сотрудников, а в выведенном на экран макете Вы, возможно. видите всего 4 -5 фамилий (первых по списку). Это объясняется тем, что при выбранном (по умолчанию) способе расположения меток, типе и размере шрифта Ехсе1 не может разместить все метки. Ситуацию можно будет исправить в дальнейшем при редактировании диаграммы.Ехсе1 имеет средства для редактирования элементов построенных диаграмм. После того, как диаграмма построена:

щелчок вне поля диаграммы -------- активизация таблицы;

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

При этом на рамке поля диаграммы появляются восемь маркеров.

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

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

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

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

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

 

Задания для самостоятельной работы.

1. Постройте объемную круговую диаграмму для ряда данных «К выплате».

2. Постройте график функции y=kx+b, таблицу значений которой вы получили в лабораторной работе «Абсолютная и относительная адресация ячеек».

Лабораторная работа №4.2.

Общие сведения.

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

В Excel база данных ( или список) – это диапазон ячеек на листе, обладающий следующими свойствами:

§ Первая строка диапазона- это имена полей БД.

§ Строка диапазона – это запись БД.

§ Столбец диапазона – это поле записи (в столбце находятся данные одного типа).

Базы данных (БД)предназначены для хранения больших объемов структурированной информации. Основными операциями при работе с БД являются:

· Организация ввода данных;

· Просмотр данных;

· Сортировка и фильтрация данных в таблицах;

· Подведение итогов.

Задание №1.

Отсортируйте записи по полю «Выработка» в ЭТ из лабораторной работы по начислению зарплаты в порядке убывания и запишите результаты сортировки в Лист3.

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

1. скопируем вначале таблицу в Лист3 (выделим активную часть таблицы (диапазон А1:Н16)

2. выполним команду Правка \Копировать,

3. щелкнем по ярлычку Лист3, активизируем ячейку А1, в которую следует копировать

4. выполним команду Правка / Вставить)

5. удалим итоговую строку и строку с номерами граф таблицы, чтобы они не были включены в сортировку (выделим эти строки и выполним команду Правка / Удалить);

6. активизируем любую ячейку таблицы

7. выполним команду Данные \ Сортировка

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

9. щелкнем ОК.

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

Фильтрация списков с помощью Автофильтра.

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

Можно задать условия в нескольких полях. Они будут работать по принципу логического И.

Задание №2.

Отберем, например, тех рабочих, у кого выработка превышает норму и выплаченная зарплата не превышает 700.

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

1. скопируем таблицу на Лисг4;

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

3. выполним команду Данные I Фильтр I Автофильтр

4. щелкнем кнопку Автофильтра, расположенную рядом с полем «Выработка» и в раскрывшемся списке щелкнем Условие

5. в появившемся окне диалога Пользовательский автофильтр в поле Выработка слева из раскрывающегося списка выберем >, а справа введем значение нормы выработки, например 100;

6. щелкнем ОК;

7. щелкнем кнопку Автофильтра, расположенную рядом с полем «К выплате» и в раскрывшемся списке щелкнем Условие

8. в появившемся окне диалога Пользовательский автофильтр в поле К выплате слеваиз раскрывающегося списка выберем < =, а справа введем 700

9. щелкнем ОК.

Задание №3

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

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

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

1. Исходный диапазон

2. Диапазон условий

3. Диапазон результата

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

Определим диапазон условий.

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

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

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

3. Введите в строки под заголовками столбцов диапазона условий критерии отбора:
а) все записи по ЗАО, названия которых начинаются с буквы И- ЗАО “И*.
б) суммы счетов должны быть <2000.

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

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