Создание электронной таблицы и заполнение формулами, обрамление.

Стр 1 из 4Следующая ⇒

Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования «Ижевский государственный технический университет»

Методические указания

к лабораторным работам по дисциплине

«Информатика»

для студентов специальности 261001

«Технология художественной обработки материалов»

на тему «ЭЛЕКТРОННЫЕ ТАБЛИЦЫ Microsoft Excel»

Ижевск, 2008

УДК

Информатика. Методические указания к лабораторным работам для студентов специальности 261001. / Конягина Т.В.; ИжГТУ. – Ижевск, 2008. – 25 с.

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

Содержание

Введение……………………………………………………………………………... Лабораторная работа №1. Основные приемы работы, ввод данных и формул, оформление таблиц………………………………………………………. Лабораторная работа № 2. Создание электронной таблицы и заполнение формулами, обрамление………………………………………………………....... Лабораторная работа № 3. Работа с автозаполнением, составление формул, абсолютные и относительные ссылки на ячейки ……………………………….. Лабораторная работа № 4. Составление условий в электронных таблицах… Лабораторная работа № 5. Работа с датами, объединение текста с данными ячеек, создание собственных форматов…………………………………………... Лабораторная работа № 6. Сортировка данных, фильтры, работа с листамирабочей книги……………………………………………………………………….. Лабораторная работа № 7. Оформление диаграмм…………………………… Литература ………………………………………………………………………….

4 5 8 13 16 19 21 25 32

ВВЕДЕНИЕ

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

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

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

• проведения однотипных расчетов над большими наборами данных;

• автоматизации итоговых вычислений;

• решения задач путем подбора значений параметров, табулирования формул;

• обработки результатов экспериментов;

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

• подготовки табличных документов;

• построения диаграмм и графиков по имеющимся данным.

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

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

Основные приемы работы, ввод данных и формул, оформление таблиц

Краткие теоретические сведения

Основные понятия электронных таблиц

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).

Ячейки и их адресация.На пересечении столбцов и строк образуются ячейки таблицы. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена, например: А1 или DE234. Обозначение ячейки (ее номер) выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках.

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

Ввод, редактирование и форматирование данных

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

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

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

Чтобы завершить ввод, сохранив введенные данные, используют клавишу ENTER. Для очистки текущей ячейки или выделенного диапазона проще всего использовать клавишу DELETE.

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

Задание.

1. Создайте простейшую таблицу под названием «Анализ показателей производства».

2. Автозаполнение(ввод в клетки СЗ и D3 выполняйте методом автозаполнения):

В таблицу должны автоматически вводиться данные:

- СЗ февраль

- D3 март

3. Далее в клетку ЕЗ введите слово «Квартал I».

Краткая теория: автозаполнением вы за секунды можете заполнить:

ряды

- чисел: 100, 200, 300,

- дат: 1-4-06, 2-4-06, 3-4-06;

- месяцев: Ноя, Дек, Янв, .

строки типа:

- Цех 1, Цех 2, ...

- Квартал 1, Квартал 2,

Потренируйтесь с автозаполнением в нижней части таблицы с 30 строки и вниз, используя вышеуказанные примеры.

4. Формулы

Чем больше в таблице формул, тем эффективнее ёе применение.

Автосуммирование:

• переместите курсор в клетку Е4;

• щелкните по кнопке «Автосумма»;

• мерцающим пунктиром будут выделены клетки, которые попадут в формулу суммы, нажмите ENTER, и в клетку Е4 автоматически будет введена формула: =СУММ(В4 : D4);

• повторите эту же процедуру для ввода формулы суммирования в клетку Е5: -СУММ(В5 : D5).

Для ввода любой функции также можно воспользоваться пунктом меню ВСТАВКА>ФУНКЦИЯ

Ввод формул вручную

• поместите курсор в клетку В6;

• введите знак «=», с которого начинается ввод формул.

Далее вы можете использовать два способа набора формул:

а) переключите клавиатуру на «латынь» и введите в строку формулу В5/В4;

б) щелкните мышкой по клетке В5, далее введите знак «/», вновь щелкните мышкой, но теперь по клетке B4 нажмите ЕNTER и в клетку В6 будет введена формула.

В таблице вы видите значение 0,9, рассчитанное по этой формуле.

Копирование формул

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

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

• наведите указатель на маркер заполнения, расположенный в правом нижнем углу рамки текущей ячейки и после того как указатель приобретёт форму тонкого черного крестика, перетащите его по клеткам С6:Е6;

• выделите клетки В6:Е6 и выберите команду копирования выбором пунктов меню ПРАВКА>ЗАПОЛНИТЕ ВПРАВО.

В результате в клетки должны быть введены следующие формулы: С6=С5/С4; D6=D5/D4; E6=E5/E4.

5. Форматирование таблицы

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

Для автоформатирования:

• выделите всю таблицу;

• выберите команду Автоформат в меню Формат;

• в диалоговом окне выберите желаемое форматирование.

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

Для этого необходимо выделить соответствующие ячейки и выбрать пункт меню ФОРМАТ>ЯЧЕЙКИ, либо аналогичный пункт контекстного меню, затем выбрать необходимую вкладку.

Формат чисел в ячейках, количество значащих цифр, направление надписи оформляются с помощью вкладки ФОРМАТ>ЯЧЕЙКИ>ЧИСЛО.

Если необходимо напечатать текст в ячейке в несколько строк, то надо включить опцию ПЕРЕНОСИТЬ ПО СЛОВАМ. Если ячейка широкая, то данные можно располагать вверху, внизу, в середине ячейки.

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

Образец выполнения задания
Показатели производства
Пок./месяцам	Январь	Февраль	Март	Квартал 1
План	1000	2000	3000	6000
Фактически	900	2000	3200	6100
%	90%	100%	107%	102%

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

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

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

Создание электронной таблицы и заполнение формулами, обрамление.

Краткие теоретические сведения

Вычисления в электронных таблицах

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

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

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

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

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

Пусть, например, в ячейке В2 имеется ссылка на ячейку A3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку ЕА27ссылка будет продолжать указывать на ячейку, располагающуюся левее и ниже, в данном случае на ячейку DZ28.

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как А1, $А$1, А$1 и $А1.В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой — как относительный.

Использование стандартных функций

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

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

Использование мастера функций.При выборе пункта Другие функции запускается Мастер функций, облегчающий выбор нужной функции. В списке Категория выбирается категория, к которой относится функция (если определить категорию затруднительно, используют пункт Полный алфавитный перечень), а в списке Функция —конкретная функция данной категории. После щелчка на кнопке ОК имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовый курсор устанавливается между этими скобками.

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

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

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

Задание 1.

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

	март
Зарплата
Премия
Закупка материалов
Амортизационные отчисления
Накладные расходы
Доход от продаж

Суммарный расход	формула

Прибыль	формула

Заполните исходную таблицу таким образом, чтобы в столбце В были соответствующие цифры, а напротив суммарного расхода и прибыли соответствующие формулы:

Суммарн. расход = Зарплата + Премия + Закуп, материалов +

+ Амортиз. отчисл. + Накладные расходы

Прибыль= Доход от продажи - Суммарн. расход.

Задание 2.

1. Составьте электронную таблицу следующего вида:

Количество выпавших осадков.

	2003	2004	2005	2006	итого	макс. кол. за месяц	миним. кол. за месяц
январь
февраль
март
апрель
май
июнь
итого
макс. за год	Соответствующие функции можно найти в меню
миним. за год	ФУНКЦИИ (или ВСТАВКА→ФУНКЦИИ)
среднее за год

Образец выполнения задания

Задание 3.

Составьте электронную таблицу, вычисляющую 1-й член, и сумму арифметической прогрессии по формулам

a_n=a₁ + d(n-1) S_n = (a₁ + a_n)*n/2,

где a — первый член прогрессии, a d — разность арифметической прогрессии.

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

2. В ячейку A3 введите «d», в ячейку В3 — «n», в С3 — «a_n» вD3 — «S_n». Для набора нижних индексов воспользуйтесь командой ФОРМАТ → ЯЧЕЙКИ, выберите вкладку Шрифт и включите нижний индекс. Выровняйте по центру и примените полужирный шрифт.

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

4. Следующий столбик заполните последовательностью чисел от 1 до 10.

5. Введите в ячейку С4 значение первого члена арифметической прогрессии (-2).

6. Выделите ячейку С5 и наберите в ней формулу =С4+А4, для этого, набрав знак равенства, щелкните мышью по ячейке С4, и в строке формул появится ее адрес, затем наберите знак + и щелкните по ячейке А4, зафиксируйте ее нажатием Enter, в ячейке окажется результат вычисления по формуле, а в Строке формул сама формула.

7. Заполните формулой, «протащив» маркер заполнения вниз, ряд ячеек, ниже С5. Ссылки в формуле изменились относительно смещения формулы.

8. Аналогично введите в ячейку D4 формулу =(-2+С4)*B4/2 для подсчета суммы п первых членов арифметической прогрессии и заполните формулами нижние ячейки, протащив вниз маркер заполнения.

9. Выделите все ячейки таблицы, содержащие данные без заголовка «Вычисление n -го члена и суммы арифметической прогрессии», и выполните команду ФОРМАТ → СТОЛБЕЦ → Подгонка ШИРИНЫ.

10. Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки.

11. Выделите четыре ячейки от А1 до D1 и выполните команду ФОРМАТ → ЯЧЕЙКИ → ВЫРАВНИВАНИЕ и установите в положение «Центрировать по выделению» (Горизонтальное выравнивание) и «Переносить по словам». Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.

12. Выделите таблицу (без заголовка) и выполните команду ФОРМАТ → ЯЧЕЙКИ → ГРАНИЦА, определите толстую линию и границу вокруг ячеек.

13. Выделите блок ячеек, относящихся к заголовку: от Al доD2, и, проделав те же операции, установите Контур. В этом случае получается рамка вокруг всех выделенных ячеек, а не каждой.

Образец выполнения задания

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

Как происходит вставка стандартных функций? Опишите работу с Мастером функций.
С помощью каких команд происходит автоподбор ширины столбца?

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

Дата добавления: 2018-05-13; просмотров: 2204; Мы поможем в написании вашей работы!

Поделиться с друзьями:

12 3 4 Следующая ⇒

Мы поможем в написании ваших работ!