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

Упражнение 1

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

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

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

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

СЧЕТ № 123 от 15.11.2000

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

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

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

Дополнения:

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

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

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

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

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

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

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

1-й этап.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2-й этап

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

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

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

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

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

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

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

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

3-й этап

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

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

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

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

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

Упражнение 2

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

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

Рис. 2.4

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

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

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

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

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

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

          Для этого выделите ячейку, в которой должен разместиться результат вычислений  (ВЗ), и выполните команду Вставка_функция...] (рис. 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 символов и содержать буквы, цифры, подчерки ( _ ), символы: обратная косая черта ( \ ), точки и вопроси­тельные знаки. Однако первый символ должен быть буквой, подчерком ( _ ) или символом обратная косая черта ( \ ). Не допускаются имена, которые воспринимаются как числа или ссылки на ячейки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

·         Выделите вторую строку заголовка и выровняйте по центру.

·         Также видно, что для того, чтобы вся таблица могла размес­титься по ширине на листе, в некоторых ячейках текст "повернут на 90°". Выделите те ячейки, в которых нужно "развернуть" текст и выберите команду Формат_Ячейки..., на вкладке Выравнивание (рис. 3.1) выберите Ориентацию текста и обязательно активизи­руйте переключатель Переносить по словам (вертикальное вырав­нивание оставьте По нижнему краю).

·         Для оставшихся (неразвернутых) ячеек примените верти­кальное выравнивание По центру Формат_Ячейки...

·         Задайте обрамление таблицы (Формат_Ячейки.., вкладка Рамка).

·         Установите в ячейках, содержащих цены денежный формат числа(Формат_Ячейки.., вкладка Число).

·         Введите нумерацию строк таблицы (столбец №), при по­мощи маркера заполнения.

·         Вставьте формулы для количества остатка ("Кол-во При­хода" минус "Кол-во Расхода") и суммы остатка ("Кол-во Ос­татка" умножить на "Цену Расхода"). Распространите эти фор­мулы вниз по таблице.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

·        запуск Excel;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

·       

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

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

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

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

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

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

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

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

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

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

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

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

Упражнение 1

Создание шаблона. Работа с шаблонами документов. Совместное использование 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, поступайте следующим образом:

1. Щелкните по кнопке Microsoft Excel на панели инструментов Microsoft, чтобы запустить 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.