СОДЕРЖАНИЕ

1.       MS Ехсеl. Ввод и обработка данных. 2

2.       MS Ехсеl. Относительные адреса. 11

3.       MS Ехсеl. Абсолютные адреса. 15

4.       MS Ехсеl. Смешанные адреса. 19

MS Ехсеl. Ввод и обработка данных

Теоретический материал

Требования к вводимым данным

1.                 Каждое данное вводится в отдельную ячейку.

2.                 Ячейки объединяются только в «шапке» и заголовке таблицы.

3.                 Заголовок и названия в «шапке» таблицы выравниваются по центру ячейки: по горизонтали и вертикали.

4.                 Текстовые данные выравниваются по левому краю.

5.                 Числовые данные выравниваются по правому краю.

6.                 Единицы измерения и даты выравниваются по центру ячейки.

7.                 Все таблицы обрамляются.

Автозаполнение

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

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

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

Списки

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

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

После запуска появляется окно «Списки» (см. рисунок 1).

Рисунок 1. Окно редактирования списков

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

Алгоритм 1:

1.     В любой строке или столбце таблицы Ехсеl вести последовательно названия объектов списка.

2.     Выделить эти ячейки и вызвать окно «Списки».

3.     Нажать кнопку Импорт.

4.     Нажать в окне «Списки» кнопку «ОК».

Алгоритм 2:

1.                 В окне «Списки» в поле «Элементы списка» ввести соответствующую информацию, нажимая клавишу Ввод для разделения элементов списка.

2.                 Нажать в окне «Списки» кнопку «Добавить», затем «ОК».

Прогрессия

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

Рисунок 2. Окно редактирования прогрессии

Данное окно в различных версиях Ехсеl вызывается различными способами:

Для ввода данных при помощи режима прогрессии существуют следующие алгоритмы:

Алгоритм 1:

1.     Ввести первое значение последовательности.

2.     Выделить диапазон, где будут располагаться данные.

3.     Открыть окно «Прогрессия».

4.     Выбрать Тип. Для режима Даты  выбрать и  Единицы.

Алгоритм 2:

1.     Ввести первое значение последовательности.

2.     Протянуть маркер автозаполнения ПРАВОЙ клавишей мыши.

3.     Выбрать из появившегося списка – Прогрессия.

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

Диаграммы

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

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

2.                 Выбрать тип диаграммы на вкладке «Вставка».

3.                 Выбрать тип диаграммы и вариант её представления.

Для обработки диаграммы, необходимо выделить её и перейти на вкладку «Конструктор», «Макет» или «Формат», в зависимости от задачи.

Практические задания

Задание 1

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

Дать название листу  – Автозаполнение. Сохранить информацию.

Задание 2

На новом листе отформатировать данные по образцу, выполнив команду Главная - Выравнивание.

Дать название листу  – Форматирование1. Сохранить информацию.

Задание 3

На новом листе отформатировать данные по образцу, выполнив команду Главная - Выравнивание.

Дать название листу – Форматирование2. Сохранить информацию.

Задание 4

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

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

Дать название листу – Посещаемость. Сохранить информацию.

Задание 5

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

Дать название листу – Учёт. Сохранить информацию.

Задание 6

Создать списки планет, знаков гороскопов и зодиаков, используя соответствующие функции Ехсеl.

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

Задание 7

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

1.                 Выделить одновременно несколько блоков для ввода данных. Например, выделить блоки: F19:G24,  I21:J28, L23:M32, F30:H32, удерживая клавишу Ctrl.

2.                 Ввести в строке формул свою фамилию.

3.                 Для заполнения ячеек своей фамилией, удерживая клавишу Ctrl, нажать  клавишу Enter.

Дать название листу. Сохранить информацию.

Задание 8

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

Дать название листу. Сохранить информацию.

Задание 9

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

Построить диаграмму.

Полученную диаграмму скопировать на новый лист и отформатировать её следующим образом:

Дать названия листам. Сохранить информацию.

Задание 10

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

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

Дать название листу. Сохранить информацию.

Задание 11

На следующем листе в одном столбце ввести числа от -3 до 5. В следующем соседнем столбце, напротив ведённых данных, ввести числа произвольной прогрессии.

Построить диаграмму в виде графика. Для этого необходимо выделить все данные и выполнить Вставка - Диаграмма – Точечная – Точечная с гладкими кривыми.

Дать название листу. Сохранить информацию.

Отчёт практической работы

1.     Продемонстрировать выполненные задания.

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

1.     Перечислить правила ввода данных в таблицу.

2.     Как в данной версии Excel вызвать окно «Списки»?

3.     Как на одном листе заполнить одновременно несколько блоков данных одинаковой информацией?

4.     Перечислить этапы построения диаграмм.

MS Ехсеl. Относительные адреса

Теоретический материал

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

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

Автозаполнение расчётов

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

Часто используемые функции

Относительные адреса

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

Если в ячейке после знака «=» щелкнуть левой кнопкой мыши, на какой-то ячейке, то Excel подставляет после «=» относительную ссылку на эту ячейку. Эта ссылка «запоминает», на каком расстоянии (в строках и столбцах) был произведён щелчок ОТНОСИТЕЛЬНО положения ячейки. Например, щелкнув на ячейку 3-мя столбцами левее и на 2 строки выше и после нажатия Enter потянуть вниз за маркер автозаполнения, то эта формула скопируется во все ячейки, через которые протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Это можно проверить, дважды щелкнув на одной из скопированных формул, или выделив ее, и нажав F2.

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

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

Практические задания

Задание 1

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

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

Дать название листу и сохранить информацию.

Задание 2

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

Рисунок 15. Таблица для задания

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

Оформить «шапку» таблицы. Дать название листу. Сохранить информацию.

Задание 3

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

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

Построить гистограмму, отображающую средний балл каждого студента.

Дать название листу и сохранить информацию.

Задание 4

На следующем листе создать таблицу «Анализ расхода материала», представленную ниже.

 В ячейках, в которых выполнен расчёт отклонений (Отклонение = Фактический – По норме), установить вывод отрицательных чисел – красным (Главная – Число).

Дать название листу. Сохранить информацию.

Задание 5

На новом листе  построить график функции вида y=kx + b на отрезке [- 4;4], где k и b могут быть любыми числами. Например, y=7x – 38.

Задание 6

На новом листе построить график функции вида y=kx²+b на отрезке [-20;20], где k и b могут быть любыми числами. Например, y=-3x²+50.

Задание 7

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

Выполнить расчёты и форматирование. Значение скидки рассчитать, введя произвольный процент скидки. Например, = Покупка*15%.

Дать название таблице и листу. Сохранить информацию.

Задание 8

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

Поле «Процент (%) выполнения плана» вычислить по формуле: «Фактически выпущено»/«План выпуска».

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

Дать название таблице и листу. Сохранить информацию.

Отчёт практической работы

1.   Продемонстрировать выполненные задания.

2.   Удалить проверенные работы с данного компьютера.

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

1.   Что такое относительные адреса?

2.   Как произвести автозаполнение расчётов?

3.   Перечислить правила ввода данных в таблицу.

4.   Как установить процентный формат данных?

5.   Как создать список?

MS Ехсеl. Абсолютные адреса

Теоретический материал

Абсолютные адреса

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

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

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1, или дают идентификатор этой ячейке (в контекстном меню выбрать «Присвоить имя…», например а = А1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$».

Практические задания

Задание 1

На новом листе создать таблицу по образцу (см. рисунок 16) и выполнить расчёты по формуле: =В4-$С$2.

Рисунок 16. Возраст

Задание 2

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

Рассчитать стоимость товара с учётом цены, количества и значения НДС, выраженного в процентах.

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

При заполнении значений в столбце с НДС нужно умножать стоимость на значение ставки НДС, указанной в процентах. В качестве значения стоимости в рублях с учетом НДС используется сумма стоимости со значением соответствующей ячейки из столбца НДС.

Дать название листу. Сохранить информацию.

Задание 3

В театре можно купить разные по стоимости билеты: места в ложе стоят - 680 руб., в передних рядах – 530 руб., в задних рядах – 390 руб.

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

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

Задание 4

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

Пеня вычисляются по формуле – 1% от начисленной суммы за каждый задержанный день.

Всего к оплате считается как сумма начисления плюс пеня.

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

Дать название листу. Сохранить информацию.

Задание 5

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

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

Дать название таблице и листу. Сохранить информацию.

Задание 6

На новом листе построить график функции вида y=kx²+b на произвольном отрезке. Значения k и b задать в отдельных ячейках, задав им собственное имя.

Дать название графику и листу. Сохранить информацию.

Задание 7

На следующем листе составить прайс-лист с учётом скидок для семи различных товаров:

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

Дать название таблице и листу. Сохранить информацию.

Отчёт практической работы

1.     Продемонстрировать выполненные задания.

2.     Создать список канцтоваров.

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

1.     Дать определение абсолютного адреса.

2.     Перечислить правила ввода данных в таблицу.

3.     Перечислить этапы построения диаграмм.

4.     Объяснить функцию автозаполнения.

MS Ехсеl. Смешанные адреса

Теоретический материал

Смешанные адреса

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

Для быстрой установки смешанной ссылки  необходимо дважды нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной.

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

Практические задания

Задание 1

На новом листе составить таблицу сложения (для чётных номеров компьютера) или умножения (для нечётных номеров компьютера) чисел первого десятка, представленную на рисунке 19, пользуясь формулой  =$A4+В$3.

Дать название таблице и листу. Сохранить информацию.

Задание 2

На следующем листе подготовить таблицу по образцу.

Заполнить произвольными непоследовательными данными на диапазоне В3:В7. Установить рублёвый формат.

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

Отсортировать прайс-лист по стоимости товара.

Построить диаграмму прайс-листа.

Дать название таблице и листу. Сохранить информацию.

Задание 3

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

Дать название таблице и листу. Сохранить информацию.

Задание 4

Необходимо определить точки пересечения двух графиков. Например, у=х² и у=2х+4. Для этого на следующем листе построить две точечные диаграммы на одной координатной плоскости.

Дать название таблице и листу. Сохранить информацию.

Задание 5

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

Рисунок 21. Отпускная стоимость товара

Для расчета Цены с наценкой использовать формулу  = Цена + Наценка, где Наценка = Цена * Процент%.

Дать название таблице и листу. Сохранить информацию.

Задание 6

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

Рисунок 22. Прайс-лист

Цены за текущий сорт вычислить по формуле = Цена 1-го сорта — № сорта * Коэффициент скидки.

Построить гистограмму, отображающую среднюю цену товара.

Дать название таблице и листу. Сохранить информацию.

Отчёт практической работы

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

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

1.     Перечислить способы установки абсолютного адреса.

2.     Перечислить способы установки смешанного адреса.

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

4.     Перечислить правила оформления таблиц.

5.     Перечислить этапы построения диаграмм.

6.     Объяснить функцию автозаполнения.

Скачано с www.znanio.ru