Знакомство с программой Excel (стр. 3 )

Использование абсолютных ссылок на ячейки.

В таблице для расчета платежей за электроэнергию повторяется одно и то же число - 0,3. Так как эта величина постоянная, вынесем ее из таблицы.

1. Подготовьте таблицу в соответствии со схемой:

	А	В	С	D	Е
]
2		Тариф	0,3
3
4	Месяц	Предыд.	Текущ.	Сумма
5	Январь	300	450
6	Февраль	450	600
7	Март	600	800
8	Апрель	800	1050

2.  В ячейку D5 запишите формулу =(C5-B5)*C2

3.  Сделайте активной ячейку D5

4.  Поместите указатель мыши на строку формул после С2. Щелкните ЛКМ. Затем нажмите клавишу F4.
Вместо С2 в формуле записано =(С5-В5)*$С$2. Эта ссылка называется абсолютной.

4. Скопируйте исправленную формулу в другие ячейки столбца D.

ЗАДАНИЕ. Как известно, длина окружности рассчитывается по формуле L=2*Pi*R, а площадь круга — S=Pi*R^2. Составьте таблицу для расчета длины окружности и площади круга, используя относительные и абсолютные ссылки на ячейки.

Присвоение имен ячейкам.

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

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

2.  Щелкнуть по полю «Имя».

3.  Ничего не стирая, написать имя ячейки.

4.  Нажать клавишу <Enter>.
Придумывая имя ячейку, необходимо знать:

- максимальное число символов - 255

- пробелы в имени запрещены

- имя не может начинаться с цифры

- все знаки препинания, кроме _ и. запрещены.

ЗАДАНИЕ. Заполните таблицу.

		А	В	С	D	Е
1
2		Тариф	0,3
3
4	Месяц	Предыд.	Текущ.	Сумма
5	Январь	300	450
6	Февраль	450	600
7	Март	600	800
8	Апрель	800	1050

Присвойте ячейке С2 имя «Тариф». В ячейку D5 запишите формулу =(С5-В5)*Тариф и скопируйте ее в другие ячейки.

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

ПОСТРОЕНИЕ ДИАГРАММ И ГРАФИКОВ

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

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

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

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

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

Оформление диаграммы. Третий этап работы мастера (после щелчка на кнопке Далее) состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:

•  Название диаграммы, подписи осей (вкладка Заголовки);

•  Отображение и маркировка осей координат (вкладка Оси);

•  Описание построенных графиков (вкладка Легенда):

•  Отображение сетки линии, параллельных, осях координат (вкладка Линии сетки);

•  Отображение надписей, соответствующих отдельным элементам данных на графике (вкладка подписи данных);

•  Представление данных использованных при построении графика, в виде таблицы (вкладка Таблица данных).

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

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

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

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

Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена (правка - удалить лист), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу <Delete>.

Построение экспериментального графика. (Практическое задание).

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

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

3.  Методом протягивания выделите все заполненные ячейки столбцов А и В.

4.  Щелкните по кнопке «Мастер диаграмм» на панели инструментов или выполните команду Вставка - Диаграмма.

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

6.  В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке <Далее>.

7.  На вкладке Ряд в поле Имя укажите: Результаты измерений. Щелкните на кнопке <Далее>.

8.  Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически
использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке <Далее>.

9.  Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке <Готово>.

10.  Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее и щелкните
на построенной кривой, чтобы выделить ряд данных.

11.  Дайте команду Формат - Выделенный ряд. Откройте вкладку Вид.

12.  На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир.

13. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитре Цвет и Фон выберите зеленый цвет.

14. Щелкните на кнопке <ОК>, снимите выделение с ряда данных и посмотрите, как изменился вид графика.

ЗАДАНИЕ. Постройте график функции у=х3 +1 на отрезке от -5 до 5 .

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

ФУНКЦИЯ – это заданная формула, выполняющая определенный тип вычислений. Чтобы воспользоваться функцией, необходимо представить ей все необходимые для вычисления величины, которые называют аргументами функции. Аргументы можно ввести тремя способами:

как числовое значение. как координаты ячейки. как диапазон ячеек.

Функция начинается со знака равенства. Следом идет имя функции, а затем в скобках имена ячеек, над которыми данная функция выполняется. Например, ПРОИЗВЕД(А1:С4) – поиск произведения ячеек из диапазона А1:С4.

Использование Мастера функций. Количество функций очень велико. И запомнить все очень сложно. Поэтому можно использовать мастер функции. Для этого:

Сделать активной ячейку, в которую необходимо вставить функцию. Щелкнуть по кнопке «Вставка функции» на панели инструментов или дать команду Вставка – Функция. В окне Мастер функции щелкнуть мышью по названию категории (левое поле), а затем – по имени функции (правое поле). Следовать указаниям, появляющимся на экране.

ЗАДАНИЕ. Заполните таблицу.

В ячейке Н1 найдите среднее арифметическое ячеек А1, В1, С1, D1, E1, F1, G1. для этого используете функцию СРЗНАЧ.

Пример. В первом классе учится 30 школьников, во втором – 34, в третьем – 28. За каждой партой сидит два школьника. Сколькими способами учительницы могут рассадить своих учеников?

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

1)  Щелчком мыши выделите ячейку С2.

2)  На панели инструментов щелкните на кнопке «Вставка функции».

3)  В поле Категория выберите «Математические».

4)  В списке функций выберите ЧИСЛКОМБ. Щелкните на кнопке <OK>.

5)  В появившемся окне в поле «Число» будет ссылка на В2, так как число учеников в классе в столбце В.

6)  В поле «Выбранное число» запишите число учеников, сидящих за партой – 2.

7)  Щелкните <OK>.

8)  В результате в ячейке С2 появится число.

9)  Скопируйте формулу из ячейки С2 в С3 и С4.

Логические функции.

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

Пример. Допустим, необходимо вычислить значение выражения Y=2X, если Х<0 и Y=2+X, если Х>0.

Воспользуемся логической функцией ЕСЛИ. Заполним таблицу.

Установите курсор в ячейку В2. Вызовите Мастера функции. В поле Категории выберите Логический. В поле Тип выберите ЕСЛИ. Так как здесь условие X<0 или X>=0, то в первое поле введите значение: A2<0, так как вместо X у нас ячейка A2. В поле «Значение если истина» введите 2*А2. В поле «Значение если ложь» введите 2+А2. Нажмите <OK>. В ячейке В2 появится значение выражения, которое зависит от того, какое значение в ячейке А2. Скопируйте формулу в остальные ячейки.

ЗАДАНИЕ. Вычислите значение Y по формуле:

ПРОСТЕЙШАЯ БАЗА ДАННЫХ.

Разработка и форматирование таблицы.

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

1.  Порядковый номер записи

2.  Табельный номер работника

3.  Фамилия

4.  Имя

5.  Отчество

6.  Дата рождения

7.  Возраст

8.  Почтовый индекс

9.  Город

10.  Адрес

11.  Телефон

12.  Дата поступления на работу

13.  Стаж работы на данном предприятии.

1.  Создайте рабочую книгу «Книга для экспериментов» в своей папке.

2.  Назовите первый лист «Сотрудники».

3.  Первую строку отведите для заголовков столбцов и в каждую ячейку первой строки введите названия столбцов так, чтобы в ячейке А1 было записано «№», а в ячейке М1 – «Стаж работы».

4.  Установите ширину столбцов

столбец	ширина
A	5,86
B	10
C	17,71
D	17,71
E	17,71
F	8,43
G	8,43
H	8,43
I	15
J	20
K	13
L	13
M	8,43

5.  В ячейках столбца «Адрес» установите перенос по словам.

6.  В столбце «Дата рождения» и «Дата поступления на работу» установите формат ячеек «Дата». В столбце «Табельный номер» установите формат «табельный номер». Установите соответствующие форматы для столбцов «Индекс» и «Телефон».

Ввод данных в таблицу.

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

2.  Дайте команду «Данные – Форма». Нажмите <OK>.

3.  На экране появится форма для ввода данных. Переход от одного поля к другому осуществляется с помощью клавиши <TAB>. Заполните форму, оставив пустыми поля «Возраст» и «Стаж». Когда закончите заполнять последнее поле, щелкните на кнопке «Добавить». Поля очистятся и текстовый курсор окажется в первом поле. Введенная информация окажется размещенной по соответствующим ячейкам таблицы.

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

Заполните поля «Возраст» и «Стаж работы». Для этого воспользуйтесь формулой =СЕГОДНЯ()-F2 для вычисления возраста работника. =СЕГОДНЯ()-L2 для вычисления стажа работы на данном предприятии.

Поиск информации. С помощью команды Найти.

Дайте команду Правка – Найти. В поле «Что» введите искомую информацию. В поле со списком «Просматривать» выберите одно из значений: а строках или столбцах. В поле со списком «Область поиска» укажите с помощью мыши, какого рода информацию нужно просматривать: формулы, значения или комментарии к ячейкам. Если прописные и строчные буквы имеют смыслоразделительное значение, то установите флажок «С учетом регистра». Если нужно полное совпадение с содержимым ячейки, то установите флажок «Ячейка целиком». Щелкните на кнопке «Найти далее». Чтобы завершить работу с окном, щелкните на кнопке «Закрыть».

С помощью формы.

Щелкните по любой ячейке, содержащей информацию. Откройте меню Данные и щелкните по строке Форма. В появившейся форме щелкните по кнопке «Критерии». Заполните поля формы условиями поиска. Чем больше полей вы заполните, тем меньше придется просматривать записей. Щелкайте на кнопке «Далее» до тех пор, пока не появится нужная запись. Закройте форму щелчком по кнопке «Закрыть».

С помощью фильтра.

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

Сортировка записей в рабочем листе.

Выделить любую ячейку, содержащую информацию. Открыть меню Данные и щелкнуть по строке Сортировка. В полях со списком «Сортировать по», «Затем по», «В последнюю очередь по» выберите имена столбцов, по которым будет проводиться сортировка. По каждому столбцу укажите, в каком порядке – возрастание или убывание – располагать записи. С помощью переключателей «Идентифицировать поля по» укажите, есть или нет строки заголовка. Щелкните по кнопке <OK>.

Сортировку можно также проводить с помощью кнопок сортировки на панели инструментов.