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

 

1. Как перейти к первому, последнему листу книги?
2. Как удалить лишние листы книги?
3. Как переименовать лист?
4. Как установить связь между листами?
5. Как отсортировать данные в таблице?
6. Как построить диаграмму по данным таблицы с подписями данных?
7. Как вставить строку в таблицу?
8. Как отфильтровать данные, задав условие?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

                                                  36

Министерство образования и науки РФ Министерство образования Пензенской области

ГБОУ СПО Пензенской области

«Нижнеломовский многопрофильный техникум»

 

 

 

 

 

Методическое пособие для учащихся и студентов

 

Табличный процессор Excel

 

 

 

 

 

 

 

 

 

 

г.Н.Ломов,2012

1

Практическая работа №1

Ввод и редактирование формул

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

Оборудование: ПК, ПО

Содержание работы:

1.      Загрузить ТП Excel. Выполнить последовательность команд ПУСК-ПРОГРАММЫ-Microsoft Excel.

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

3.      Освоим выделение ячеек, строк, столбцов.

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

* Выделите несколько смежных ячеек. Например, установите курсор мыши на ячейку А3, нажмите на левую кнопку мыши и, не отпуская ее, протяните мышь до ячейки Е3, затем отпустите мышь. Диапазон ячеек А3:Е3 выделится черным цветом.

* Выделите несмежные ячейки. Например, нажмите клавишу Ctrl и, удерживая ее, щелкните последовательно по ячейке С5,Е8,В1, диапазон G8:H11, C4, затем отпустите

                                                     

 

                                                   2

 

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

Ссылки на ячейки второго листа рабочей книги.

• Перейдите к Листу 2. Сразу же переименуйте его в «Детские».
• Мы хотим подготовить ведомость, поэтому в ней будут три столбца: «Ф.И.О.», «Сумма», «Подпись»
• В графу «Ф.И.О.» нужно поместить список сотрудников с листа Начисления.
• Выделите ячейку А2 листа Детские и введите формулу: =Начисления!В3, где имя листа определяется восклицательным знаком, а В3- адрес ячейки, в которой размещена первая фамилия сотрудника на листе Начисления.
• Перейдите на лист Детские, проверьте, полученную формулу и распространите ее вниз, воспользовавшись маркером заполнения.
• В графе «Сумма» аналогично нужно разместить формулу =Начисления!Н3*53900, где Н3- адрес ячейки на листе Начисления, содержащей количество детей. Заполните эту формулу вниз и примените денежный формат числа.
• Выполните обрамление таблицы.
• Поместите выше таблицы заголовок ведомости ( для этого понадобиться вставка дополнительных строк).
• Для того чтобы список состоял из сотрудников, имеющих детей, установите фильтр по наличию детей (Данные –Фильтр –Автофильтр), а раскрывающемся

 списке «Сумма» выберите Условие… и установите критерий Больше0).

 

 

                                                              

 

                                                                  35

 

• Запустите Мастер диаграмм одним из способов: выбрав на панели инструментов кнопку Мастер диаграмм либо команду меню Вставка-Диаграмма…
• Передвигаясь по шагам с Мастером диаграмм, выберите тип диаграммы –Круговая, вид- Объемная -Далее. В третьем шаге построения подпишите данные, выбрав пункт Категория и доля, в четвертом шаге укажите Поместить диаграмму на отдельном листе - Готово.
• Перед листом «Начисления» появится новый лист Диаграмма1

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

                              

 

                                                      34                                                                        

 

клавишу мыши и Ctrl.

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

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

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

      4.  Научимся вводить и редактировать формулы.

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

·         Введите в ячейку А1 цифру 1 и нажмите клавишу Enter.Ввод значения в ячейку А1 будет завершен. Темная рамка автоматически перейдет в ячейку А2.

·         Введите в ячейку А2 цифру 3 — Enter. Темная рамка перейдет в ячейку А3.

Запомните правила ввода формул:

 Правило 1. Ввод формулы в ячейку всегда начинается со знака «=».

 Правило 2.  Вводите формулу только латинскими буквами.

                Введите в ячейку А3 формулу «=А1+А2»— Enter.

В ячейке А3 появится результат 5.

      5.   Отредактируем формулу в ячейке А3, заменив знак «+» на « - ». Для этого:

           *   Выделите ячейку А3, нажав клавишу управления курсором Стрелка вверх или щелкнув по ячейке А3 левой кнопкой мыши.

           *   Нажмите клавишу F2. Включится режим редактирования содержимого ячейки.

           *   Нажимая клавишу Стрелка влево, поместите текстовый курсор перед знаком «+», нажмите клавишу Delete. Знак «+» будет удален.

           *   Введите знак « — » (минус) и нажмите клавишу  Enter. В

                                                    

 

                                                          

                                                                   3

 

ячейке А3 появится результат разницы содержимого ячеек А1 и

А2. Таким образом, мы отредактировали формулу в ячейке А3.

      6.    Научимся копировать содержимое ячеек в буфер обмена Windows.

           *  Выделите ячейку А1.

           *  Нажмите на панели Стандартная кнопку Копировать. Вокруг ячейки А1 появится бегущая штриховая рамка, содержимое ячейки скопировано во внутренний буфер обмена.

           *  Нажмите клавишу Стрелка вправо, чтобы выделить ячейку В2.

           *  Нажмите кнопку Вставить на панели инструментов Стандартная. Содержимое буфера обмена будет вставлено в ячейку В2— Enter.

Самостоятельно скопируйте содержимое ячейки А2 в ячейку В2.

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

         а)  Вычислите сумму ячеек А1:В1 (в Excel так обозначаются диапазоны ячеек). Для этого:

         * Выделите диапазон ячеек от А1 до С1.

         * Щелкните мышью по кнопке  Автосумма, которая находится на панели Стандартная. В ячейку С1 автоматически будет вставлена формула «=СУММ(А1:В1), что означает вычисление суммы значений, начиная с ячейки А1 и заканчивая В1.

         б) Вычислите самостоятельно сумму ячеек В1:В2.

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

         * Выделите ячейку А3. Обратите внимание: значение текущей ячейки отображается в строке формул.

         * Нажмите клавишу «=» и щелкните мышью по ячейке А1. В

 

 

 

                                                            4

 

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

Примерный вид формулы: =(C3-E3-75900)*13%, или  =(C3-E3-75900)*13/100, или =(C3-E3-75900)*0,13. После ввода формулы в ячейку F3 ее нужно распространить вниз.

Для подсчета «Суммы к выдаче» примените формулу, позволяющую вычислить разность оклада и налогов. Примерный вид формулы: =C3-D3-E3-F3, размещенной в ячейке G3 и распространенной вниз.

Окончательный вид таблицы:

 

№	Фамилия, имя, отчество	Оклад	Налоги			Сумма   к выдаче	Количество детей
			проф.	пенс.	подох.
1.	Иванов А.Ф.	230000	2300	2300	19734	205 666,00р.	1
2.	Иванова Е.П.	450000	4500	4500	48048	392 952,00р.	2
3.	Китова В.К.	430000	4300	4300	45474	375 926,00р.	0
4.	Котов И.П.	378000	3780	3780	38782	331 658,40р.	0
5.	Круглова А.Д.	230000	2300	2300	19734	205 666,00р.	1
6.	Леонов И.И.	550000	5500	5500	60918	478 082,00р.	2

Поскольку мы в дальнейшем будем работать сразу с несколькими листами, имеет смысл переименовать их ярлычки в соответствии с содержимым. Для этого выполним команду Формат – Лист  - Переименовать… и вместо Лист1 введите название листа, например, Начисления.

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

Построим диаграмму, отражающую начисления каждого сотрудника. Выделить два столбца «Фамилия, имя, отчество» и «Сумма к выдаче», удерживая нажатой клавишу Ctrl.

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

                                                

 

                                                  33

 

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

 

№	Фамилия, имя, отчество	Оклад	Налоги			Сумма   к выдаче	Количество детей
			проф.	пенс.	подох.
1.
2.
3.
4.
5.
6.

Примените следующие операции:

• форматирование строки заголовка;
• изменение ширины столбца;
• обрамление таблицы;
• задание формата числа «денежный»;
• заполнение ячеек столбца последовательностью чисел 1,2,….;
• ввод формулы в верхнюю ячейку столбца;
• распространение формулы вниз по столбцу и в некоторых случаях вправо по ряду;
•  заполнение таблицы текстовой и фиксированной числовой информацией (столбцы «ФИО», «Оклад», «Количество детей»;
• Сортировку строк (сначала отсортировать по фамилиям по алфавиту, затем отсортировать по суммам).

Профсоюзный и пенсионный налоги нужно брать от оклада, т.е. ссылаться только на столбец «Оклад». Примерный вид формулы: =$C3*1%, или  =$C3*0,01, или  =$C3*1/100. После ввода формулы в ячейку D3 ее нужно распространить вниз (протянув за маркер заполнения) и затем- вправо на один столбец.

 

 

                                               

 

                                                    

                                                              32

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

         * Нажмите клавишу «+» и щелкните мышью по ячейке В1. В формулу будет вставлен адрес этой ячейки.

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

      9.  Выйдите из Excel любым из 4-х способов, известных вам из работы Word.

 

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

 

1. Как выделить смежные ячейки? несмежные ячейки?
2. Как ввести формулу в ячейку? Правила ввода.
3. Как отредактировать формулу?
4. Как быстро ввести адреса ячеек при помощи мыши?
5. Как скопировать содержимое ячейки в другую ячейку?
6. Как вычислить сумму данных нескольких ячеек? 2 способа.

 

                                                   

 

 

 

 

 

 

 

 

                                                      

 

 

                                               

 

 

                                                           5

Практическая работа № 2

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

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

Оборудование: ПК, ПО, Excel.

Содержание работы:

1.      Загрузите ТП Excel. Для этого выполните последовательно команды ПУСК - ПРОГРАММЫ- Microsoft Excel.

2.      Изучим основные возможности форматирования таблиц на примере простого бланка-счета за ремонт телевизора.

·         Введите в ячейку А1 строку « Счет за ремонт телевизора»

·         Введите в ячейку А3 символ «№», а в А4—1, в А5—2.

·         Введите в ячейку В3 строку «Наименование работ», в В4—«Замена кинескопа», в В5—«Ремонт антенны», в В6— «Итого:», в В7—«НДС:», в В8—«Спецналог:», в В9—«К оплате:».

·         Введите в ячейку С3 строку «Стоимость работ», в С4—200000, в С5—15000.

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

Обратите внимание, строка «Стоимость работ» закрывает строку «Наименование работ». Исправим это. Но сначала уменьшим ширину столбца А.

            *Установите указатель мыши на границу столбца А так, чтобы указатель мыши изменил вид с белого крестика на

                                                      

                                             

    

                                                     

 

 

                                                        6

Практическая работа №8

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

Цель: Научиться управлять листами рабочей книги

Оборудование: ПК, ПО, Excel

Содержание работы:

• Загрузите ТП Excel.

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

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

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

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

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

Если в книге больше листов , их нужно удалить: вызвать контекстное меню Удалить или воспользовавшись командой горизонтального меню Правка – Удалить лист.

Активен Лист1. На нем будем создавать таблицу.

 

                                     

 

                                                    

 

 

                                                       31

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

 

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

2.      Как вставить ежедневно меняющуюся дату?

3.      Как выполнить перенос по словам?

4.      Как убрать рамку вокруг рисунка?

5.      Как отсортировать список по алфавиту?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

                                                  

                                                           30

 

 

черный.

            * Нажмите и, удерживая левую кнопку мыши, передвиньте границу столбца А влево до символа «№». Это первый способ изменения ширины столбцов.

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

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

            * Выберите команду меню Формат- Столбец - Автоподбор ширины. Ширина столбца изменится так, чтобы в столбцах уместилась самая длинная строка.

4. Выбор ширины и выравнивание текста.

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

·         Щелкните мышью по ячейке А1 и выберите шрифт  Arial и размер шрифта 14.

·         Щелкните мышью на заголовке третьей строки. Выделится вся строка. Назначьте выделенной строке шрифт Times New Roman размером 14 пунктов. При этом строки в ячейках B3 и С3 выйдут за пределы столбцов.

·         Подберите ширину столбцов, перетаскивая границы столбцов в заголовках.

·         Отцентрируйте текст в ячейках строки 3, нажав кнопку По центру.

·         Выделите диапазон ячеек А1:С1 и нажмите кнопку Объединить и поместить в центре на панели инструментов Форматирование. Текст будет выровнен по центру выделенного диапазона.

                                                       7

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

·         Подсчитайте, сколько будет стоить замена кинескопа и ремонт антенны, и результат занесите в ячейку Итого:. Для этого выделите диапазон ячеек С4:С6 и щелкните мышью по кнопке Автосумма на панели Стандартная. В ячейке С6 появится результат вычислений «215000».

·         Введите в ячейку С7 формулу для вычислений НДС «=С6*0,2».

·         Введите в ячейку С8 формулу для вычислений спецналога «=С6*0,015».

·         Выделите диапазон ячеек С6:С9 и щелкните мышью по кнопке Автосумма. В ячейку С9 будет вставлен результат вычислений «261225».

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

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

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

·         Выделите диапазон ячеек С4:С9.

·         Щелкните по выделенному фрагменту правой кнопкой мыши и выберите из контекстного меню команду Формат ячеек.

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

·         Если в поле Обозначение не стоит денежная единица (р.), то установите, выбрав ее из выпадающего списка

                                                   8

 

 

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

Упражнение 2.. Вставка ежедневно меняющейся даты.

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

• выделите ячейку выше таблицы (в случае необходимости вставить дополнительные строки перед таблицей Вставка-Строки;
• выполните команду Вставка-Функция….
• в поле Категория выберите категорию Дата и время, в поле Функция остановитесь на функции Сегодня и через шаг вы получите в выделенной ячейке текущую дату, которая будет изменяться ежедневно.
• Можно разместить в этом документе также рисунок, характеризующий направление торговли. Для этого необходимо выполнить такие же действия, как и в редакторе Word. В меню Вставка выполните команды Рисунок-Картинки и выберите подходящий рисунок. Рисунок, вставленный в Excel, можно перемещать мышью по документу, а также изменять традиционным способом его ширину и высоту. Если хотите убрать рамку вокруг рисунка, то воспользуйтесь пиктограммой Цвет линий панели Рисование. Раскройте пиктограмму и выберите пункт Нет линий (рисунок предварительно должен быть выделен). Можно вставить не рисунок , а логотип предприятия, воспользовавшись для этого возможностями WordArt.
• Можно отсортировать товары по алфавиту.

                                                         29

 

 

кроме «Цена в р.» Столбец «Наименование товара» заполните

текстовыми данными (перечень товаров по вашему усмотрению), а столбец «Эквивалент $US) – числами(цены в долларах).

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

Выделите ячейку, в которую будет вводить курс доллара на сегодняшний день и выполните команду Вставка – Имя - Присвоить… В появившемся диалоговом окне введите имя ячейки и нажмите кнопу ОК. В строке формул в поле «Имя» вместо адреса ячейки разместилось ее имя.

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

                                                           

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

 

                                                 28

                                                      

—ОК.

    Выделенные суммы отобразятся с разделением тысяч и символом «р.» в конце.

      6.Обрамление и фон ячеек.

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

·         Выделите диапазон ячеек А1:С9.

·         Выберите команду меню Формат—Ячейки. В диалоговом окне выберите вкладку Граница.

·         Щелкните мышью на изображении дойной линии в поле Тип линии и затем в поле Все щелкните по кнопке Внешние. В поле Отдельные появится образец обрамления двойной линией—ОК.

·         Выделите диапазон ячеек А3:С3.

·         Щелкните мышью на панели Форматирование по пиктограмме Границы и выберите там шаблон с толстой черной линией снизу. Выделенный диапазон ячеек будет подчеркнут толстой черной линией.

·         Выделите диапазон ячеек В3:В9 и создайте тонкую черную черту справа с помощью пиктограммы Границы.

·         Выделите диапазон ячеек А9:С9.

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

7. Изменение информации в бланке. Посмотрите

                                                      

                                                           9

 

внимательно на свой бланк и представьте, что в связи с изменением цен вам нужно изменить стоимость работ по замене кинескопа с 200000 на 250000. Для этого выделите ячейку С4 и введите новую стоимость 250000.—Enter. Проанализируйте, как изменились остальные значения.

     8. Подготовка документа к печати всегда должна начинаться с установки параметров страниц.

               *  Выберите команду меню Файл—Параметры страницы. Появится диалоговое окно.

              *   Выберите вкладку Страница. Установите опцию Книжная в поле Ориентация.

              *   Откройте список Размер бумаги и выберите строку А4 210297мм.

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

              *    В поле Центрировать на странице установите флажок Горизонтально и сбросьте флажок Вертикально. Теперь наш бланк будет размещен в верхней части листа и выровнен по центру ширины листа.

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

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

         *    Нажмите кнопку Просмотр. На экране появится изображение листа бумаги с нашим бланком. Это—режим

                                              

                                                       10

 

Практическая работа №7

Введение понятия «Имя ячейки»

Цель: Научиться присваивать ячейке имя, производить в ячейке перенос по словам, вставлять ежедневно меняющуюся дату, сортировать список

Оборудование: ПК, ПО, Excel

Содержание работы:

• Загрузить Excel.

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

 

	12.01.05
				Курс доллара		1550
	Наименование товара		Эквивалент $US		Цена в р.
	Кресло рабочее		39		60450
	Стеллаж		35		54250
	Стойка компьютерная		60		93000
	Стол приставной		42		65100
	Стол рабочий		65		100750
	Стул для посетителей		20		31000
	Тумба выкатная		65		100750
	Шкаф офисный		82		127100

 

Подготовьте таблицу, состоящую из столбцов: «Наименование товара», « Эквивалент $US», «Цена в р.» Заполните все столбцы,

 

                                                       27

 

 

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

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

 

Задания для тренировки:

Задание 1.

Составьте таблицу умножения от 1 до 9

 

Таблица умножения
	0	1	2	3	4	5	6	7	8	9
0	0	0	0	0	0	0	0	0	0	0
1	0	1	2	3	4	5	6	7	8	9
2	0	2	4	6	8	10	12	14	16	18
3	0	3	6	9	12	15	18	21	24	27
4	0	4	8	12	16	20	24	28	32	36
5	0	5	10	15	20	25	30	35	40	45
6	0	6	12	18	24	30	36	42	48	54
7	0	7	14	21	28	35	42	49	56	63
8	0	8	16	24	32	40	48	56	64	72
9	0	9	18	27	36	45	54	63	72	81

 

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

1.      Как установить точное значение ширины столбца?

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

3.      Для чего ставится знак $ в позиции адреса ячейки?

4.      Как называются ссылки типа А3, $А3, $А$3?

5.      Какая  клавиша служит для циклического изменения                      типа ссылки? 

 

                                            26

 

 

 

предварительного просмотра. Для выхода из просмотра—     

кнопка Закрыть.

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

 

                                                     

Счет за ремонт телевизора
№  Наименование работ	Стоимость работ
1   Замена кинескопа       2   Ремонт антенны Итого: НДС: Спецналог: К оплате:	250 000р. 15 000р. 265 000р. 53 000р. 3 975р. 321 975р.

 

    9. Выведем  бланк-счет на печать, нажав на панели Стандартная кнопку Печать.

 

 

 

 

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

 

 

1. Как изменить ширину столбца двумя способами?

2. Как вычислить сумму нескольких значений в столбце?

3. Как придать числу денежный стиль?

4. Как обрамить ячейки?

5. Как придать ячейкам фон?

 

                                                   11

 

 

Практическая работа №3

Построение диаграмм

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

Оборудование: ПК, ПО,Excel

Содержание работы:

 

1. Загрузить ТП Excel.

2. Создайте следующую таблицу для функции Y=X^2

 

А	В
х	Y
-10	100
-9	81
-8	64
-7	49
-6	36
-5	25
-4	16
-3	9
-2	4
-1	1
0	0
1	1
2	4
3	9
4	16
5	25
6	36
7	49

•  Хотелось бы распространить эту формулу на остальные ячейки таблицы. Выделите ячейку В3 и заполните, протянув вниз маркер выделения вправо, соседние ячейки. Что произошло? Почему результат не оправдал наших надежд? В ячейке С3 не видно числа, так как оно не помещается целиком в ячейку. Расширьте мышью столбец С. Число появится на экране, но оно явно не соответствует квадрату числа 11. Почему? Дело в том. Что если мы распространили формулу вправо, то автоматически изменил с учетом нашего смещения адрес ячеек, на которые ссылается формула, и в ячейке С3 возводится число, рассчитанное по формуле =В3*10+С3. Во всех предыдущих упражнениях нас вполне устраивали относительные ссылки на ячейки таблицы (при перемещении формулы по такому же закону смещаются и ссылки), однако, здесь возникла необходимость зафиксировать определенные ссылки, т.е. указать, что число десятков можно брать только из столбца А, а число единиц – только из строки 2 ( для того чтобы формулу можно было распространить вниз). В этом случае применяют абсолютные ссылки. Для фиксирования любой позиции адреса ячейки перед ней ставят знак  $. Таким образом, верните ширину столбца С в исходное положение и выполните следующие действия.
• Выделите ячейку В3. Установите текстовый курсор в Строку формул перед ссылкой, которую будете изменять, и исправьте имеющуюся формулу =СТЕПЕНЬ(А3*10+В2;2) на правильную =СТЕПЕНЬ($А3*10+В$2;2), нажимая F4 для циклического изменения ссылки.
• Теперь, воспользовавшись услугами маркера заполнения, можно заполнить этой формулой все свободные ячейки

 

 

                                                  25

 

 

 

 

 

• чтобы заполнить столбец числами от 1 до 9.
• Алогично заполните ячейки В2-К2 числами от 0 до 9.
• После заполнения строчки числами от 0 до 9 вы увидите, сто все необходимые для работы ячейки одновременно не видны на экране. Сузьте их, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой). Для этого выделите столбцы от А до К и выполните команду Формат – Столбец – Ширина…, в поле ввода Ширина столбца введите значение, например 5. В ячейку В3 нужно поместить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце А, и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводится в квадрат в ячейке В3, можно задать формулой =А3*10+B2 (число десятков, умноженное на 10 +число единиц), Остается возвести это число в квадрат.
• Попробуйте воспользоваться Мастером функций. Для этого выделите ячейку, в которой разместится результат вычислений (В3), и выполните команду Вставка – Функция… Из предложенных категорий функций выберите Математическая, Имя функции – Степень, нажмите ОК. В следующем диалоговом окне введите в поле Число основание степени =А3*10+В2 и в поле Степень показатель степени- 2. Это будет проще сделать, если сдвинуть диалоговое окно вниз так, чтобы была видна таблица. Введите адреса ячеек не с клавиатуры, а при помощи мыши, щелкая на соответствующих ячейках А3 и В2. В этом же диалоговом окне можно увидеть значение самого числа (10) и результат вычисления степени (100). Остается только нажать кнопку ОК. В ячейке В3 появится результат вычисления.

                                                         24

 

 

8	64
9	81
10	100

 

3. Постройте диаграмму по данным функции.

Для этого:

• Выделите всю таблицу.
• На панели Стандартная выберите кнопку с изображением диаграммы Мастер диаграмм шагом создайте диаграмму.
• В поле Тип выберите тип диаграммы График. В поле Вид щелкните мышью по Графику, имеющему вид графика. Нажмите кнопку Далее.
• Посмотрите на  образец, и, если он вас устраивает, нажмите кнопку Далее.
• Введите в поле Название диаграммы название «График функции Y=X^2» и нажмите кнопку Далее.
• Чтобы поместить диаграмму на том же листе, где вы создали таблицу, установите флажок напротив пункта На имеющемся, если он там не стоит, и нажмите кнопку Готово.

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

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

                                                   

                                                              13

 

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

7. Сохраните ваш документ на диск А:

     * Вставьте дискету в дисковод А: и нажмите на панели Стандартная кнопку с изображением дискеты Сохранить.

     * В диалоговом окне выберите в списке Папка диск А:

     * В окне Имя файла введите имя файла Диаграмма— Сохранить.

8. Распечатайте ваш файл на принтере.

     * Приведите принтер в состояние готовности.

     * Выполните команды Файл-Печать.

     * Выберите в поле Вывести на печать опцию Всю книгу -ОК

9. Закройте документ. Закройте программу Excel.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

1.      Как создать диаграмму на этом же листе?

2.      Как создать диаграмму на отдельном листе?

 

 

                                       14

Практическая работа №6

Абсолютные ссылки

 

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

Оборудование: ПК, ПО, Excel.

Содержание работы

Загрузите ТП Excel.

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

 

Таблица квадратов
	0	1	2	3	4	5	6	7	8	9
1	100	121	144	169	196	225	256	289	324	361
2	400	441	484	529	576	625	676	729	784	841
3	900	961	1024	1089	1156	1225	1296	1369	1444	1521
4	1600	1681	1764	1849	1936	2025	2116	2209	2304	2401
5	2500	2601	2704	2809	2916	3025	3136	3249	3364	3481
6	3600	3721	3844	3969	4096	4225	4356	4489	4624	4761
7	4900	5041	5184	5329	5476	5625	5776	5929	6084	6241
8	6400	6561	6724	6889	7056	7225	7396	7569	7744	7921
9	8100	8281	8464	8649	8836	9025	9216	9409	9604	9801

 

• В ячейку А3 введите число 1, в ячейку А4 – число 2, выделите обе ячейки и протащите маркер выделения вниз,

                                                          

                                                        23

 

столбец, выравнивание влево.

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

 

Грузоотправитель и адрес
Грузополучатель и адрес
К реестру №                      Дата получения «__»_______200_г.
Счет № 123от 13.08.02
Постоянный торговый дом Пресненский
Адрес: 123456,Москва, ул.Рондельская,4
Дополнения:
	№	Наиме нование	Ед.изме рения	Кол-во	Цена	Сумма
	1.
	2.
	3.
	4.
	5.
	6.
Итого:
Руководитель предприятия                            Чижов Е.Ю.
Главный бухгалтер                                           Стасова А.И.

 

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

1. Как установить авторазбиение на страницы?

2. Как отсортировать данные в таблице?

                                                   22

 

 

Практическая работа № 4

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

 

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

Оборудование: ПК, ПО, ТП Excel

Содержание работы.

 

Упражнение 1. Автозаполнение.

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

 

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

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

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

Попробуйте ввести в ячейку «январь» и, заполнив ряд дальше

                                                     15

 

 

вправо, получить «февраль», «март», а «протянув» маркер заполнения от ячейки «январь» влево, соответственно получить «декабрь», «ноябрь» и т.д.
Упражнение 2. Составьте таблицу для вычисления n-го члена и суммы арифметической прогрессии.

Формула n–го члена арифметической прогрессии
a_n=a₁+d*(n-1)

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

S_n=(a₁+a_n)*n/2
Первый член возьмем a₁=-2

Выполнение упражнения можно производить следующим образом:
    * В ячейку А1 введите заголовок таблицы «Вычисление n–го члена и суммы арифметической прогрессии». Заголовок будет размещен в одну строку и займет несколько ячеек правее А1.

    * Сформатируйте строку заголовка таблицы. В ячейку А2 введите «d», в ячейку В2-«n», в С2- «a_n», в  D2-«S_n».

    *  Для набора нижних индексов воспользуемся командой Формат – Ячейки – выберите вкладку Шрифт –активизируйте переключатель Нижний индекс.

Приступайте к заполнению таблицы.

    * В ячейку А3 введите величину разности арифметической прогрессии d (в нашем примере это 0,725).
    * Далее заполните ряд нижних ячеек  таким же числом. Растиражируйте это значение вниз, используя маркер заполнения.

    * В следующем столбце размещена последовательность чисел от 1 до 10. Воспользуйтесь маркером заполнения и заполните столбец (упражнение 1).

    * В ячейку С3 поместите формулу для вычисления n–го члена арифметической прогрессии a_n и зафиксируйте е нажатием клавиши Enter. Выполните автозаполнение нижних ячеек, «протащив» формулу за маркер заполнения. Сверьте получившееся значение с образцом.

                                                         16

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

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

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

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

Выполните команду Данные – Сортировка…, Выберите столбец, по которому нужно отсортировать данные ( в нашем случае это столбец В, так как именно он содержит перечень товаров, подлежащих сортировке), и установите переключатель в положение «По возрастанию»

3-й этап.

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

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

• Наберите необходимый текст и после таблицы. Следите за выравниванием.
• Обратите внимание, что текст «Дата получения «_»_________200_г.» и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы «Сумма» (самый правый столбец нашей таблички), только применено выравнивание вправо. Текст «Счет №» внесен в ячейку самого левого столбца, и применено выравнивание по центру выделения  (предварительно выделены ячейки одной строки по всей ширине таблицы счета). Вся остальная текстовая информация до и после таблицы внесена в самый левый

                                               

 

                                 21

 

помещаются на странице, а какие нет.

• Создайте таблицу по предлагаемому образцу с таким же числом строк т столбцов
• Выровняйте и отформатируйте шрифт в ячейках-заголовках, подберите ширину столбцов, изменяя их при помощи мыши.
• Введите нумерацию в первом столбце таблицы, воспользовавшись маркером заполнения.
• «Разлинуйте» таблицу, используя линии различной толщины. Обратите внимание на то, что в последней строке 5 соседних ячеек не имеют внутреннего обрамления.
• На этом этапе желательно выполнить команду Файл – Предварительный просмотр, чтобы убедиться,  что таблица целиком вмещается на листе по ширине и все линии обрамления на нужном месте.

2 этап.

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

• Заполните столбцы  «Наименование», «Кол-во», «Цена» по своему усмотрению.
• Установите денежный формат числа в тех ячейках, в которых требуемое число десятичных знаков, если они нужны.

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

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

                                                        

 

                                             

 

                                                               20

                                               

Вычисление n–го члена и суммы арифметической прогрессии
d	n	an	Sn
0, 725	1	-2	-2
0, 725	2	-1,275	-3,275
0, 725	3	-0,55	-3,825
0, 725	4	0,175	-3,65
0, 725	5	0,9	-2,75
0, 725	6	1,625	-1,125
0, 725	7	2,35	1,225
0, 725	8	3,075	4,3
0, 725	9	3,8	8,1
0, 725	10	4,525	12,625

 

* Аналогично введите в ячейку D3 формулу для подсчета суммы n первых членов арифметической прогрессии S_n распространите заполнение на прилегающие ячейки.

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

      * Выделите столбцы А, В, С, D, протянув мышь по заголовкам, и выполните команду Формат – Столбцы - Автоподбор ширины.

Займемся заголовком таблицы.
      * Для заголовка и шапки таблицы выберите полужирное начертание. Шапку таблицы отцентрируйте.

• Заголовок довольно неэстетично « вылезает» вправо за пределы нашей маленькой таблички. Выделите диапазон ячеек А1:D1 и выполните команду Формат- Ячейки –

 

                                                17

 

выберите вкладку Выравнивание –активизируйте

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

Обрамите таблицу при помощи кнопки Границы панели

Форматирование.

 

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

1. Как автоматически пронумеровать список?
2. Как ввести верхний и нижний индекс?
3. Как отцентрировать текст в ячейке таблицы по вертикали?
4. Как задать команду Переносить по словам?
5. Как при помощи меню можно объединить ячейки?

 

 

 

 

 

 

 

 

 

 

 

18

Практическая работа №5

Закрепление основных навыков работы с ЭТ, знакомство с понятием «Сортировка данных»
Цель: Закрепить основные навыки работы с ЭТ, научиться производить сортировку данных

Оборудование: ПК, ПО, Excel

Содержание работы

Загрузите ТП Excel
Упражнение заключается в создании и заполнении бланка товарного счета.
Выполнение упражнения лучше всего разбить на 3 этапа:

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

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

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

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

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

• предварительно установите поля, размер и ориентацию бумаги Файл – Параметры страницы…;
•  выполнив команду Сервис – Параметры… , во вкладке Вид в поле Параметры окна активизируйте переключатель Авторазбиение на страницы.

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

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

 

                                                      

 

                                                           19