Лабораторные работы по Excel учебно-методический материал по информатике и икт (9 класс) на тему. Лабораторные работы по excel Лабораторные работы математические функции в excel

31.12.2023 Ноутбук

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

Лабораторная работа по Excel №1.doc

Библиотека
материалов

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

Упражнение 1

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

1. Запустите программу Microsoft Excel , любым, известным вам способом. Внимательно рассмотрите окно программы Microsoft Excel . Первый взгляд на горизонтальное меню и панели инструментов несколько успокаивает, так как многие пункта горизонтального меню и кнопки панелей инструментов совпадают с пунктами меню и кнопками окна редактора Word .

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

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

2. Для того, чтобы ввести текст в одну из ячеек таблицы, необходимо ее выделить и сразу же (не дожидаясь появления столь необходимого нам в процессоре Word текстового курсора) “писать”.

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

Зафиксировать данные молено одним из способов:

нажать клавишу (Enter };

щелкнуть мышью по другой ячейке,

воспользоваться кнопками управления курсором на клавиатуре (перейти к другой ячейке).

Зафиксируйте введенные вами данные.

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

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

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

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

Каков заголовок этого столбца?

Выделите целикам ту строку таблицы, а которой расположено название дня недели-

Какой заголовок имеет эта строка?

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

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

Выделите ячейку D 5; F 2; А16.

Какой адрес имеет ячейка, содержащая день недели?

5. Давайте представим, что в ячейку, содержащую день недели нужно дописать еще и часть суток. Выделите ячейку, содержащую день недели, введите с клавиатуры название текущей части суток, например, "утро" и зафиксируйте данные, нажав клавишу { Enter }.

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

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

Выделите ячейку таблицы, содержанию часть суток, установите текстовый курсор перед текстом в Строке формул и наберите заново день недели. Зафиксируйте данные. У вас должна получиться следующая картина (рис.1.1):

рис.1.1.

вторник, утро

Видно, что запись вышла за пределы своей ячейки и заняла часть соседней. Это происходит только тогда, когда соседняя ячейка пуста. Давайте ее заполним и проверим, что изменится.

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

Теперь видна только та часть ваших данных, которая помещается в ячейке (рис. 1.2). Как просмотреть всю запись? И опять к вам на помощь придет Строка Формул. Именно в ней можно увидеть все содержимое выделенной ячейки.

рис.1. 2.

вторник, ут

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

внести изменения в содержимое выделенной ячейки;

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

6. Как увеличить ширину столбца для того, чтобы в ячейке одновременно были видны и день недели, и часть суток?

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

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

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

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

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

Выделите блок ячеек, начав с ячейки А1 и закончив ячейкой, содержащей "сегодня".

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

Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке.

8. Каким образом удалить содержимое ячейки? Для этого достаточно выделить ячейку (или блок ячеек) и нажать клавишу {Delete } или воспользоваться командой горизонтального меню Правка  Очистить.

Удалите все свои записи.

Упражнение 2

Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних

индексов.

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

Для начала напомним формулу n -го члена арифметической прогрессии:

a n =a 1 +d(n-l)

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

S n =(a 1 + a n )* n /2, где a 1 - первый член прогрессии, a d - разность арифметической прогрессии.

На рис. 1.3 представлена таблица для вычисления n -го члена и суммы арифметической прогрессии, первый член которой равен -2, а разность равна 0,725.

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

Вычисление n -го члена и суммы арифметической прогрессии

Рис. 1.3.

Выполнение упражнения можно разложить по следующим этапам.

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

Сформатируйте строку заголовков таблицы. В ячейку A3 введите "d", в ячейку ВЗ - "n ", в СЗ - "a n ". в D 3 - "S n ".

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

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

Строка-заголовок вашей таблицы оформлена. Можете приступить к заполнению.

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

Далее нужно заполнить ряд нижних ячеек таким же числом. Набирать в каждой ячейке одно и то же число неинтересно и нерационально. В редакторах Paintbrush и Word мы пользовались приемом копировать-вставить. Excel позволяет еще больше упростить процедуру заполнения ячеек одинаковыми данными.

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

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

Заполните таким образом значением разности арифметической прогрессии еще девять ячеек ниже ячейки А4.

В следующем столбце размещена последовательность чисел от 1 до 10.

И опять нам поможет заполнить ряд маркер заполнения. Введите в ячейку В4 число 1, в ячейку В5 число 2, выделите обе эти ячейки и, ухватившись за маркер заполнения, протяните его вниз.

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

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

Можно ввести в ячейку "январь" и, заполнив ряд дальше вправо получить "февраль", "март", а "протянув" маркер заполнения от ячейки "январь" влево, соответственно получить "декабрь", "ноябрь" и т. д. Попробуйте.

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

В третьем столбце размещаются n -е члены прогрессии. Введите в ячейку С4 значение первого члена арифметической прогрессии.

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

Все формулы начинаются со знака равенства.

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

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

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

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

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

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

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

Выделите ячейку С8 и посмотрите в Строке формул, как выглядит формула, она приняла вид =С7+А7. Заметно, что ссылки в формуле изменились относительно смещению самой формулы.

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

Выделите все ячейки таблицы, содержащие данные (не столбцы целиком, а только блок заполненных ячеек без заголовка "Вычисление n -го члена и суммы арифметической прогрессии") и выполните команду Формат  Столбец  Подгон ширины

Рис. 1. 5.

Рис. 1.6 .

Ришла пора заняться заголовком таблицы "Вычисление n -го члена и суммы арифметической прогрессии".

Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки. Заголовок довольно неэстетично "вылезает" вправо за пределы нашей маленькой таблички.

В
ыделите четыре ячейки от А1 до D 1 и выполните команду Формат  Ячейки..., выберите закладку Выравнивание и установите переключатели в положение "Центрировать по выделению" (Горизонтальное выравнивание) и "Переносить по словам" (рис. 1.5). Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.

Таблицу почти привели к виду образца. Если а этот момент выполнить просмотр Файл  Предварительный просмотр, то окажется, что остается выполнить обрамление таблицы.

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

Затем выделите блок ячеек, относящихся к заголовку: от А1 до D 2 и, проделав те же операции, установите переключатель Контур. В этом случае получается рамка вокруг всех выделенных ячеек, а не каждой.

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

Выбранный для просмотра документ Лабораторная работа по Excel №2.doc

Библиотека
материалов

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

Упражнение 1

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

Грузоотправитель и его адрес

Грузополучатель и его адрес

К Реестру № Дата получения «___»___________200__г.

СЧЕТ № 123 от 15.11.2000

Поставщик Торговый Дом Рога и Копыта

Адрес 243100, Клинцы, ул. Пушкина, 23

Р/счет № 45638078 в МММ-банке, МФО 985435

Дополнения:

Наименование

Ед.измерения

Руководитель предприятия Сидоркин А.Ю.

Главный бухгалтер Иванова А.Н.

Упражнение заключаете в создания и заполнении бланка товарного счета.

Выполнение упражнения лучше всего разбить на три этапа:

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

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

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

1-й этап.

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

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

предварительно установите поля, размер и ориентацию бумаги (Файл  Параметры страницы… ),

выполнив команду Сервис  Параметры..., в группе переключателей Параметры окна активизируйте переключатель Авто-разбиение на страницы (рис. 2.1).

Рис. 2.1.

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

Наименование

Ед.измерения

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

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

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

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

Проще всего добиться этого следующим путем:

выделить всю таблицу и установить рамку - "Контур" жирной линией;

затем выделить все строки, кроме последней и установить рамку тонкой линией "Справа", "Слева", "Сверху", "Снизу";

после этого выделить отдельно самую правую ячейку нижней строки и установить для нее рамку "Слева" тонкой линией;

останется выделить первую строку таблицы и установить для нее рамку "Снизу" жирной линией.

Хотя можно действовать и наоборот. Сначала "разлиновать" всю таблицу, а затем снять лишние линии обрамления,

На этом этапе желательно выполнить команду Файл  Предварительный Просмотр, чтобы убедиться, что таблица целиком вмещается на листе по ширине и все линии обрамления на нужном месте.

2-й этап

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

Заполните столбцы "Наименование", "Кол-во" и "Цена" по своему усмотрению.

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

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

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

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

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

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

3-й этап

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

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

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

Рис. 2.3 .

Братите внимание, что текст "Дата получения "__"_______200_г." и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы "Сумма" (самый правый столбец нашей таблички), только применено выравнивание вправо.

Текст "СЧЕТ №" внесен в ячейку самого левого столбца, и применено выравнивание по центру выделения (предварительно выделены ячейки одной строки по всей ширине таблицы счета). Применена рамка для этих ячеек сверху и снизу.

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

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

Упражнение 2

ТАБЛИЦА КВАДРАТОВ

1024

1089

1156

1225

1296

1369

1444

1521

1600

1681

1764

1849

1936

2025

2116

2209

2304

2401

2500

2601

2704

2809

2916

3025

3136

3249

3364

3481

3600

3721

3844

3969

4096

4225

4356

4489

4624

4761

4900

5041

5184

5329

5476

5625

5776

5929

6084

6241

6400

6561

6724

6889

7056

7225

7396

7569

7744

7921

8100

8281

8464

8649

8836

9025

9216

9409

9604

9801

Рис. 2.4

В ячейку A3 введите число 1, в ячейку А4 - число 2, выделите обе ячейки и протащите маркер выделения вниз, чтобы заполнить столбец числами от 1 до 9.

Аналогично заполните ячейки В2 - К2 числами от 0 до 9.

Когда вы заполнили строчку числами от 0 до 9, то все необходимые вам для работы ячейки одновременно не видны на экране. Давайте сузим их, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой).

Для этого выделите столбцы от А до К и выполните команду Формат  Столбец Ширина..., в поле ввода Ширина столбца введите значение, например, 5.

Разумеется, каждому понятно, что в ячейку ВЗ нужно поместить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце А и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке ВЗ можно задать формулой =АЗ*10+В2 (число десятков, умноженное на десять плюс число единиц). Остается возвести это число в квадрат.

Попробуем воспользоваться Мастером функций.

Для этого выделите ячейку, в которой должен разместиться результат вычислений (ВЗ), и выполните команду Вставка  функция...] (рис. 2.5.).

Рис. 2.5.

Следующем диалоговом окне введите число (основание степени) - АЗ*10+В2 и показатель степени - 2. Так же, как и при наборе формулы непосредственно в ячейке электронной таблицы, нет необходимости вводить адрес каждой ячейки, на которую ссылается формула, с клавиатуры. Работая с Мастером функций, достаточно указать мышью на соответствующую ячейку электронной таблицы, и ее адрес появится в поле ввода "Число" диалогового окна. Вам останется ввести только арифметические знаки (*, +) и число 10.

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

Остается только нажать кнопку Закончить.

В ячейке ВЗ появился результат вычислений.

Хотелось бы распространить эту формулу и на остальные ячейки таблицы. Выделите ячейку ВЗ и заполните, протянув маркер выделения вправо, соседние ячейки. Что произошло (рис. 2.6)?

Почему результат не оправдал наших ожиданий? В ячейке СЗ не видно числа, т. к. оно не помещается целиком в ячейку-

Расширьте мышью столбец С. Число появилось на экране, но оно явно не соответствует квадрату числа 11 (рис. 2.7).

Рис. 2.6 Рис. 2.7

Почему? Дело в том, что когда мы распространили формулу вправо. Excel автоматически изменил с учетом нашего смещения адреса ячеек, на которые ссылается формула, и в ячейке СЗ возводится в квадрат не число 11, а число, вычисленное по формуле = ВЗ*10+С2.

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

Для фиксирования любой позиции адреса ячейки перед ней ставят знак $.

Таким образом, верните ширину столбца С в исходное положение и выполните следующие действия-

Выделите ячейку ВЗ и, установив текстовый курсор в Строку формул, исправьте имеющуюся формулу =СТЕПЕНЬ(АЗ*10+В2;2) на правильную =СТЕПЕНЬ($АЗ*10+В$2,2).

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

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

Упражнение 3

Введение понятия "имя ячейки ".

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

Подготовьте таблицу, состоящую из столбцов:

"Наименование товара", "Эквивалент $ US ", "Цена в р.". Заполните все столбцы, креме "Цена в р." Столбец "Наименование товара” заполните текстовыми данными (перечень товаров по вашему усмотрению), а столбец "Эквивалент $ US " числами (цены в долл.).

Понятно, что а столбце "Цена в р." должна разместиться формула: "Эквивалент $ US "*Kypc доллара".

Почему неудобно в этой формуле умножать на конкретное значение курса? Да потому, что при каждом изменении курса, вам придется менять свою формулу в каждой ячейке.

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

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

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

Примечание: Имя может иметь в длину до 255 символов и содержать буквы, цифры, подчерки (_), символы: обратная косая черта (\), точки и вопросительные знаки. Однако первый символ должен быть буквой, подчерком (_) или символом обратная косая черта (\). Не допускаются имена, которые воспринимаются как числа или ссылки на ячейки.

Рис. 2.8.

В появившемся диалоговом окне вам остается только ввести имя ячейки (ее точный адрес уже приведен в поле ввода "Ссылается на") и нажать кнопку ОК. Обратите внимание на то, что в Поле имени, вместо адреса ячейки, теперь размещено ее имя.

В ячейку, расположенную левее ячейки "Курс_доллара", можно ввести текст "Курс доллара".

Теперь остается ввести формулу для подсчета цены в рублях.

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

Заполните формулу вниз, воспользовавшись услугами маркера заполнения.

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

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

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

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

Выбранный для просмотра документ Лабораторная работа по Excel №3.doc

Библиотека
материалов

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

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

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

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

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

Заполнение таблицы данными традиционным способом и с применением формы;

Подбор данных по определенному признаку.

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

Введите заголовки таблицы в соответствии с предложенным образцом. Учтите, что заголовок располагается в двух строках таблицы: в верхней строке "Приход", "Расход", "Остаток", а строкой ниже остальные пункты заголовка.

Приход

Расход

Остаток

Отдел

Наименование товара

Единица измерения

Цена прихода

Кол-во прихода

Цена расхода

Кол-во расхода

Кол-во остатка

Сумма остатка

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

Так, для форматирования ячеек их достаточно выделить, щелкнуть правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения и выбрать команду Формат  Ячеек... , вы перейдете к тому же диалоговому окну Формат ячеек (рис. 3.1). Да и редактировать содержимое ячейки (исправлять, изменять данные) совсем не обязательно в Строке формул. Если дважды щелкнуть мышью по ячейке, в ней появится текстовый курсор, и можно произвести все необходимые исправления.

Заполнение таблицы

Определитесь, каким видом товаров вы собираетесь торговать и какие отделы будут в вашем магазине.

Вносите данные в таблицу не по отделам, а вперемешку (в порядке поступления товаров).

Заполните все ячейки, кроме тех, которые содержат формулы ("Остаток").

Обязательно оставьте последнюю строку таблицы пустой (но.эта строка должна содержать все формулы и нумерацию).

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

Приход

Расход

Остаток

Отдел

Наименование товара

Единица измерения

Цена прихода

Кол-во прихода

Цена расхода

Кол-во расхода

Кол-во остатка

Сумма остатка

Кондитерский

Зефир в шоколаде

упак.

20 р.

25р.

0 р.

Молочный

Сыр

кг.

65 р.

85 р.

170 р.

Мясной

Колбаса Московская

кг.

110 р.

120р.

600 р.

Мясной

Балык

кг.

120 р.

140 р.

700 р.

Вино-водочный

Водка «Абсолют»

бут. 2 л.

400 р.

450 р.

0 р.

Вычисляемые поля (в которых размещены формулы) выводятся на экран без окон редактирования ("Кол-во Остатка" и "Сумма Остатка").

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

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

Дойдя до последней записи (мы специально оставили ее пустой, но распространили на нее формулы и нумерацию), заполните ее новыми данными.

Перемещаться между окнами редактирования (в которые вносятся данные) удобно клавишей (Tab }.

Когда заполните всю запись, нажмите клавишу {Enter }, и вы автоматически перейдете к новой чистой карточке-записи

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

Заполните несколько новых записей и затем нажмите кнопку Закрыть.

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

Оперирование данными

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

Выделите таблицу без заголовка и выберите команду Данные-  Сортировка... (Рис. 3.3).

Выберите первый ключ сортировки: в раскрывающемся списке "Сортировать" выберите "Отдел" 5 и установите переключатель в положение "По возрастанию" (все отделы в таблице расположатся по алфавиту).

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

Продолжим знакомство с возможностями баз данных Excel .

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

Выделите таблицу со второй строкой заголовка (как перед созданием формы данных).

Выберите команду меню Данные  Фильтр...  Автофильтр.

Снимите выделение с таблицы.

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

Раскройте список ячейки "Кол-во Остатка", выберите команду Настройка... и, в появившемся диалоговом окне установите соответствующие параметры (>0).

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

Фильтр можно усилить. Если дополнительно выбрать какой-нибудь конкретный отдел, то можно получить список непроданных товаров по отделу.

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

Но и это еще не все возможности баз данных Excel . Разумеется ежедневно нет необходимости распечатывать все сведения о непроданных товарах, нас интересует только "Отдел", "Наименование" и "Кол-во Остатка".

Можно временно скрыть остальные столбцы. Для этого выделите столбец №, вызовите контекстное меню (правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения) и выберите команду Скрыть.

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

Вместо команды контекстного меню можно воспользоваться командой горизонтального меню Формат  Столбец  Скрыть.

Чтобы не запутаться в своих распечатках вставьте дату, которая автоматически будет изменяться в соответствии с установленным на вашем компьютере временем Вставка  Функция..., имя функции - "Сегодня").

Теперь уже точно можно распечатать и иметь подшивку ежедневных сведений о наличии товара.

Как вернуть скрытые столбцы? Проще всего выделить таблицу Формат  Столбец  Показать.

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

Прежде чем напечатать любой документ, выполните просмотр (Файл  Предварительный просмотр или воспользуйтесь кнопкой Предварительный просмотр панели инструментов). Вам может не понравиться несколько моментов:

В верхней части листа появилась запись "Лист I". Нужно ее удалить.

Страница...;

Колонтитулы ;

в поле выбора Верхние колонтитулы установите Нет

В нижней части листа появилась запись "СТР. I". Нужно ее удалить.

Находясь в режиме просмотра, выберите кнопку Страница...;

в появившемся диалоговом окне выберите вкладку Колонтитулы ;

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

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

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

Таблица не помещается по ширине на странице, хотелось бы уменьшить левое и правое поля.

1. Находясь в режиме просмотра, выберите кнопку Страница..., в появившемся диалоговом окне вкладку Поля и установите желаемые поля.

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

Размер полей уменьшен, а таблица так и не помещается по ширине на странице. Хотелось бы изменить ориентацию листа.

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

Диалоговое окно <Параметры страницы> можно вызвать, находясь в режиме таблицы (не выходя в режим просмотра), выполнив команду Файл  Параметры страницы....

Выбранный для просмотра документ Лабораторная работа по Excel №4.doc

Библиотека
материалов

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

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

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

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

По умолчанию рабочая книга открывается с 16-ю рабочими листами, имена которых Лист1, ..., Лист16. Имена листов выведены на ярлычках в нижней части окна рабочей книги.

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

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

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

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

на первом разместим сведения о начислениях,

на втором - диаграмму, .

на третьем - ведомость на выдачу заработной платы,

а на четвертом - ведомость на выдачу компенсаций на детей.

Остальные листы будут только мешать, поэтому их лучше удалить.

Выделите листы с 5 по 16. Для этого щелкните мышью по ярлычку листа 5, затем, воспользовавшись кнопкой перейдите к ярлычку листа 16 и, удерживая клавишу (Shift }, щелкните по нему мышью. Ярлычки листов с 5 по 16 выделятся цветом.

Удалите выделенные листы, вызвав команду контекстного меню Удалить или воспользовавшись командой горизонтального меню Правка  Удалить лист.

Теперь выглядывают ярлычки только четырех листов.

Активен (ярлычок выделен цветом) Лист 1. Именно на нем мы и начнем создавать таблицу.

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

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

запуск Excel ;

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

изменение ширины столбца (в зависимости от объема вводимой информации);

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

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

заполнение ячеек столбца последовательностью чисел 1, 2, ...;

ввод формулы в верхнюю ячейку столбца;

распространение формулы вниз по столбцу и в некоторых случаях вправо по ряду;

заполнение таблицы текстовой и фиксированной числовой информацией (столбцы "ФИО", "Оклад", "Число детей");

сортировка строк (сначала отсортировать по фамилиям по алфавиту, затем отсортировать по суммам).

Фамилия, имя отчество

Оклад

Налоги

Сумма к выдаче

Число детей

профс.

пенс.

подох.

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

=$СЗ*1 % или =$СЗ*0,01 или =$СЗ*1/100. После ввода формулы в ячейку D 3 ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец.

Подоходный налог подсчитаем по формуле: 12% от Оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(СЗ-ЕЗ-86)*12% или =(СЗ-ЕЗ-86)*12/100 или =(СЗ-ЕЗ-86)*0,12. После ввода формулы в ячейку F 3, ее нужно распространить вниз.

Для подсчета Суммы к выдаче примените формулу, вычисляющую разность оклада и налогов. Примерный вид формулы: ==СЗ-D 3-E 3-F 3, размещенной в ячейке G 3 и распространенной вниз.

Заполняйте столбцы "Фамилия, имя, отчество", "Оклад", и "Число детей" после того, как введены все формулы. Результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполнения таблицы.

После ввода всех данных желательно выполнить их сортировку (не забудьте перед сортировкой выделить все строки от фамилий до сведений о детях).

В окончательном виде таблица будет соответствовать образцу:

Фамилия, имя отчество

Оклад

Налоги

Сумма к выдаче

Число

профс.

подох.

Иванов А-Ф.

230000

2300

18216

207184

Иванова Е.П.

450 000

4500

44352

396 648

Китов а В. К

430 000

4300

41 976

379 424

Котов И.П

378000

3780

35 798

334642

Кругло ва АД

230000

2300

18 216

207184

Леонов И И

560 000

560D

5600

57 420

491 380

Петров М.В.

348 000

3490

32353

309667

Сидоров И.В.

450000

4500

44352

396 648

Симонов К.Е

349 000

3490

32 353

309667

Храмов А.К

430 000

4300

41 Э76

379 424

Чудов АН,

673 000

6730

70844

588 696

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

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

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

Построим диаграмму, отражающую начисления каждого сотрудника. Понятно, что требуется выделить два столбца таблицы: "Фамилия, имя, отчество" и "Сумма к выдаче". Но эти столбцы не расположены рядом, и традиционным способом мы не сможем их выделить. Для Excel это не проблема.

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

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

Запустите Мастер диаграмм одним из способов: либо выбрав кнопку Мастер диаграмм панели инструментов, либо команду меню Вставка  Диаграмма….

Передвигаясь по шагам с Мастером диаграмм, выберите тип диаграммы - объемная круговая, подтип седьмой (с метками данных). Приблизительный вид приведен на рисунке.

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

Перейдите к Листу 3. Сразу же переименуйте его в "Детские".

ФИО

Сумма

Подпись

Иванов А.Ф.

53 130

Иванова Е.П.

106260

Кругло ва А.Д.

53130

Леонов И.И.

159390

Петров М.В.

53 130

Сидоров И.В.

53 130

Чудов А.Н.

106260

Мы хотим подготовить ведомость, поэтому в ней будут три столбца: "ФИО", "Сумма" и "Подпись". Сформатируйте заголовки таблицы.

В графу "ФИО" нужно поместить список сотрудников, который мы имеем на листе "Начисления". Можно скопировать на одном листе и вставить на другой, но хотелось бы установить связь между листами (как это выполняется для диаграммы и листа начислений). Для этого на листе "Детские" поместим формулу, по которой данные будут вставляться из листа "Начисления".

Выделите ячейку А2 листа "Детские" и введите формулу: =Начисления!ВЗ, где имя листа определяется восклицательным знаком, а ВЗ - адрес ячейки, в которой размещена первая фамилия сотрудника на листе "Начисления". Можно набрать формулу с клавиатуры, а можно после набора знака равенства перейти на лист "Начисления", выделить ячейку, содержащую первую фамилию и нажать (Enter ) (не возвращаясь к листу "Детские").

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

В графе "Сумма" аналогичным образом нужно разместить формулу =Начисления!НЗ*53130, где НЗ адрес первой ячейки на листе "Начисления", содержащей число детей. Заполните эту формулу вниз и примените денежный формат числа.

Выполните обрамление таблицы.

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

Создание шаблона. Работа с шаблонами документов. Совместное использование Word и Excel .

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

Создание бланка-шаблона

1. Оставьте в рабочей книге только один лист.

2
. Сформатируйте заголовок табеля учета рабочего времени за текущий месяц и подготовьте таблицу-бланк по образцу, приведенному на рис. 1

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

Введите числа месяца с 1-го по 31-е. Для столбцов, содержащих даты, установите ширину столбца, равную 2.

Если на вашем предприятии постоянный состав сотрудников, внесите в шаблон фамилии и профессии.

3. Для сохранения подготовленного файла в качестве шаблона:

Введите имя сохраняемого файла в поле ввода Имя файла : Табель;

В списке типов файлов выберите Шаблон, расширение файла сменится на.xlt ;

Нажмите ОК;

Закройте файл.

Применение шаблона

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

В меню Файл выберите Создать.

В списке Общие диалогового окна <Создание документа> выделите шаблон, на основе которого хотите создать новую рабочую книгу (рис.2).

Выберите кнопку ОК.

Таким образом, вы получите рабочую копию шаблона.

1. Введите название текущего месяца в заголовок табеля.

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

3. Проставьте для каждого сотрудника:

Количество часов, отработанных за день, или

о, если он находится в отпуске, или

б, если в этот день сотрудник болеет, или

п, если прогуливает.

о, б, п - русские буквы, проставляются без кавычек.

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

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

Microsoft Excel позволяет зафиксировать заголовок на странице, чтобы при перемещении нужные вам столбцы (или строки) оставались на своем месте. Для того, чтобы зафиксировать столбец "Фамилия":

Выделите столбец справа от столбца "Фамилия" ("Профессия");

В меню Окно выберите команду Закрепить области;

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

Чтобы зафиксировать горизонтальные заголовки, выделите строку ниже заголовков.

Чтобы зафиксировать вертикальные заголовки, выделите столбец справа от заголовков.

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

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

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

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

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

5. Для подсчета дней явок необходимо в каждой строке (для каждого сотрудника) подсчитать количество ячеек, содержащих числа (не суммируя эти числа). Для этого:

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

Выполните команду Вставка  Функция...;

В списке Имя функции окна диалога <Мастер функций> выберите функцию СЧЕТ (рис. 3). Если вы не знаете, к какой категории относится искомая функция, выберите категорию Полный алфавитный перечень и дальше ищите по алфавиту. Нажмите кнопку Ок .

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

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

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

Нажмите кнопку Ок.

Заполните формулу вниз.

б. Для подсчета количества дней, проведенных в отпуске, вставьте функцию СЧЕТЕСЛИ и, в качестве критерия введите образец (что нужно подсчитывать) русскую букву о, т. е. тот символ, который вы вносили в таблицу, отмечая отпуск.

Заполните формулу вниз по столбцу.

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

Упражнение 2

Совместное использование Word и Excel .

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

Использование кнопок Excel

Панели инструментов Word содержат две кнопки для работы с Excel : одна на стандартной панели инструментов и другая - на панели инструментов Micro soft , как показано ниже. Чтобы вывести на экран панель инструментов Microsoft , выберите команду Вид  Панели инструментов и установите флажок Microsoft , после чего щелкните по ОК.

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

Функции этих двух кнопок кратко можно описать следующим образом:

Кнопка Добавить таблицу Excel на стандартной панели инструментов приводит к внедрению в документ Word электронной таблицы - то есть при этом вы сможете редактировать электронную таблицу Excel прямо в документе Word .

Кнопка Microsoft Excel на панели инструментов Microsoft приводит к связыванию электронной таблицы или вставке базы данных из Excel ; щелчок по этой кнопке приводит к запуску Excel или (если он уже запущен) переключению в окно Excel .

Обмен информацией с Excel

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

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

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

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

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

Использование ячеек таблицы Excel

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

Вставка ячеек

Чтобы вставить в документ Word ячейки электронной таблицы Excel , поступайте следующим образом:

2. Либо откройте одну из существующих книг, либо введите нужное содержимое в новую таблицу.

3. Выделите ячейки, которые вы хотите скопировать в документ Word , и выберите команду Правка  Копировать .

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

После вставки содержимое ячеек будет оформлено в виде таблицы Word и не будет иметь никакой связи с Excel или файлом, созданным в Excel .

Внедрение ячеек

Чтобы внедрить ячейки таблицы Excel в документ Word , поступайте следующим образом:

1. Щелкните по кнопке Добавить таблицу Excel на стандартной панели инструментов и протаскиванием в появившейся сетке укажите число строк и столбцов, которые вы хотите вставить в документ Word (точно так же, как при использовании кнопки Вставить таблицу). После этого вы увидите в своем документе объект Excel , который выглядит так:

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

3. Щелкните в документе Word за пределами таблицы, чтобы вернуться к работе с документом. Тех же самых результатов можно добиться, выбрав команду Вставка  Объект , указав вкладку Создание, выбрав из списка Тип объекта пункт Лист Microsoft Excel и щелкнув по ОК.

Связывание ячеек

Чтобы связать ячейки книги Excel с документом Word , поступайте так:

1. Щелкните по кнопке Microsoft Excel на панели инструментов Microsoft , чтобы запустить Excel .

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

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

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

5. Выберите команду Правка  Специальная вставка .

6. В диалоговом окне Специальная вставка установите опцию Форматированный текст (RTF ). Установите флажок Связать и щелкните по ОК.

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

Использование диаграмм Excel

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

Самостоятельно создайте в Excel диаграмму и выполните вставку и внедрение диаграммы в Word .

Найдите материал к любому уроку,

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ

ФГБОУ ВО «ВЯТСКАЯ ГОСУДАРСТВЕННАЯ

СЕЛЬСКОХОЗЯЙСТВЕННАЯ АКАДЕМИЯ»

Кафедра информационных технологий и статистики

Ливанов Р.В.

Практикум по работе в электронной таблице Microsoft Office Excel 2007

для студентов экономического факультета

КИРОВ Вятская ГСХА

Практическая часть

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

Общее знакомство с Microsoft Excel

Запустите программу Microsoft Excel: «Пуск» «Все программы» «Microsoft Office» «Microsoft Office Excel 2007» или при помощи соответствующего ярлыка на рабочем столе. В результате откроется новая рабочая книга, содержащая несколько рабочих листов.

В открывшемся окне найдите следующие элементы:

Кнопки управления

Лента инструментов

Полосы прокрутки

Строка заголовка

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

Рабочая область листа

Панель быстрого

Активная ячейка

13. Зона заголовков

столбцов

Кнопка Office

Зона заголовков строк

Строка формул

Вкладки на ленте

10. Ярлыки рабочих

Кнопка вставки

Задание 1. Основы работы с электронными таблицами.

1. Переименуйте название рабочего листа.

Щелкните ПКМ по ярлыку «Лист1» в нижней части рабочего листа и в контекстном меню выберите команду «Переименовать» .

Удалите старое название рабочего листа, введите с клавиатуры новое название «Принтеры» и нажмите клавишу Enter .

2. Подготовьте ячейки таблицы к вводу исходных данных.

Выделите диапазон ячеек A1:D1 и задайте команду контекстного меню

«Формат ячеек».

«переносить по словам» и выберите тип горизонтального и вертикального выравнивания – по центру .

На вкладке «Шрифт» диалогового окна выберите тип начертания шрифта –

полужирный курсив и нажмите кнопку «ОК» .

3. Заполните таблицу данными по предложенному ниже образцу.


		Наименования	Количество,



		Принтер лазерный, ч/б

		Принтер лазерный, цв.

		Принтер струйный, ч/б

		Принтер струйный, цв.

		Принтер матричный, ч/б

4. Рассчитайте объем продаж как произведение количества и цены.

Выделите ячейку D2 и введите с клавиатуры знак = .

Щелкните ЛКМ по ячейке В2 , с клавиатуры введите знак * и щелкните ЛКМ по ячейке С2 . Если все сделано правильно, то в строке формул появится формула следующего вида: =В2*С2 .

Нажмите клавишу Enter – в ячейке появится результат расчета по формуле:

450000.

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

Выделите ячейку D2 , в которой находится результат вычислений.

Установите курсор мыши на маркер заполнения (маленький квадратик в правом нижнем углу выделенной ячейки).

Нажмите ЛКМ и, удерживая ее, протяните курсор до 6-й строки включительно. Если все сделано правильно, то все ячейки столбца «Объем продаж» будут заполнены рассчитанными значениями.

6. Установите для чисел в столбцах «Цена» и «Объем продаж» денежный формат.

Выделите диапазон ячеек С2:D6 и задайте команду контекстного меню

«Формат ячеек».

денежный , число десятичных знаков – 0 , обозначение – р. и нажмите кнопку «ОК» .

7. Вставьте в таблицу новый столбец.

Выделите щелчком ЛКМ любую ячейку первого столбца (например А2

или А3 ).

«Вставить столбцы на лист» – в результате слева от таблицы появится новый столбец.

В ячейку А1 введите заголовок нового столбца № п/п и установите для данной ячейки горизонтальное и вертикальное выравнивание – по центру ,

тип начертания шрифта – полужирный курсив .

8. Заполните столбец «№ п/п» с использованием автозаполнения.

В ячейку А2 введите цифру 1 , в ячейку А3 – цифру 2 .

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

Наведите курсор мыши на маркер заполнения в правом нижнем углу выделенных ячеек, нажмите ЛКМ и, удерживая ее, протяните курсор до 6-й

строки включительно. В результате в столбце появятся числа от 1 до 5 .

9. Вставьте в таблицу новую строку для оформления заголовка таблицы.

Выделите щелчком ЛКМ любую ячейку первой строки (например В1

или С1 ).

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

Выделите диапазон ячеек A1:Е1 и задайте команду контекстного меню

«Формат ячеек».

В появившемся диалоговом окне на вкладке «Выравнивание» установите флажок «объединение ячеек» , выберите тип горизонтального выравнивания

– по центру .

На вкладке «Шрифт» выберите тип начертания шрифта – полужирный ,

цвет шрифта – красный и нажмите «ОК» .

В объединенную ячейку введите заголовок таблицы: Объем продаж принтеров .

10. Установите обрамление ячеек таблицы.

Выделите все ячейки таблицы за исключением ее заголовка (диапазон

А2:Е7 ) и задайте команду контекстного меню «Формат ячеек» .

В появившемся диалоговом окне на вкладке «Граница» выберите тип

11. Установите заливку ячеек таблицы.

Выделите шапку таблицы (диапазон А2:Е2 ) и задайте команду контекстного меню «Формат ячеек» .

В появившемся диалоговом окне на вкладке «Заливка» выберите какой-либо цвет заливки ячеек и нажмите «ОК» .

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

12. Постройте диаграмму по столбцам «Наименования товаров» и

«Количество».

Выделите диапазон ячеек В2:С7 , задайте команду «Вставка» «Гистограмма» и в раскрывающемся списке выберите вид гистограммы –

гистограмма с группировкой (первый шаблон в первой строке) – в

результате диаграмма построится.

На вкладке «Конструктор» нажмите кнопку «Строка/столбец» –

в результате на гистограмме изменится вид отображения рядов данных.

На вкладке «Макет» нажмите кнопку «Название диаграммы» , в

раскрывающемся списке выберите размещение названия «Над диаграммой»

и введите название диаграммы Принтеры .

Используя кнопку «Подписи данных» на вкладке «Макет» , установите

в диаграмме числовые подписи рядов данных с размещением «В центре» .

На вкладке «Конструктор» нажмите кнопку «Переместить диаграмму»,

в появившемся диалоговом окне выберите размещение диаграммы на отдельном листе и нажмите кнопку «ОК» – в результате в рабочей книге появится новый рабочий лист с названием «Диаграмма1» , на котором будет размещена диаграмма.

13. Рассчитайте строку «Итого» по столбцу «Объем продаж» .

Перейдите на рабочий лист «Принтеры» , содержащий таблицу с данными.

В ячейку В8 введите Итого , а в ячейках С8 и D8 поставьте прочерки.

Установите курсор в ячейку Е8 и щелкните по кнопке автосумма на вкладке «Главная» – в результате в ячейке появится формула

СУММ(Е3:Е7).

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

Установите для строки «Итого» обрамление и свой цвет заливки.

14. Измените данные в таблице по столбцу «Количество» .

Выделите ячейку С3 и введите в нее значение 30 – после нажатия клавиши

Enter произойдет автоматический пересчет значений в столбце «Объем продаж» .

Выделите ячейку С7 , введите в нее значение 7 и нажмите клавишу Enter .

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

В результате всех вышеперечисленных действий отформатированная

таблица должна выглядеть следующим образом:



		Объем продаж принтеров
		Наименования	Количество,


		Принтер лазерный, ч/б
		Принтер лазерный, цв.
		Принтер струйный, ч/б
		Принтер струйный, цв.
		Принтер матричный, ч/б

Задание 2. Использование условного форматирования при расчетах.

1. Перейдите на новый рабочий лист «Лист2» и присвойте ему имя

«Финансы».

2. Выделите и объедините диапазон ячеек А1:Е1 , установите горизонтальное выравнивание – по центру и введите заголовок таблицы:

Движение денежных средств.

3. Выделите диапазон ячеек А2:Е9 и установите для выделенных ячеек внешние и внутренние границы, используя на вкладке «Главная» кнопку и шаблон «Все границы» .

4. Оформите шапку таблицы.

В ячейку А2 введите Месяц .

В ячейку В2 введите На начало периода.

В ячейку С2 введите Доходы .

В ячейку D2 введите Расходы .

В ячейку Е2 введите На конец периода.

Выделите диапазон ячеек А2:Е2 и установите для них отображение –

переносить по словам , горизонтальное и вертикальное выравнивание –

по центру , начертание шрифта – курсив .

5. Заполните данными столбец «Месяц» с использованием автозаполнения.

В ячейку А3 введите название месяца Январь .

Наведите курсор мыши на маркер заполнения ячейки А3 и, удерживая ЛКМ,

протяните курсор до 8-й строки включительно. В результате в столбце появятся названия месяцев с января по июнь.

В ячейку А9 введите Итого за полугодие и установите для этой ячейки перенос по словам.

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

В ячейку В3 введите значение 1000 .

Заполните данными столбцы «Доходы» и «Расходы» «На конец периода» и «На начало периода» .

Выделите ячейку Е3 и введите в нее формулу следующего вида: =B3+C3–D3

Нажмите клавишу Enter – при этом в ячейке появится результат расчета по формуле: 980 .

Выделите ячейку В4 и введите в нее формулу: =Е3

Откопируйте формулу в остальные ячейки этого столбца.

8. Установите для ячеек с числами в таблице денежный формат.

Выделите диапазон ячеек В3:Е8 и задайте команду контекстного меню

«Формат ячеек».

В диалоговом окне на вкладке «Число» выберите числовой формат –

денежный , число десятичных знаков – 0 , обозначение – $ и нажмите кнопку «ОК» .

9. Рассчитайте суммарные доходы и расходы за полугодие.

Выделите ячейку С9 , щелкните по кнопке автосумма на вкладке

«Главная» и нажмите клавишу Enter .

Откопируйте полученную функцию вправо по строке в ячейку D9 .

10. Рассчитайте финансовый результат деятельности.

Выделите диапазон ячеек А12:D12 , объедините их и установите горизонтальное выравнивание – по правому краю .

Введите в получившуюся ячейку: Финансовый результат: прибыль (+),

убыток (–).

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

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

Упражнение 1

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

1. Запустите программу Microsoft Excel, любым, известным вам способом. Внимательно рассмотрите окно программы Microsoft Excel. Первый взгляд на горизонтальное меню и панели инструментов несколько успокаивает, так как многие пункта горизонтального меню и кнопки панелей инструментов совпадают с пунктами меню и кнопками окна редактора Word.

Зафиксировать данные молено одним из способов:

нажать клавишу (Enter};

щелкнуть мышью по другой ячейке,

воспользоваться кнопками управления курсором на клавиатуре (перейти к другой ячейке).

Зафиксируйте введенные вами данные.

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

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

Каков заголовок этого столбца?

Выделите целикам ту строку таблицы, а которой расположено название дня недели-

Какой заголовок имеет эта строка?

Выделите ячейку D5; F2; А16.

Какой адрес имеет ячейка, содержащая день недели?

рис.1. 1.

вторник, утро

рис. 1. 2.

вторник, ут

внести изменения в содержимое выделенной ячейки;

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

6. Как увеличить ширину столбца для того, чтобы в ячейке одновременно были видны и день недели, и часть суток?

Выделите блок ячеек, начав с ячейки А1 и закончив ячейкой, содержащей "сегодня".

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

Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке.

8. Каким образом удалить содержимое ячейки? Для этого достаточно выделить ячейку (или блок ячеек) и нажать клавишу {Delete} или воспользоваться командой горизонтального меню Правка  Очистить.

Удалите все свои записи.

Упражнение 2

Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних

индексов.

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

Для начала напомним формулу n-го члена арифметической прогрессии:

a n =a 1 +d(n-l)

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

S n =(a 1 + a n )* n /2, где a 1 - первый член прогрессии, a d - разность арифметической прогрессии.

На рис. 1.3 представлена таблица для вычисления n-го члена и суммы арифметической прогрессии, первый член которой равен -2, а разность равна 0,725.

Вычисление n -го члена и суммы арифметической прогрессии
	a n	S n

Рис. 1.3.

Выполнение упражнения можно разложить по следующим этапам.

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

Сформатируйте строку заголовков таблицы. В ячейку A3 введите "d", в ячейку ВЗ - "n", в СЗ - "a n ". в D3 - "S n ".

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

Строка-заголовок вашей таблицы оформлена. Можете приступить к заполнению.

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

Заполните таким образом значением разности арифметической прогрессии еще девять ячеек ниже ячейки А4.

В следующем столбце размещена последовательность чисел от 1 до 10.

В третьем столбце размещаются n-е члены прогрессии. Введите в ячейку С4 значение первого члена арифметической прогрессии.

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

Все формулы начинаются со знака равенства.

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

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

Выделите все ячейки таблицы, содержащие данные (не столбцы целиком, а только блок заполненных ячеек без заголовка "Вычисление n-го члена и суммы арифметической прогрессии") и выполните команду Формат  Столбец  Подгон ширины

Рис. 1. 5.

Рис. 1.6 .

ришла пора заняться заголовком таблицы "Вычисление n-го члена и суммы арифметической прогрессии".

В
ыделите четыре ячейки от А1 до D1 и выполните команду Формат  Ячейки..., выберите закладку Выравнивание и установите переключатели в положение "Центрировать по выделению" (Горизонтальное выравнивание) и "Переносить по словам" (рис. 1.5). Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.

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

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

Лабораторная работа № 3 (квартплата)

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

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

		Квартплата
Тариф за I кв. м:		5 р.
Срок оплаты:
Пени за I день:		1,5 р.		■■"■-■:":";"■"■ "■" ."

. № квартиры.	Фамилия квартиросъемщика	Площадь кв. м	Сумма	Дата оплаты	Просрочка	Штраф	Итого

Все заголовки столбцов должны быть выровнены по центру как по горизонтали, так и по вертикали, при определении формата ячейки примените опцию переноса слов.
Столбец «№ квартиры»: 10, 11, 12, ... 50. Центральное выравнивание.
Столбец «Фамилия квартиросъемщика»: Иванов, Петров, Сидоров, Краснов, Белов, все остальные Куропаткин 1, Куропаткин 2,
Столбец «Площадь»: 70; 69,5; 69 и т. д. (каждая следующая на 0,5 кв. м меньше предыдущей), выравнивание центральное.
Столбец «Сумма»: для каждой квартиры умножается значение из графы «Площадь» на значение из графы «Тариф» (которое может быть изменено учителем во время работы); формат рублевый без копеек.
Столбец «Дата оплаты»: с 10 февраля, каждая следующая квартира произвела оплату на день позже предыдущей. Формат Дата, полная форма.
Столбец «Просрочка»: если оплата была произведена до срока оплаты включительно, то автоматически ставится 0 (ноль); если позже срока, то должно подсчитываться количество дней просрочки («Дата оплаты» - «Срок оплаты»). Срок оплаты может меняться учителем во время работы с вашей таблицей. Данные выровняйте по центру.
Столбец «Штраф»: для каждой квартиры умножается значение из графы «Пени за 1 день» на значение из графы «Просрочка». Формат денежный без копеек.
Столбец «Итого»: суммируются значения из граф «Сумма» и «Штраф», формат денежный без копеек.

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

Министерство образования и науки

Российской Федерации

Федеральное государственное автономное образовательное учреждение

высшего профессионального образования

Национальный исследовательский ядерный университет «МИФИ»

Волгодонский инженерно-технический институт – филиал НИЯУ МИФИ

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

МЕТОДИЧЕСКИЕ УКАЗАНИЯ к лабораторной работе

по информатике в программе microsoft excel

Волгодонск 2010

УДК 519.683(076.5)

Рецензент канд. техн. наук З.О. Кавришвили

Составитель В.А. Булава

Создание таблиц . Методические указания к лабораторной работе в программе Microsort Excel. 2010. 13 с.

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

_____________________________________________________________________________

ã Волгодонский институт НИЯУ МИФИ, 2010

ã Булава В.А, 2010

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

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

Постановка задачи .

Вычислить значение функции y = f (x )/ g (x ) для всех х на интервале [ a , b ] с шагом к . Значение функций f (x ) , g (x ) , значение концов интервала a и b и значение шага к задается из таблицы 1 в Приложении согласно варианту для конкретной специальности.

Решение должно быть получено в виде таблиц «Основной» и «Вспомогательной».

Вычисленные значения функции у скопировать в столбец К без формул.

Запуск программы Excel осуществляется с помощью команд Пуск → Программы → Microsort Excel .

При создании таблицы в первой строке объединить ячейки А1:Н1 и в центре расположить текст «Таблицы».

Во второй строке объединить ячейки А2:Е2 и в центре расположить текст «Основная». Объединить ячейки G2:H2 и в центре расположить текст «Вспомогательная»

В ячейку А3 ввести текст «№ п/п». В ячейках В3:F3 разместить соответственно названия столбцов: х ; f (x )=…(согласно своему варианту) ; g (x )=…(согласно своему варианту) ; y = f (x )/ g (x ).

В ячейках G3:H3 разместить соответственно названия столбцов: a ; к .

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

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

Цвет шрифта у названий таблиц должен быть синий.

Внешние границы таблиц окрасить в синий цвет, внутренние границы – в зеленый, заливку ячеек – в желтый.

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

Результат выполнения лабораторной работы предоставить в виде отчета в печатном или в электронном варианте.

Печатный вариант отчета должен содержать:

а) титульный лист;

б) цель работы;

в) постановку задачи;

г) результат выполнения задания.

2. Результат выполнения лабораторной работы в электронном виде предоставить на дискете 3,5 дюйма в виде файла с именем «Таблицы».

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

Что такое абсолютная, относительная, смешанная адресации?

Каким образом происходит автозаполнение ячеек числами, формулами?

Какие существуют способы выравнивания содержимого ячейки?

Каким образом можно изменить цвет и толщину линий внешних и внутренних границ таблицы?

Каким образом можно изменить цвет фона ячеек таблицы?

Типовой пример.

Вычислить значение функции у = х∙sin(x)/(x+1) на отрезке с шагом 0,1. Решение предоставить в виде таблицы. Вычисленные значения функции у скопировать в столбец К без формул.

Решение.

В данном случае f (x ) = x ∙ sin (x ) , g (x ) = x +1 , a =0 , b = 2 , k = 0.1

1. В первой строке таблицы выделим ячейки А1:Н1. Выполним команду Формат → Ячейки , в открывшемся окне раскроем вкладку выравнивание и выберем пункт объединение ячеек . В центре объединенных ячеек введем текст «Таблицы».

2. Аналогичным образом во второй строке объединим ячейки А2:Е2 и в центре расположим текст «Основная» и объединим ячейки G2:H2, и в центре расположим текст «Вспомогательная».

3. В третьей строке в ячейке А3 введем текст № п/п (название первогостолбца таблицы) , в ячейке В3 – х (название второгостолбца таблицы), вячейке С3 – f (x )= x ∙ sin (x ) , в ячейке D3 – g (x )= x +1 , в ячейке Е3 – у= f (x )/ g (x ) , в ячейке G3 – a , в ячейке H3 – k .

4. В ячейку А4 введем 1 и заполним ячейки А5:А24 числами от 2 до 21. Для этого выделим ячейку А4 (сделаем ее текущей), она выделится в черную рамку. Наведем курсор мыши на маркер заполнения (черный крестик в правом нижнем углу ячейки) и нажав правую кнопку мыши протянем маркер заполнения вдоль столбца А таким образом, чтобы черная рамка охватила ячейки А5:А24. Отпустив правую кнопку мыши, в открывшемся меню выберем пункт заполнить . Ячейки А5: А24 заполнятся числами 2;3;4…

5. В ячейку G4 занесем значение 0 (значение левого конца интервала).

6. В ячейку Н4 занесем значение 0,1 (величина шага).

7. Заполним столбец В значениями х :

В ячейку В4 занесем формулу =$ G $4 (начальное значение х), знак $ указывает на абсолютную адресацию. В ячейку В5 занесем формулу =В4+$ H $4. Это означает, что начальное значение х будет увеличено на величину шага;

с помощью метода автозаполнения заполним этой формулой ячейки В5:В24. Выделим ячейку В5. Наведем указатель мыши на маркер заполнения и, нажав левую кнопку мыши, протянем маркер заполнения таким образом, чтобы черная рамка охватила ячейки В5:В24. Столбец В заполнится числами 0; 0,1; 0,2;…, а в строке формул будут соответствующие формулы.

8. Заполним столбец С значениями функции f(x)=x∙sin(x). В ячейку С4 занесем формулу =В4∙sin(B4). Заполним этой формулой ячейки С5:С24 с помощью метода автозаполнения.

9. Заполним столбец D значениями функции g(x)=x+1. В ячейку D4 занесем формулу =В4+1. Заполним этой формулой ячейки D5:D24 с помощью метода автозаполнения.

10. Заполним столбец E значениями функции y=f(x)/g(x). В ячейку E4 занесем формулу =C4/D4, заполним этой формулой ячейки E5:E24 с помощью метода автозаполнения.

11. Выполним обрамление таблиц:

12. Изменим цвет фона ячеек основной и вспомогательной таблиц:

выделим основную таблицу;

введем команды меню Формат → Ячейки → Вид. В открывшемся окне выберем цвет желтый. Щелкнем по кнопке ОК.

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

13. В основной таблице полученные в результате вычислений значения у скопируем в столбец К без формул:

выделим ячейки Е4:Е24;

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

нажав правую кнопку мыши и не отпуская ее переместим указатель мыши в ячейку К4;

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

В результате выполнения работы получим таблицы:


Основная						Вспомогательная

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

Упражнение 1

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

Упражнение 2

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

Упражнение 1

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

1-й этап.

Упражнение 2

Упражнение 3

Введение понятия "имя ячейки ".

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

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

Создание бланка-шаблона

Применение шаблона

Использование кнопок Excel

Обмен информацией с Excel

Использование ячеек таблицы Excel

Вставка ячеек

Внедрение ячеек

Связывание ячеек

Использование диаграмм Excel

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

Лучшие статьи по теме

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