ЗАНЯТИЕ 2. Типы данных в ячейках таблицы:

АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ ЯЧЕЕК. ИСПОЛЬЗОВАНИЕ МАРКЕРА АВТОЗАПОЛНЕНИЯ. ФОРМУЛЫ. МАСТЕР ФУНКЦИЙ

Типы данных в ячейках таблицы:

1. Текстовые данные – строка текста, длина которой не более 32000 символов. В качестве первого символа для обозначения текстовых данных иногда используется апостроф – ‘, кавычки – " или пробел. Если MS Excel не может интерпретировать данные в ячейке как число или как формулу, программа считает, что это текстовые данные.

2. Числовые данные – отдельное число, введенное в ячейку. Данные рассматриваются как число, если их формат позволяет это сделать. Как числа рассматриваются данные, определяющие даты или денежные суммы. Ячейки, содержащие числовые данные могут, использоваться в вычислениях.

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

- константы;

- ссылки на ячейки (при выполнении вычислений на место ссылки вставляется числовое значение, находящееся в ячейке, на которую указывает ссылка);

- операторы – знаки арифметических, логических и других операций;

- встроенные функции;

- скобки, закладки и др.

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

Ссылка – указатель на ячейку или группу ячеек. Ссылки делят на:

- абсолютные – указывают на ячейку безотносительно к выбранной ячейке электронной таблицы;

- относительные – указывают на положение ячейки относительно текущей.

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

Для создания абсолютной ссылки нужно поставить знак $ перед той ее частью, которая не должна изменяться. Например: $C$1 – абсолютные строка и столбец; $C1 – абсолютный столбец и относительная строка; C$1 – относительный столбец и абсолютная строка; C1 – относительные столбец и строка.

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

- копировать и размножать содержимое ячеек;

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

- копировать формулы;

- стирать данные в выделенных ячейках;

- удалять и вставлять ячейки.

Копирование и размножение содержимого ячеек с помощью маркера автозаполнения:

- выделить ячейку или копируемый блок;

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

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

Создание рядов с помощью маркера автозаполнения:

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

- выделить заполненные ячейки;

- перетащите маркер автозаполнения в нужном направлении, при этом программа MS Excel сама определит закон, по которому формируется последовательность.

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

Способы ввода функций:

- непосредственный набор функции с клавиатуры в Строку формул;

- с помощью Панели формул;

- с помощью Мастер функций.

Вызов Панели формул осуществляется щелчком по кнопке (Изменить формулу) в Строке формул.

Панель формул имеет следующий вид:

Поле Функция Отмена формулы Ввод формулы

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

Кнопка Отмена формулы осуществляет закрытие Панели формул и возврат к активной ячейке.

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

Способы вызова Мастера функций:

- пункт Функция меню Вставка;

- кнопка Вставка функции на Панели инструментов Стандартная;

- комбинация клавиш Shift + F3.

Задания:

1. Запустите MS Excel.

2. Переименуйте Лист1, дав ему имя Заработная плата.

3. Сохраните таблицу в файле Занятие 2.

4. Введите в ячейки таблицы следующие данные:

A1 – № п/п;

A2 – 1;

A3 – 2;

B1 – Фамилия;

B12 – Итого;

C1 – Оклад;

D1 – Ставка;

E1 – Зарплата;

F1 – Налоги;

G1 – К выдаче;

H1 – Ставка подоходного налога;

I1 – 12%.

5. Заполните столбец А, используя маркер автозаполнения так, чтобы в нем содержалась последовательность чисел от 1 до10.

6. Заполните ячейки B2: B11, введя в них произвольные фамилии.

7. Заполните ячейки C2: D11.

8. Вычислите “Зарплату” как произведение “Оклада” на “Ставку”. Для этого введите в ячейку Е2 формулу =C2*D2.

9. Используя маркер автозаполнения, скопируйте формулу из ячейки E2 в Е3: Е11. Обратите внимание на то, как изменилась формула.

10. Вычислите “Налог”. Для этого введите в ячейку F2 формулу =E2*$I$1.

11. Используя маркер автозаполнения скопируйте формулу из ячейки F2 в F3: F11. Обратите внимание на то, как изменилась формула.

12. Вычислите “К выплате”. Для этого введите в ячейку G2 формулу =E2-F2.

13. Используя маркер автозаполнения, скопируйте формулу из ячейки G2 в G3: G11. Обратите внимание на то, как изменилась формула.

14. Введите в ячейку C12 формулу =СУММ(C2: C11). Для ввода функции используйте Мастер функций.

15. Используя маркер автозаполнения, скопируйте формулу из ячейки C12 в D12: G12. Обратите внимание на то, как изменилась формула.

16. Измените значение ячейки I1 на 20% и проследите за произошедшими в таблице изменениями.

17. Сохраните текущую таблицу и закройте MS Excel.

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

1. Создайте следующую таблицу в файле Маркер:

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

2. Создайте в файле Страна следующую таблицу:

Для разбиения текста в ячейке на строки используйте комбинацию клавиш Alt + Enter.

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

Вычислите сумму в столбце “Площадь”, используя кнопку Поле Функция Панели формул.

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

Для каждой страны вычислите плотность населения и долю (в %) от всего населения Земли.

При вычислении плотности населения всего мира используйте функцию СРЗНАЧ – Среднее значение.

3. Решите задачу. У Сережи было 6 рублей. Он вложил свой капитал в прибыльный бизнес, который приносит ему 20% дохода. Кроме того, каждое утро папа дает Сереже 50 копеек. Мальчик ездит в школу на автобусе, платя по 40 копеек за каждую поездку. Составьте и оформите таблицу доходов Сережи по предложенному образцу:

4. Один стакан лимонада содержит 15 калорий, 1 кусок торта – 150 калорий, 1 драже «Тик-Так» - 2 калории. Во время праздничного обеда Буратино выпил 5 стаканов лимонада, съел 20 драже «Тик-Так» и 4 куска торта. Мальвина съела 2 драже «Тик-Так», 1 кусок торта и выпила 1 стакан лимонада. Пьеро выпил 2 стакана лимонада и съел 2 куска торта. Дуремар съел 3 куска торта и выпил 2 стакана лимонада. Построить электронную таблицу, из которой будет видно, сколько всего стаканов лимонада было выпито, кусков торта и драже «Тик-так» съедено; сколько калорий употребил каждый участник праздничного обеда; сколько калорий содержалось во всем выпитом лимонаде, всех съеденных кусках торта и драже «Тик-так».

5. Решить задачу путем построения электронной таблицы. Исходные данные для заполнения таблицы подберите самостоятельно (не менее 10 строк). При построении таблицы используйте функцию ЕСЛИ.

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

6. Постройте таблицу умножения целого числа N на множители от 1 до 10. Сделайте так, чтобы таблицу можно было перестроить на новое значение N путем изменения содержимого всего одной ячейки.

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

8. В сельскохозяйственном кооперативе работают 5 сезонных рабочих. Норма сбора овощей составляет N кг. Оплата труда производится по количеству собранных овощей: k рублей за 1 кг. Составить таблицу, содержащую сведения о количестве собранных овощей каждым рабочим и об оплате труда каждого рабочего. Известно, что 1-й рабочий собрал овощей в 3 раза больше нормы; 2-й – на 50 кг меньше 1-го; 3-й – в 1, 5 раза больше нормы; 4-й – на 75 кг больше 3-го; 5-й – на 10 кг больше 1-го.

9. В начале года потребление овощей и мяса составляло А кг и В кг соответственно. Ежемесячно потребление овощей увеличивается в среднем в 1, 1 раза, мяса – на 3%. Проследить ежемесячное изменение потребления овощей и мяса в течение полугода.

10. Вычислить размер недельной заработной платы рабочего. Ежедневно он может находиться как на обычном, так и на вредном производстве. Часы работы по дням недели указаны в двух строках для каждого человека. По итогам недели вычисляется число дней и часов, отработанных в обычных и вредных условиях. На их основе определяется оплата труда умножением «часов» на соответствующую «часовую оплату». Кроме того, рабочим начисляется «доплата» за сверхурочный труд и за вредность. «Доплата» за труд в обычных условиях производится только при наличии сверхурочного времени. Разность между фактической длиной рабочей недели и 48 часами оплачивается по тарифу сверхурочных часов (ячейка L1). Доплата за работу на вредном производстве осуществляется аналогично, но только если отработано свыше 20-ти «вредных» часов. Кроме того, в «доплату» входит сумма на покупку молока (ячейка L2) за каждый день, отработанный во вредных условиях. В столбце M формируется сообщение (слово «Отгул»), если отработано свыше 30-ти часов на вредном производстве. В клетке M27 вычислить число всех отгулов за неделю.

Примечания:

1) в ячейке E2 содержится размер почасовой оплаты за работу на обычном производстве;

2) в ячейке E3 содержится размер почасовой оплаты за работу на вредном производстве;

3) формат ячеек E2, E3, L2, J7: L28 – к числовым данным автоматически добавляется «р.»;

4) формат ячейки M11 – к числовым данным добавляется «дн.»;

5) слово «Отгул» должно выводится красным цветом;

6) при вычислении количества отгулов можно использовать функцию СЧЁТЕСЛИ(диапазон; условие).

ЗАНЯТИЕ 3

ДИАГРАММЫ

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

Способы вызова Мастера диаграмм:

- с помощью кнопки Мастер диаграмм на Панели инструментов Стандартная;

- с помощью пункта Диаграмма… меню Вставка.

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

Этапы построения диаграммы с помощью Мастера диаграмм:

1. Первое диалоговое окно Мастера – Шаг 1 из 4 – Тип диаграммы – имеет две вкладки: Стандартные и Нестандартные. На этом этапе нужно выбрать подходящий вариант строящейся диаграммы.

2. Второе диалоговое окно Мастера – Шаг 2 из 4 – Источник данных диаграммы – имеет две вкладки: Диапазон данных и Ряд.

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

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

3. Третье диалоговое окно Мастера – Шаг 3 из 4 – Параметры диаграммы – позволяет определить характер оформления диаграммы, т.е. выполнить ее форматирование. Данное окно содержит следующие вкладки:

- Заголовок – для ввода текста заголовка диаграммы и подписей осей;

- Оси – для определения отображений и маркировки осей координат;

- Линии сетки – для выбора типа линий и характера отображения сетки;

- Легенда – для отображения или скрытия легенды и определения ее места на диаграмме. Легенда – небольшое подокно на диаграмме, в котором отображаются названия рядов данных и образцы их раскраски на диаграмме (в виде ключа легенды);

- Подписи данных – для управления отображения надписей, соответствующих отдельным элементам, данным на диаграмме;

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

4. Четвертое окно Мастера – Шаг 4 из 4 – Размещение диаграммы – служит для определения места размещения диаграммы в рабочей книге.

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

Задания:

1. Откройте файл Страна.xls.

2. Постройте круговую диаграмму по данным Название страны и Население. Для этого нужно:

- выделить диапазон ячеек D2: D12;

- вызвать Мастер диаграмм;

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

- на втором шаге Мастера перейти на вкладку Ряд и определить Подписи категорий, щелкнув по кнопке в правой части данного подокна, выделить диапазон ячеек, содержащий название стран, и восстановить размеры диалогового окна с помощью кнопки ;

- на третьем шаге Мастера на вкладке Заголовки ввести Название диаграммы – Распределение населения;

- на четвертом шаге Мастера выбрать Поместить диаграмму на отдельном листе и ввести имя листа Население.

В результате на листе Население будет находиться диаграмма следующего вида:

3. Измените тип диаграммы на гистограмму. Для этого в контекстном меню диаграммы выберите пункт Тип диаграммы…, в появившемся окне выберите Обычную гистограмму и нажмите .

В результате на листе Население будет находиться диаграмма следующего вида:

4. Перейдите на Лист2 и дайте ему имя Динамика.

5. Составьте таблицу по образцу:

6. Заполните таблицу, выполнив необходимые вычисления.

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

8. Сохраните текущий документ.

9. Выделите в таблице столбцы B, D, F и H.

10. Скройте выделенные столбцы с помощью команды Формат – Столбцы – Скрыть.

11. Скройте строку 3 с помощью команды Скрыть контекстного меню.

12. Выделите диапазон ячеек A2: I6.

13. Создайте диаграмму следующего вида:

14. С помощью пункта Формат оси… контекстного меню Оси значений измените формат чисел так, чтобы на экран выводились целые числа со знаком процента (0%, 10%, …, 100%).

15. Сохраните документ и закройте MS Excel.

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

1. Создайте и оформите следующую таблицу:

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

2. Создайте и оформите следующую таблицу:

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