Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Башкирский Экономико-юридический техникум
Л.Н.КИСЕЛЕВА
ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ
В ПРОФЕССИОНАЛЬНОЙ ДЕЯТЕЛЬНОСТИ
Учебное пособие
(практикум по Microsoft Excel)
Часть 1
Уфа 2008
УДК 681.3 Печатается по решению
ББК 32.97 редакционно-издательского
К 44 совета БЭК
Киселёва Л.Н. Информационные технологии в профессиональной деятельности: (практикум по Microsoft Excel) Часть 1: Учебное пособие. – Уфа: БЭК, 2008. – 56 с.
Практикум разработан на основе Государственного образовательного стандарта и рабочих программ дисциплин, в которых предполагается изучение электронных таблиц для специальностей: 0602 «Менеджмент», 0603 «Финансы», 0604 «Банковское дело», 0201 «Правоведение», 0601 «Экономика и бухгалтерский учет (по отраслям)», 2202 «Автоматизированные системы обработки информации и управления», 2203 «Программное обеспечение вычислительной техники» для студентов очного и заочного отделений.
Комплекс упражнений, теоретический материал и контрольные работы позволяют студентам изучить основные возможности электронных таблиц и получить навыки в создании, заполнении, форматировании и использовании электронных таблиц для решения экономических и математических задач.
Рецензенты:
Р.Р.Жданов, канд. техн. наук, ст. преподаватель кафедры «Телекоммуникационные системы» Уфимского государственного авиационного технического университета;
Ю.В.Орлова, преподаватель Башкирского экономико-юридического техникума.
© Издательство «БЭК», 2008
Введение
Обрабатываемая информация часто представляется в виде таблиц. Часть ячеек таблицы содержит исходную или первичную информацию, а часть – производную. Производная информация является результатом различных арифметических или иных операций, совершаемых над первичными данными.
Для решения задач, которые можно представить в виде таблиц, разработаны специальные пакеты программ, называемые электронными таблицами или табличными процессорами.
Электронные таблицы ориентированы прежде всего на решение экономических задач. Однако имеющиеся в них инструментальные средства позволяют решать многие инженерные задачи, например, выполнять расчёты по формулам, строить графические зависимости и т. п.
Освоение работы электронных таблиц обеспечит вам возможность самостоятельно решать различные задачи, не используя языки программирования. Создавая электронную таблицу, пользователь выполняет одновременно функции разработчика алгоритма, программиста и конечного пользователя. Это обеспечивает высокую эффективность эксплуатации программ, так как в них оперативно могут вноситься любые изменения, связанные с изменением алгоритма, перекомпоновкой таблицы и т. п.
Раздел по изучению электронных таблиц входит в курс информационных технологий и информатики. Использовать электронные таблицы можно для решения задач в различных экономических дисциплинах, в курсах по численным и математическим методам.
Базовая идея электронных таблиц проста: значение ячейки с определённым адресом, например С17, является функцией значений других ячеек; при этом некоторые из этих значений сами могут быть функциями, а некоторые – исходными данными (константами). Однако, чтобы использовать электронные таблицы, созданные в Microsoft Excel, необходимо изучить множество операций, которые требуют определённых интеллектуальных усилий.
Настоящие методические указания предназначены для студентов всех специальностей БЭК, изучающих информационные технологии и информатику. Они могут быть также использованы при проведении практики по получению первичных профессиональных навыков студентами очной и заочной форм обучения.
Основы работы в Excel
Запуск Excel осуществляется различными способами:
Окно Excel состоит из следующих элементов:
1 – строка заголовка (шапка окна) с кнопками управления;
2 – строка меню;
3 – панель инструментов – Форматирование;
4 – панель инструментов – Стандартная;
5 – поле имени ячейки;
6 – строка ввода и редактирования (строка формул);
7 – названия столбцов;
8 – номера строк;
9 – лист электронной таблицы;
10 – строка состояния.
Вид может меняться в зависимости от настроек. При отсутствии какой-либо панели инструментов или строки формул необходимо выбрать пункт меню Вид – Панели инструментов.
1. Основные понятия Excel
Рабочее поле электронной таблицы (ЭТ) состоит из строк и столбцов. Номера строк могут изменяться от 1 до 65536. Заголовки столбцов могут принимать значения в диапазоне от А до IV (буквы английского алфавита).
На пересечении строки и столбца находится ячейка. Ячейка является основным строительным блоком любого рабочего листа. Каждая ячейка может хранить и отображать информацию, имеет уникальные координаты, которые называются адресом ячейки, или ссылкой. Ячейка на пересечении столбца А и строки 1 имеет адрес А1. Выделенную ячейку называют активной, или текущей ячейкой. Выбрать ячейку можно при помощи указателя мыши или стрелок перемещения курсора, клавишей Таb.
Блок представляет собой прямоугольную область смежных ячеек, он может состоять из одной или нескольких ячеек, строк или столбцов. Адрес блока состоит из координат противоположных углов, разделенных двоеточием. Например, В13:С19.
ЭТ имеет трехмерную структуру: она состоит из листов, как книга (всего листов 16). На экране виден только один лист – верхний. Переход на другой лист осуществляется щелчком мыши на ярлычках листов (над строкой состояния).
2. Выделение строк и столбцов
Выделение строк, столбцов осуществляется щелчком мыши на номере строки или названии столбца. При нажатии клавиши Ctrl можно выделить несколько столбцов (не отпуская кнопку мыши после щелчка, выделить столбцы или строки).
Для выделения блока надо щелкнуть кнопкой мыши на начальной ячейке блока и, не отпуская левую кнопку, протянуть мышь на последнюю ячейку. Для выделения блока с помощью клавиатуры необходимо, удерживая клавишу Shift, нажимать на соответствующие клавиши перемещения курсора.
Для выделения нескольких несмежных блоков необходимо выделить первый блок и, удерживая клавишу Ctrl, выделить следующие блоки.
Для отмены выделения достаточно щелкнуть кнопкой мыши на любой ячейке.
3. Типы данных
Существуют три типа данных, вводимых в ячейку: текст, число и формула.
Текст – это набор любых символов. Если длина текста больше ширины столбца и ячейка справа пуста, то текст на экране займет и ее место. При вводе данных в соседнюю ячейку предыдущий текст на экране будет «обрезан» (но при этом в памяти сохранен полностью).
Числа в ячейку можно вводить со знаком плюс или минус или без них. Если количество цифр введенного числа больше, чем ширина ячейки на экране, то Excel отображает его в экспоненциальной форме или вместо числа ставятся символы #### (но при этом число в памяти сохраняется). Экспоненциальная форма используется для представления очень маленьких и больших чисел. Например, число 5010000000 будет записано как 5.01Е+09. Число 0,0000005 будет записано как 5Е-7. Для ввода дробных чисел используется десятичная запятая.
В виде формулы может быть записано арифметическое выражение. Формула должна начинаться со знака = и содержать операторы, имена функций, числа, адреса ячеек, соединенные знаками арифметических операций. Числа и формулы интерпретируются как текст, если первым символом является пробел или апостроф. После ввода формулы в ячейке выводится число - результат расчета, а саму ее можно увидеть в строке формул.
Если в формуле необходимо использовать адрес ячейки, то чтобы не писать адрес вручную в режиме английского языка, достаточно просто щелкнуть на ячейке и адрес вставляется в формулу (при этом не учитывается алфавит).
Для просмотра формул во всех ячейках одновременно необходимо выполнить команду меню Сервис –Параметры – Вид – Формулы.
Для редактирования содержимого ячейки можно дважды щелкнуть в ячейке, либо выделить ее и нажать на клавишу F2, либо щелкнуть на строке формул. Завершается ввод данных клавишей Enter.
4. Копирование, перемещение содержимого ячеек
Копировать (перемещать) содержимое ячейки можно через буфер, нажав кнопку Копировать (Вырезать) на стандартной панели инструментов. Затем поместить курсор в нужную ячейку и нажать Вставить.
Excel позволяет также перемещать, используя приемы перетаскивания (Drag&Drop). Для этого установите указатель на границу активной ячейки (курсор примет вид стрелки), щелкните левую кнопку мыши и, не отпуская кнопку, перенесите указатель на новое место, затем отпустите кнопку мыши. Для копирования необходимо одновременно удерживать клавишу Ctrl.
Excel позволяет копировать ячейку с помощью маркера автозаполнения.
Если поставить курсор на ячейку с формулой, подвести указатель мыши к маркеру (указатель превратится в +), нажать кнопку мыши и перемещать на другие ячейки, то произойдет копирование формулы.
Введите в ячейку январь и выполните автозаполнение на несколько ячеек. Вы получите продолжение названий месяцев. Аналогично: если записать день недели (понедельник), то можно получить названия оставшихся дней недели.
Примечание: если отсутствует маркер автозаполнения, то установите флажок Разрешить перетаскивание ячеек в пункте меню Сервис – Параметры – вкладка Правка.
Ширина столбца и высота строки изменяется с помощью мыши следующим образом: установить указатель мыши на границу названия столбца (номера строки) и, не отпуская кнопку, перемещать мышь, затем отпустить кнопку.
Упражнения
Упражнение 1. Введение основных понятий, связанных с работой электронных таблиц Excel.
Внимательно рассмотрите окно программы Microsoft Excel.
Рабочая область представляет из себя размеченную таблицу, состоящую из ячеек одинакового размера. Одна из ячеек явно выделена (обрамлена черной рамкой). Как выделить другую ячейку? Достаточно щелкнуть по ней мышью, причем указатель мыши в это время должен иметь вид светлого креста.
Попробуйте выделить различные ячейки таблицы. Для перемещения по таблице воспользуйтесь полосами прокрутки.
Основным отличием работы электронных таблиц от текстового процессора является то, что после ввода данных в ячейку, их необходимо зафиксировать, т. е. дать понять программе, что вы закончили вводить информацию в эту конкретную ячейку.
Зафиксировать данные можно одним из способов:
Чтобы выровнить текст в ячейках, выделите их и воспользуйтесь кнопками выравнивания абзацев на панели Форматирование.
Выделите ячейку D5; F2; А16.
Как же дополнить содержимое ячейки таблицы (отредактировать), не набирая заново все данные? Выделив ячейку, содержащую часть суток, вы увидите, что ее содержимое дублируется в Строке формул, расположенной выше заголовков столбцов. Именно в Строку формул можно щелчком мыши установить традиционный текстовый курсор, внести все требуемые изменения и затем зафиксировать окончательный вариант данных.
Можно добиться желаемого результата и другим способом. Двойным щелчком мыши по ячейке вы попадаете в режим редактирования ячейки и получаете текстовый курсор прямо в ней. Остается только внести изменения и зафиксировать данные.
Запись может выйти за пределы своей ячейки и занять часть соседней, когда соседняя ячейка пуста.
Если видна только та часть ваших данных, которая помещается в ячейке, то для просмотра всей записи используйте Строку формул. Именно в ней можно увидеть все содержимое выделенной ячейки.
Для этого подведите указатель мыши к правой границе заголовка столбца, «поймайте» момент, когда указатель мыши примет вид черной двойной стрелки, и, удерживая нажатой левую клавишу мыши, переместите границу столбца вправо. Столбец расширился. Аналогично можно сужать столбцы и изменять высоту строки.
Выделенный блок «охвачен» рамкой, все ячейки, кроме той, с которой начали выделение, окрашены в черный цвет. Обратите внимание, что в процессе выделения в Поле имени регистрируется количество строк и столбцов, попадающих в выделение. В тот же момент, когда вы отпустили левую клавишу, в Поле имени высвечивается адрес активной ячейки, ячейки, с ко торой начали выделение цветом.
Для выделения всей таблицы используйте «пустую» угловую кнопку, расположенную над заголовком первой строки.
Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке.
Упражнение 2. Применение основных приемов работы с электронными таблицами: ввод данных в ячейку. Форматирование шрифта. Изменение ширины столбца. Автозаполнение, ввод формулы, обрамление таблицы, выравнивание текста по центру выделения, набор нижних индексов.
Составим таблицу, вычисляющую n-й член и сумму арифметической прогрессии.
Для начала напомним формулу n-го члена арифметической прогрессии:
an=ai+d(n-l)
и формулу суммы n первых членов арифметической прогрессии:
Sn=(a1+an)*n/2,
где a1 – первый член прогрессии, а d – разность арифметической прогрессии.
На рисунке представлена таблица для вычисления n-го члена и суммы арифметической прогрессии, первый член которой равен -2, а разность равна 0,725.
Перед выполнением упражнения придумайте свою арифметическую прогрессию, т. е. задайте собственные первый член прогрессии и разность.
|
Вычисление n-го члена и суммы арифметической прогрессии |
|||
|
d |
n |
аn |
Sn |
|
0,725 |
1 |
-2 |
-2 |
|
0,725 |
2 |
-1,275 |
-3,275 |
|
0,725 |
3 |
-0,55 |
-3,825 |
|
0,725 |
4 |
0,175 |
-3,65 |
|
0,725 |
5 |
0,9 |
-2,75 |
|
0,725 |
6 |
1,625 |
-1,125 |
|
0,725 |
7 |
2,35 |
1,225 |
|
0,725 |
8 |
3,075 |
4,3 |
|
0,725 |
9 |
3,8 |
8,1 |
|
0,725 |
10 |
4,525 |
12,625 |
Упражнение можно выполнить в несколько этапов.
Для набора нижних индексов воспользуйтесь командой Формат - Ячейки..., выберите вкладку Шрифт и активизируйте переключатель Подстрочный в группе переключателей Эффекты.
Выделите заполненные четыре ячейки и при помощи соответствующих кнопок панели инструментов увеличьте размер шрифта на 1 пункт, выровняйте по центру и примените полужирный стиль начертания символов.
Строка-заголовок вашей таблицы оформлена. Можете приступить к заполнению таблицы.
Если подвести указатель мыши к маркеру заполнения, и в тот момент, когда указатель мыши принимает форму черного крестика, протянуть маркер заполнения на несколько ячеек вниз, то весь ряд выделенных ячеек заполнится данными, расположенными в первой ячейке.
Заполните таким образом значением разности арифметической прогрессии еще девять ячеек ниже ячейки А4.
И опять нам поможет заполнить ряд маркер заполнения.
Введите в ячейку В4 число 1, в ячейку В5 число 2, выделите обе эти ячейки и, ухватившись за маркер заполнения, протяните его вниз.
Отличие от заполнения одинаковыми данными заключается в том, что выделив две ячейки, вы указали принцип, по которому следует заполнить оставшиеся ячейки. Маркер заполнения можно «протаскивать» не только вниз, но и вверх, влево или вправо, в этих же направлениях распространится и заполнение. Элементом заполнения могут быть не только формула или число, но и текст.
Можно ввести в ячейку «январь» и заполнив ряд дальше вправо получить «февраль», «март», а «протянув» маркер заполнения от ячейки «январь» влево, то соответственно получить «декабрь», «ноябрь» и т. д. Попробуйте.
Самое главное, прежде, чем распространять выделение, нужно выделить именно ту ячейку (или те ячейки), по которой форматируется заполнение.
В ячейку С5 нужно поместить формулу для вычисления n-го члена прогрессии, которая заключается в том, что каждая ячейка столбца отличается от предыдущей прибавлением разности арифметической прогрессии.
Для того чтобы ввести формулу, необходимо выделить ячейку, в которую хотите поместить формулу, набрать знак равенства и затем набрать саму формулу со ссылками на соответствующие ячейки таблицы (не забудьте, что заголовки столбцов определяются латинскими буквами и русские «А», «С», «В» хоть и похожи на такие же буквы латинского алфавита, они не являются равноценной заменой).
Выделите ячейку С5 и наберите в ней формулу =С4+А4 (не забудьте перейти на латиницу, а вместо ссылки на ячейку А4 можно ввести конкретное значение разности вашей арифметической прогрессии).
Можно и не набирать с клавиатуры адрес той ячейки, на которую делается ссылка. Набрав знак равенства, щелкните мышью по ячейке С4 и в строке формул появится ее адрес, затем продолжите набор формулы. В этом случае вам не нужно переключаться на латиницу.
Полностью введя формулу, зафиксируйте ее нажатием {Enter}, в ячейке окажется результат вычисления по формуле, а в Строке формул сама формула.
Вот проявилась и еще одна функция Строки формул: если в ячейке вы увидите результат вычислений по формуле, то саму формулу можно просмотреть в Строке формул, выделив соответствующую ячейку.
Если вы неправильно набрали формулу, исправить ее можно в Строке формул, предварительно выделив ячейку.
Выделите ячейку С8 и посмотрите в Строке формул, как выглядит формула. Вы увидите, что она приняла вид =С7+А7. Заметно, что ссылки в формуле изменились относительно смещению самой формулы.
Все столбцы одинаковой ширины, хотя и содержат информацию разного объема. Можно вручную (используя мышь) изменить ширину отдельных столбцов, а можно автоматически подогнать ширину.
Выделите все ячейки таблицы, содержащие данные (не столбцы целиком, а только блок заполненных ячеек без заголовка «Вычисление n-го члена и суммы арифметической прогрессии» и выполните команду Формат – Столбец – Подгон ширины.
Выделите ячейку А1 и примените полужирное начертание символов к содержимому ячейки. Заголовок довольно не эстетично «вылезает» вправо за пределы нашей маленькой таблички.
Выделите четыре ячейки от А1 до D1 и выполните команду Формат – Ячейки..., выберите вкладку Выравнивание и установите переключатели в положение «Центрировать по выделению» (Горизонтальное выравнивание) и «Переносить по словам». Это позволит расположить заголовок в несколько строчек и по центру выделенного блока ячеек.
Для этого выделите таблицу (без заголовка) и выполните команду Формат – Ячейки..., выберите вкладку Граница, определите стиль линии и активизируйте переключатели Сверху, Снизу, Слева, Справа. Данная процедура распространяется на каждую из ячеек.
Затем выделите блок ячеек, относящихся к заголовку: от А1 до D2 и, проделав те же операции, установите переключатель Внешние границы. В этом случае получается рамка вокруг всех выделенных ячеек, а не каждой.
Упражнение 3. Подготовьте таблицу для расчета ваших еженедельных трат на поездки в городском транспорте. (Заголовки – по строкам дни недели, а по столбцам виды транспорта.)
В соответствующие ячейки таблицы вы будете вводить число поездок на каждом виде транспорта в определенный день недели. В отдельную ячейку стоимость одной поездки на текущий момент. Для подсчета итогового результата примените формулу, отражающую произведение суммы общего числа поездок и стоимости одной поездки.
Для составления общей суммы используйте кнопку и выделение соответствующих ячеек таблицы. Примерный вид формулы:
=СУММ (В2:Н5) *D6 (двоеточие между адресами ячеек определяет интервал: все ячейки от В2 до Н5).
Для обрамления выделите сначала таблицу без последней строки и установите рамки, затем ячейки, имеющие отдельные рамки.
|
Понедельник |
Вторник |
Среда |
Четверг |
Пятница |
Суббота |
Воскресенье |
|
|
Метро |
|||||||
|
Автобус |
|||||||
|
Троллейбус |
|||||||
|
Трамвай |
|||||||
|
Стоимость одной поездки |
Всего за неделю |
Упражнение 4. Закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: сортировка данных, типы выравнивания текста в ячейке, формат числа.
Упражнение заключается в создании и заполнении бланка товарного счета.
Грузоотправитель и адрес
Грузополучатель и адрес
К Реестру № Дата получения «___» ________2007 г.
СЧЕТ № 123 от 13.10.07
Поставщик Торговый Дом Пресненский
Адрес 123456, Москва, Рочдельская ул., 4
Р/счет № 456789 в АВС-банке, МФО 987654
|
№ |
Наименование |
Ед. изм. |
Кол-во |
Цена |
Сумма |
|
|
|||||
|
|
|||||
|
|
|||||
|
|
|||||
|
ИТОГО |
Руководитель предприятия Чижов Е.Ю.
Главный бухгалтер Стасова А.И.
Выполнение упражнения лучше всего разбить на три этапа:
1-й этап – создание таблицы.
Основная задача – уместить таблицу по ширине листа. Для этого:
В результате вы получите в виде вертикальной пунктирной линии правую границу полосы набора (если ее не видно, переместитесь при помощи горизонтальной полосы прокрутки вправо) и нижнюю границу полосы набора (для того чтобы ее увидеть, переместитесь при помощи вертикальной полосы прокрутки вниз).
Авторазбиение на страницы позволяет уже в процессе набора данных и форматирования таблицы следить за тем, какие столбцы помещаются на странице, а какие нет.
Проще всего добиться этого следующим путем:
Хотя можно действовать и наоборот. Сначала «разлиновать» всю таблицу, а затем снять лишние линии обрамления.
2-й этап – заполнение таблицы, сортировка данных и использование различных форматов числа.
В нашем случае это пустые ячейки столбцов «Цена» и «Сумма». Их нужно выделить и выполнить команду Формат – Ячейки..., выбрать вкладку Число и выбрать категорию Денежный. Это даст вам разделение на тысячи, чтобы удобнее было ориентироваться в крупных суммах.
Для этого выделите все строки таблицы, кроме первой (заголовка) и последней («Итого»), можно не выделять и нумерацию.
Выполните команду Данные – Сортировка..., выберите столбец, по которому нужно отсортировать данные (в нашем случае это столбец В, так как именно он содержит перечень товаров, подлежащих сортировке), и установите переключатель в положение «По возрастанию».
3-й этап
Для этого выделите несколько первых строк таблицы и выполните команду Вставка – Строки. Вставится столько же строк, сколько вы выделили.
Обратите внимание, что текст «Дата получения «___»______2007 г.» и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы «Сумма» (самый правый столбец нашей таблички), только применено выравнивание вправо.
Упражнение 5. Введение понятия «абсолютная ссылка», установка точного значения ширины столбца при помощи команд горизонтального меню. Вставка функции при помощи мастера функций.
Новое понятие «абсолютная ссылка» можно рассмотреть на конкретном примере. Подготовим традиционную таблицу квадратов двузначных чисел, так хорошо знакомую каждому из курса алгебры.
|
ТАБЛИЦА КВАДРАТОВ |
||||||||||
|
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
|
|
1 |
100 |
121 |
144 |
169 |
196 |
225 |
256 |
289 |
324 |
361 |
|
2 |
400 |
441 |
484 |
529 |
576 |
625 |
676 |
729 |
784 |
841 |
|
3 |
900 |
961 |
1024 |
1089 |
1156 |
1225 |
1296 |
1369 |
1444 |
1521 |
|
4 |
1600 |
1681 |
1764 |
1849 |
1936 |
2025 |
2116 |
2209 |
2304 |
2401 |
|
5 |
2500 |
2601 |
2704 |
2809 |
2916 |
3025 |
3136 |
3249 |
3364 |
3481 |
|
6 |
3600 |
3721 |
3844 |
3936 |
4096 |
4225 |
4356 |
4489 |
4624 |
4761 |
|
7 |
4900 |
5041 |
5184 |
5329 |
5476 |
5625 |
5756 |
5929 |
6084 |
6241 |
|
8 |
6400 |
6561 |
6724 |
6889 |
7056 |
7225 |
7396 |
7569 |
7744 |
7921 |
|
9 |
8100 |
8281 |
8464 |
8649 |
8836 |
9025 |
9216 |
9409 |
9604 |
9801 |
Для этого выделите столбцы от А до К и выполните команду Формат – Столбец – Ширина..., в поле ввода Ширина столбца введите значение, например, 5.
Для этого выделите ячейку, в которой должен разместиться результат вычислений (В3), и выполните команду Вставка – Функция...
Среди предложенных категорий функций выберите «Математические», имя функции: «Степень», нажмите кнопку Шаг.
В следующем диалоговом окне введите число (основание степени) – А3*10+В2 и показатель степени – 2. Так же, как и при наборе формулы непосредственно в ячейке электронной таблицы, нет необходимости вводить адрес каждой ячейки, на которую ссылается формула, с клавиатуры. Работая с Мастером функций, достаточно указать мышью на соответствующую ячейку электронной таблицы и ее адрес появится в поле ввода «Число» диалогового окна. Вам останется ввести только арифметические знаки (*, +) и число 10.
Если диалоговое окно загораживает нужные ячейки электронной таблицы, переместите его в сторону, «схватив» мышью за заголовок. В этом же диалоговом окне можно увидеть значение самого числа (10) и результат вычисления степени (100).
Остается только нажать кнопку Закончить.
В ячейке В3 появился результат вычислений.
Почему результат не оправдал наших ожиданий? В ячейке С3 не видно числа, т. к. оно не помещается целиком в ячейку.
Расширьте мышью столбец С. Число появилось на экране, но оно явно не соответствует квадрату числа 11.
Почему? Дело в том, что когда мы распространили формулу вправо, Excel автоматически изменил с учетом нашего смещения адреса ячеек, на которые ссылается формула, и в ячейке С3 возводится в квадрат не число 11, а число, вычисленное по формуле = В3*10+С2.
Во всех предыдущих упражнениях нас вполне устраивали относительные ссылки на ячейки таблицы (при перемещении формулы по такому же закону смещаются и ссылки), однако здесь возникла необходимость зафиксировать определенные ссылки, т. е. указать, что число десятков можно брать только из столбца А, а число единиц только из строки 2 (для того, чтобы формулу можно было распространить вниз). В этом случае применяют абсолютные ссылки.
Для фиксирования любой позиции адреса ячейки перед ней ставят знак $.
Таким образом, верните ширину столбца С в исходное положение и выполните следующие действия.
Упражнение 6. Составьте таблицу умножения чисел от 1 до 9
Для заполнения последовательности чисел от 0 до 9 введите первые два числа (0 и 1), выделите обе ячейки, содержащие эти числа, и протяните маркер заполнения в нужном направлении. Введите в одну из ячеек таблицы формулу, используя абсолютную ссылку. Распространите эту формулу на всю таблицу. Обратите внимание на оформление таблицы: обрамление и фон (Формат – Ячейки..., вкладка Вид).
Закончив создавать и оформлять таблицу, выделите последнюю заполненную строку таблицы и протяните маркер заполнения вниз – таблица умножения продолжилась для последующих чисел. Выполните то же самое для последнего заполненного столбца. Таким образом, можно получить таблицу умножения и для больших чисел.
ТАБЛИЦА УМНОЖЕНИЯ
|
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
|
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
1 |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
|
2 |
0 |
2 |
4 |
6 |
8 |
10 |
12 |
14 |
16 |
18 |
|
3 |
0 |
3 |
6 |
9 |
12 |
15 |
18 |
21 |
24 |
27 |
|
4 |
0 |
4 |
8 |
12 |
16 |
20 |
24 |
28 |
32 |
36 |
|
5 |
0 |
5 |
10 |
15 |
20 |
25 |
30 |
35 |
40 |
45 |
|
6 |
0 |
6 |
12 |
18 |
24 |
30 |
36 |
42 |
48 |
54 |
|
7 |
0 |
7 |
14 |
21 |
28 |
35 |
42 |
49 |
56 |
63 |
|
8 |
0 |
8 |
16 |
24 |
32 |
40 |
48 |
56 |
64 |
72 |
|
9 |
0 |
9 |
18 |
27 |
36 |
45 |
54 |
63 |
72 |
81 |
Упражнение 7. Подготовьте таблицу, с помощью которой вам достаточно будет ввести последние показания счетчика, a Excel рассчитает расход электроэнергии и сумму оплаты.
Стоимость электроэнергии 1,26 руб./кВт ч
|
Месяц |
Дата |
Показания счетчика |
Расход кВт ч |
Сумма |
|
26.12.06 |
3750 |
|||
|
Январь |
30.01.07 |
3840 |
||
|
Февраль |
25.02.07 |
3960 |
||
|
Март |
23.03.07 |
4070 |
||
|
Апрель |
24.04.07 |
4185 |
||
|
Май |
30.05.07 |
4200 |
||
|
Июнь |
28.06.07 |
4290 |
||
|
Июль |
29.07.07 |
4400 |
||
|
Август |
28.08.07 |
4535 |
||
|
Сентябрь |
27.09.07 |
4680 |
||
|
Октябрь |
28.10.07 |
4790 |
||
|
Ноябрь |
29.11.07 |
4895 |
||
|
Декабрь |
26.12.07 |
5000 |
Для заполнения столбца названиями месяцев года, введите первый месяц и, выделив ячейку, протяните маркер заполнения вниз. В столбцы «Расход кВт ч» и «Сумма» введите формулы (для формулы столбца «Сумма» не забудьте абсолютные ссылки).
К некоторым заголовкам столбцов примените возможность «Переносить по словам», все заголовки центрируйте по горизонтали и вертикали.
Упражнение 8. Введение понятия «имя ячейки»
Представьте, что вы имеете собственную фирму по продаже какой-либо продукции и вам ежедневно приходится распечатывать прайс-лист с ценами на товары в зависимости от курса доллара.
«Наименование товара», «Эквивалент $ US», «Цена в р.». Заполните все столбцы, кроме «Цена в р.». Столбец «Наименование товара» заполните текстовыми данными (перечень товаров по вашему усмотрению), а столбец «Эквивалент $ US» числами (цены в долл.).
Почему неудобно в этой формуле умножать на конкретное значение курса? Да потому, что при каждом изменении курса, вам придется менять свою формулу в каждой ячейке.
Проще отвести под значение курса доллара отдельную ячейку, на которую и ссылаться в формуле. Ясно, что ссылка должна быть абсолютной, т. е. значение курса доллара можно брать только из этой конкретной ячейки с зафиксированным адресом.
Как задавать абсолютные ссылки, мы рассмотрели выше, однако существует еще один удобный способ: ссылаться не на адрес ячейки, а на имя, которое можно присвоить ячейке.
В появившемся диалоговом окне вам остается только ввести имя ячейки (ее точный адрес уже приведен в поле ввода «Ссылается на») и нажать кнопку ОК. Обратите внимание на то, что в Поле имени вместо адреса ячейки теперь размещено ее имя.
Для этого выделите самую верхнюю пустую ячейку столбца «Цена в рублях» и введите формулу следующим образом: введите знак «=», затем щелкните мышью по ячейке, расположенной левее (в которой размещена цена в долл.), после этого введите так «*» и в раскрывающемся списке Поля имени выберите мышью имя ячейки «Курс_доллара». Формула должна выглядеть приблизительно так: =В7*Курс_доллара.
Для этого необходимо выполнить действия, полностью совпадающие с такими же в редакторе Word.
В меню Вставка выберите команду Рисунок..., найдите каталог Clipart и выберите подходящий рисунок.
Рисунок, вставленный в Excel можно перемещать мышью по документу, а также изменять традиционным способом его ширину и высоту.
Если хотите убрать рамку вокруг рисунка, то воспользуйтесь командой Формат-Объект.... На вкладке Вид можно отменить рамку (рисунок предварительно должен быть выделен).
А можно вставить не рисунок, а логотип (фирменный знак) предприятия, воспользовавшись для этого возможностями WordArt (аналогично тому, как делали это в редакторе Word).
|
Курс доллара 25,7р. |
||
|
Наименование товара |
Эквивалент $US |
Цена в р. |
|
39 |
||
|
35 |
||
|
60 |
||
|
42 |
||
|
65 |
||
|
20 |
||
|
65 |
||
|
82 |
В первом столбце таблиц дан список товаров, а во втором и в третьем приведены цены на эти товары за 1994 и 1997 год соответственно. По приведенным данным требуется вычислить индекс цен и построить диаграммы.
Индекс цен служит для измерения инфляции. Для вычисления индекса цен берут соотношения между совокупной ценой товаров и услуг определенного набора товаров для временного периода и совокупной ценой идентичной или сходной группы товаров и услуг в базовом периоде. Выражается индекс цен обычно в процентах:
Индекс цен в = Цена группы товаров в данном периоде 100 %
данном периоде, % Цена аналогичной группы товаров
в базовом периоде
В ячейку D2 введите формулу =C2/B2*100 и скопируйте её в оставшиеся ячейки.
Для подведения итогов воспользуйтесь кнопкой Автосумма на панели инструментов Стандартная.
Для построения диаграммы воспользуйтесь кнопкой Мастер диаграмм или командами меню Вставка – Диаграмма. При построении диаграмм используется программа Microsoft Graph, с которой вы познакомились при изучении текстового процессора Microsoft Word.
Постройте диаграмму, сравнивающую цены за 1994 и 1997 годы (гистограмму или линейный график) и круговую диаграмму для индекса цен для одного из вариантов.
|
Товары |
Цены за 1994 год |
Цены за 1997 год |
Индекс цен |
|
Хлеб |
500 |
3000 |
|
|
Молоко |
1500 |
4900 |
|
|
Масло |
2800 |
5000 |
|
|
Картофель |
1200 |
3400 |
|
|
Мука |
1650 |
5600 |
|
|
Сумма |
|
Дата |
волна |
стрела |
агро |
кенгуру |
спорт |
|
высший курс |
37,7 |
78,5 |
560 |
97,2 |
24,5 |
|
низший курс |
34,3 |
45,9 |
210 |
89,7 |
12,8 |
|
средний курс |
35,54444 |
59,83333 |
418,5556 |
94,18889 |
21,93333 |
|
02.11.07 |
34,5 |
78,5 |
456,6 |
89,7 |
23,4 |
|
09.11.07 |
35,6 |
75,6 |
440 |
90,8 |
23,2 |
|
16.11.07 |
35,7 |
59,6 |
448,6 |
92,4 |
22,6 |
|
23.11.07 |
37,5 |
45,9 |
457,8 |
94,6 |
22,8 |
|
30.11.07 |
37,7 |
46,2 |
470 |
95,4 |
23,3 |
|
07.12.07 |
34,3 |
54,5 |
490 |
95,6 |
23,6 |
|
14.12.07 |
34,4 |
58,6 |
560 |
95,8 |
24,5 |
|
21.12.07 |
35 |
59,2 |
234 |
96,2 |
21,2 |
|
28.12.07 |
35,2 |
60,4 |
210 |
97,2 |
12,8 |
Используя статистические функции, заносим в В2 максимальное значение из области В5 : В13, в В3 – минимальное, в В4 – среднее. Для этого используем кнопку Вставка функции (fx) или команды меню Вставка – Функция – Статистические – МАКС (МИН, СРЗНАЧ), ОК. Затем выделяем диапазон, из которого выбирается значение, если он указан неверно, и нажимаем кнопку ОК.
Упражнение 11. Оформление рабочих листов. Изменение ориентации текста в ячейке, ознакомление с возможностями баз данных Excel. Сортировка данных по нескольким ключам. Подготовка документа к печати
Представьте себя владельцем маленького магазина. Необходимо вести строгий учет прихода и расхода товаров, ежедневно иметь перед глазами реальный остаток, иметь возможность распечатать наименование товаров по отделам и т. д. Даже в таком непростом деле Excel может заметно облегчить работу.
Разобьем данное упражнение на несколько заданий в логической последовательности:
Создание таблицы
|
ПРИХОД |
РАСХОД |
ОСТА-ТОК |
|||||||
|
№ |
Отдел |
Наименование |
Единица измерения |
Цена прихода |
Кол-во прихода |
Цена расхода |
Кол-во расхода |
Кол-во остатка |
Сумма остатка |
Во многих случаях удобнее пользоваться контекстным меню, вызы ваемым нажатием правой клавиши мыши.
Так, для форматирования ячеек их достаточно выделить, щелкнуть правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения и выбрать команду Формат – Ячейки..., и вы перейдете к тому же диалоговому окну. Да и редактировать содержимое ячейки (исправлять, изменять данные) совсем не обязательно в Строке формул.
Если дважды щелкнуть мышью по ячейке, то в ней появится текстовый курсор и можно произвести все необходимые исправления.
Вносите данные в таблицу не по отделам, а вперемешку (в порядке поступления товаров).
Заполните все ячейки, кроме тех, которые содержат формулы («Остаток»).
Обязательно оставьте последнюю строку таблицы пустой (но эта строка должна содержать все формулы и нумерацию).
Вводите данные таким образом, чтобы встречались разные товары из одного отдела (но не подряд) и обязательно присутствовали товары с нулевым остатком (все продано).
|
ПРИХОД |
РАСХОД |
ОСТА-ТОК |
|||||||
|
№ |
Отдел |
Наименование |
Единица |
Цена |
Кол-во прихода |
Цена |
Кол-во |
Кол-во остатка |
Сумма |
|
1. |
Кондитерский |
Зефир в шоколаде |
упак. |
12 р. |
15 |
15 р. |
15 |
0 |
|
|
2. |
Молочный |
Молоко |
упак. |
38 р. |
32 |
42 р. |
28 |
4 |
|
|
3. |
Мясной |
Колбаса докторская |
кг |
116 р. |
40 |
200 р. |
15 |
25 |
|
|
4. |
Мясной |
Сосиски |
кг |
100 р. |
12 |
122 р. |
10 |
2 |
|
|
5. |
Вино-водочный |
Пепси-кола |
бут. 1 л |
42 р. |
32 |
45 р. |
15 |
17 |
|
|
6. |
Вычисляемые поля (в которых размещены формулы) выводятся на экран без окон редактирования («Кол-во остатка» и «Сумма остатка»).
Теперь вы имеете свою таблицу как бы в форме отдельных карточек-записей (каждая из которых представляет строку таблицы).
Перемещаться между записями можно либо при помощи кнопок «Предыдущая», «Следующая», либо клавишами управления курсором (вверх, вниз), либо перемещая бегунок на полосе прокрутки формы данных.
Дойдя до последней записи (мы специально оставили ее пустой, но распространили на нее формулы и нумерацию), заполните ее новыми данными.
Перемещаться между окнами редактирования (в которые вносятся данные) удобно клавишей {Tab}.
Когда заполните всю запись, нажмите клавишу {Enter}, и вы автоматически перейдете к новой чистой карточке-записи.
Заполните несколько новых записей и затем нажмите кнопку Закрыть.
Как видно, заполнять таблицу в режиме формы довольно удобно.
Оперирование данными
Итак, вы заполняли таблицу в порядке поступления товаров, а хотелось бы иметь список товаров по отделам, для этого применим сортировку строк.
Если же вы хотите, чтобы внутри отдела все товары размещались по алфавиту, то выберите второй ключ сортировки: в раскрывающемся списке «Затем» выберите «Наименование», установите переключатель в положение «По возрастанию».
Теперь вы имеете полный список товаров по отделам.
Вспомним, что нам ежедневно нужно распечатывать список товаров, оставшихся в магазине (имеющих ненулевой остаток), но для этого сначала нужно получить такой список, т. е. отфильтровать данные.
Можно временно скрыть остальные столбцы. Для этого выделите столбец №, вызовите контекстное меню (правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения) и выберите команду Скрыть.
Таким же образом можно скрыть и остальные столбцы, связанные с приходом, расходом и суммой остатка.
Вместо команды контекстного меню можно воспользоваться командой горизонтального меню Формат – Столбец – Скрыть.
Упражнение 12. Знакомство с общими сведениями об управлении листами рабочей книги, удалении, переименовании листов. Формулы, имеющие ссылки на ячейки другого листа рабочей книги. Мастер диаграмм. Выделение ячеек таблицы, не являющихся соседними.
Подготовим ведомость на выдачу заработной платы (естественно, несколько упрощенный вариант).
Обратите внимание на то, что в нижней части экрана горизонтальная полоса прокрутки состоит из двух частей. Правая часть служит для перемещения по таблице (вправо, влево), а левая часть, содержащая ярлычки листов, позволяет перемещаться между листами.
По умолчанию рабочая книга открывается с 3-мя рабочими листами, имена которых Лист 1, ..., Лист 3. Имена листов выведены на ярлычках в нижней части окна рабочей книги.
Щелкая по ярлычкам, можно переходить от листа к листу внутри рабочей книги.
Ярлычок активного листа выделяется цветом, к надписи на нем применен полужирный стиль.
Для выбора конкретного листа достаточно щелкнуть по его ярлычку мышью.
Для выполнения упражнения нам понадобятся только четыре листа:
Чтобы вставить дополнительный лист, установите указатель мыши на ярлычок последнего листа и сделайте щелчок правой кнопкой, выберите в меню Добавить, а на вкладке Общие шаблон Лист, нажмите кнопку ОК.
Удалить выделенные листы можно, вызвав команду контекстного меню Удалить или воспользовавшись командой горизонтального меню Правка – Удалить лист.
Теперь выглядывают ярлычки только четырех листов.
Активен (ярлычок выделен цветом) Лист 1. Именно на нем мы и начнем создавать таблицу.
Создание таблицы
Создайте заготовки таблицы самостоятельно, применяя следующие операции:
|
№№ |
Фамилия, имя, отчество |
Оклад |
Налоги |
Сумма к выдаче |
Число детей |
||
|
Проф. |
Пенс. |
Подох. |
|||||
|
|
|||||||
|
|
|||||||
|
|
|||||||
|
|
Для форматирования формул вам наверняка понадобится дополнительная информация. Примем профсоюзный и пенсионный налоги, составляющими по 1 % от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Самое важное не забыть про абсолютные ссылки, так как и профсоюзный, и пенсионный налоги нужно брать от оклада, т. е. ссылаться только на столбец «Оклад». Примерный вид формулы =$СЗ*1 %, =$СЗ*0,01 или =$СЗ* 1/100. После ввода формулы в ячейку D3 ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец.
Подоходный налог подсчитаем по формуле: 13 % от Оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(СЗ-ЕЗ-400)*13%, или =(СЗ-ЕЗ-400)* 13/100, или =(С3-Е3-400)*0,13. После ввода формулы в ячейку F3 ее нужно распространить вниз.
Для подсчета Суммы к выдаче примените формулу, вычисляющую разность оклада и налогов. Примерный вид формулы: =СЗ-D3-E3-F3, размещенной в ячейке G3 и распространенной вниз.
Заполняйте столбцы «Фамилия, имя, отчество», «Оклад», и «Число детей» после того, как введены все формулы. Результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполнения таблицы.
После ввода всех данных желательно выполнить их сортировку (не забудьте перед сортировкой выделить все строки от фамилий до сведений о детях).
Можно ввести в формулу значение минимальной заработной платы на сегодняшний день, а можно, по аналогии с предыдущим упражнением, завести отдельную ячейку, присвоить ей имя, вносить в эту ячейку значение минимальной заработной платы и сослаться на нее в формуле.
Можно ввести строку для подсчета общей суммы начислений.
Поскольку мы собираемся в дальнейшем работать сразу с несколькими листами, имеет смысл переименовать их ярлычки в соответствии с содержимым. Переименуем активный в настоящий момент лист. Для этого выполните команду Формат – Лист – Переименовать... и в поле ввода Имя листа введите новое название листа, например, «Начисления».
Построение диаграммы на основе готовой таблицы
и размещение ее на новом листе рабочей книги
Подобно тому, как мы строили диаграммы на основе готовой таблицы в редакторе Word, можно это сделать и в Excel.
Построим диаграмму, отражающую начисления каждого сотрудника. Понятно, что требуется выделить два столбца таблицы:
«Фамилия, имя, отчество» и «Сумма к выдаче». Но эти столбцы не расположены рядом и традиционным способом мы не сможем их выделить. Для Excel это не проблема.
Если удерживать нажатой клавишу {Ctrl}, то можно одновременно выделять ячейки в разных местах таблицы.
Создадим ведомость на получение компенсации на детей на основе таблицы начислений. Используем ссылки на ячейки другого листа рабочей книги. Перейдём к Листу 3 и переименуем его в «Детские».
|
ФИО |
Сумма |
Подпись |
|
Иванов А.Ф. |
153,00 р. |
|
|
Круглова А.Д. |
306,00 р. |
|
|
Леонов И.И. |
153,00 р. |
|
|
Петрова Е.П. |
153,00 р. |
В нашей рабочей книге осталось оформить последний лист – ведомость на выдачу заработной платы. Таблица должна содержать следующие столбцы: «ФИО», «Сумма к выдаче» и «Подпись». Выполните это самостоятельно.
Таким образом, вы создали рабочую книгу, состоящую из четырех именованных листов.
Ею можно пользоваться для начисления заработной платы и распечатки ведомостей из месяца в месяц, внося изменения и сохраняя под новым именем.
Конечно, выполненная нами работа упрощена и несколько отличается от реального расчета заработной платы, но позволяет познакомиться с некоторыми важными процедурами.
Упражнение 13
Стоимость пансиона увеличьте на 40 % по отношению к полупансиону.
|
июнь |
||||
|
Категория отеля |
7 дней |
14 дней |
||
|
полупансион |
пансион |
полупансион |
пансион |
|
|
|
||||
|
|
||||
|
|
Далее работайте с Microsoft WordArt так же, как это делали при изучении Microsoft Word. В окне ввода текста наберите название фирмы (в нашем примере это туристическое агентство). Выберите стиль оформления и размер шрифта.
Прежде чем вставлять из буфера обмена скопированный лист (Правка – Вставить), выделите ячейку А1 чистого листа или выделите чистый лист целиком. В противном случае вам будет выдано сообщение о несовпадении области копирования и области вставки.
Упражнение 14. Закрепление областей. Вставка функций. Шаблоны
Представьте себя работником отдела кадров, которому ежемесячно предстоит заполнять табель учета рабочего времени на сотрудников предприятия. Разумеется, хотелось бы максимально автоматизировать эту операцию. Удобно создать шаблон заготовки бланка и применить специальные функции.
Создание бланка-шаблона
|
ФИО |
Профессия |
Разряд |
Числа месяца |
Дни явок |
Дни неявок |
Отработано часов |
|||||||
|
1 |
2 |
… |
29 |
30 |
31 |
отпуск |
болезнь |
прогул |
|||||
Воспользуйтесь всеми известными вам приемами форматирования. Сформатируйте заголовок, применив различные способы выравнивания текста.
Введите числа месяца с 1-го по 31-е. Для столбцов, содержащих даты, установите ширину столбца, равную 2.
Если на вашем предприятии постоянный состав сотрудников, внесите в шаблон фамилии и профессии.
Применение шаблона
Для создания нового файла с применением шаблона выполните следующие действия:
Таким образом, вы получите рабочую копию шаблона.
Имея такую широкую таблицу, как ваша, можно столкнуться с неудобствами при заполнении. Дело в том, что, перемещаясь вправо для заполнения таблицы, вы теряете из вида столбец с фамилиями и становится трудно определить, кому из сотрудников проставляете рабочие часы.
Microsoft Excel позволяет зафиксировать заголовок на странице, чтобы при перемещении нужные вам столбцы (или строки) оставались на своем месте. Для того, чтобы зафиксировать столбец «Фамилия»:
Работая с большими таблицами, можете пользоваться следующими возможностями фиксации заголовков.
В меню Окно выберите команду Закрепить области. Все строки выше выделенной строки (ячейки) будут зафиксированы и все столбцы слева от выделенного столбца (ячейки) будут зафиксированы.
Чтобы отменить фиксацию заголовков в меню Окно выберите команду Снять закрепление областей.
Хотелось бы ввести формулы для подсчета дней явок, неявок и отработанных часов.
Нажмите кнопку Закончить.
Заполните формулу вниз.
Для подсчета количества дней, проведенных в отпуске, вставьте функцию СЧЕТЕСЛИ и, в качестве критерия введите образец русскую букву о, т. е. тот символ, который вы вносили в таблицу, отмечая отпуск.
Заполните формулу вниз по столбцу.
Задания
Закройте шаблон, сохранив изменения под тем же именем, заменив старый шаблон Tabel. xls.
Создайте новую рабочую книгу с помощью этого шаблона. Проверьте, работают ли вставленные вами формулы.
Для выделения несоседних столбцов примените клавишу {Ctrl}.
Измените данные в табеле. Отразились ли эти изменения в диаграмме?
Попробуйте выделить один из столбцов гистограммы и увеличить его, «схватив» мышью за узелки. Изменились ли данные в таблице?
Предположим, вы хотите все табели за текущий год иметь в одной рабочей книге. В этом случае, перед созданием табеля за следующий месяц выполните следующее:
Новый лист вставился перед исходным. Переместите новый лист (схватив за ярлычок) после исходного, переименуйте и заполните данными.
Контрольные задания
Задание 1
Таблица 1
Операционный дневник
|
№ лицевого счёта |
Вид вклада |
Сумма вклада |
|||
|
Оста-ток входящий (тыс. руб.) |
приход (тыс. руб.) |
расход (тыс. руб.) |
остаток исходящий (тыс.руб.) |
||
|
1 |
2 |
3 |
4 |
5 |
6 |
|
R6596 |
До востребования |
54 |
4 |
||
|
F6775 |
Праздничный |
45 |
|||
|
S3445 |
Срочный |
76 |
8 |
9 |
|
|
G8746 |
До востребования |
15 |
12 |
2 |
|
|
Z7312 |
Срочный |
6 |
3 |
Расчетная формула: гр. 6 = гр. 3 + гр. 4 — гр. 5.
Таблица 2
Остаток вклада с начисленным процентом
|
№ лицевого счёта |
Вид вклада |
Остаток вклада |
|
1 |
2 |
3 |
|
R6596 |
До востребования |
|
|
F6775 |
Праздничный |
|
|
S3445 |
Срочный |
|
|
G8746 |
До востребования |
|
|
Z7312 |
Срочный |
Таблица 3
Процентная ставка
|
До востребования |
2% |
|
Праздничный |
5% |
|
Срочный |
3% |
Задание 2
10. В приложении Word создать документ и поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.
Формы документов:
Таблица 4
Ведомость расчета размера девальвации национальной валюты
|
Страна |
Год |
Курс национальной валюты (в долларах) |
Размер девальвации национальной валюты (в %) |
|
|
до девальвации |
после девальвации |
|||
|
1 |
2 |
3 |
4 |
5 |
|
Великобритания |
1997 |
2,8 |
2,4 |
|
|
Великобритания |
1998 |
2,4 |
2,1 |
|
|
Великобритания |
1999 |
2,1 |
1,8 |
|
|
ФРГ |
1998 |
0,4 |
0,38 |
|
|
ФРГ |
1999 |
0,38 |
0,32 |
|
|
Франция |
1998 |
0,132 |
0,13 |
|
|
Франция |
1999 |
0,13 |
0,12 |
|
|
РФ |
1998 |
0,167 |
0,06 |
|
|
РФ |
1999 |
0,06 |
0,04 |
Расчетная формула: гр. 5 = (гр. 3 — гр. 4) * 100/ гр. 3
Таблица 5
Тенденция изменения курса национальной валюты
|
Страна |
Год |
Тенденция изменения курса национальной валюты |
|
1 |
2 |
3 |
|
Великобритания |
2000 |
|
|
ФРГ |
||
|
Франция |
||
|
РФ |
Задание 3
Выполнить обрамление всей таблицы, убрать сетку.
Убрать рамки у легенды и диаграммы.
Документ сохранить, организовать просмотр перед печатью.
10. В приложении Word создать документ с заголовком диаграммы, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.
Форма документа:
Таблица 6
Ведомость расчета стоимости продукции с учетом скидки
|
Но-менк-латур-ный номер |
Наи-мено-вание про-дук-ции |
Ко-ли-чест-во (шт.) |
Цена (тыс. руб.) |
Сумма (тыс. руб.) |
% скид-ки |
Сумма скидки (тыс. руб.) |
Стои-мость с учётом скидки (тыс. руб.) |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
202 |
Монитор |
5 |
12 |
60 |
7 |
4,2 |
5,8 |
|
201 |
Клавиатура |
25 |
0,25 |
6,25 |
5 |
0,31 |
5,94 |
|
403 |
Дискета |
100 |
0,02 |
2 |
10 |
0,2 |
1,8 |
|
105 |
Принтер |
2 |
10 |
20 |
20 |
4 |
16 |
|
204 |
Сканер |
1 |
8 |
8 |
15 |
1,2 |
6,8 |
Расчетная формула: гр. 5=гр.3*гр.4; гр. 7=гр.5*гр. 6/100; гр. 8=гр. 5-гр.7
Задание 4
1. Построить таблицу по приведенной ниже форме.
2. Рассчитать сумму возврата кредита (гр. 6) при условии: если дата возврата фактическая не превышает договорную, то сумма возврата увеличивается на 40 % от суммы кредита (гр. 3), в противном случае сумма возврата увеличивается на 40 % плюс 1 % за каждый просроченный день.
3. Ввести текущее значение даты и времени между названием таблицы и ее шапкой (головкой).
4. Выполнить обрамление всей таблицы, убрать сетку.
5. Используя Мастер рисования, выделить тенью графу 2.
6. По данным граф 1, 3 и 6 таблицы построить гистограмму с легендой, заголовком, названием осей.
7. Убрать рамки у легенды и диаграммы.
8. Ввести в нижний колонтитул индекс группы, свою фамилию, имя, дату и имя файла.
9. Документ сохранить, организовать просмотр перед печатью.
10. В приложении Word создать документ, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.
Форма документа:
Таблица 7
Расчет возвратной суммы кредита
|
Наименование организации |
Дата получе-ния кредита |
Сумма кредита (млн.руб.) |
Дата возврата (по договору) |
Дата возврата (фактическая) |
Сумма возврата (млн. руб.) |
|
1 |
2 |
3 |
4 |
5 |
6 |
|
АО «Роника» |
05.12.98 |
200 |
04.03.99 |
22.02.99 |
280 |
|
СП «Изотоп» |
25.01.99 |
500 |
24.04.99 |
15.05.99 |
805 |
|
ООО «Термо» |
03.02.99 |
100 |
02.06.99 |
22.06.99 |
160 |
|
АОЗТ «Чипы» |
21.11.98 |
300 |
20.05.99 |
18.05.99 |
420 |
|
АО «Медицина» |
12.05.99 |
50 |
11.07.99 |
20.09.99 |
104,5 |
|
АО «Колос» |
08.04.99 |
150 |
07.10.99 |
12.10.99 |
217,5 |
Задание 5
Формы документов:
Таблица 8
Сведения о результатах экзаменационной сессии
|
1 курс |
2 курс |
3 курс |
4 курс |
5 курс |
|||||
|
Балл |
Кол-во оценок |
Балл |
Кол-во оценок |
Балл |
Кол-во оценок |
Балл |
Кол-во оценок |
Балл |
Кол-во оценок |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
5 |
23 |
5 |
27 |
5 |
32 |
5 |
28 |
5 |
34 |
|
4 |
57 |
4 |
60 |
4 |
58 |
4 |
63 |
4 |
62 |
|
3 |
18 |
3 |
14 |
3 |
10 |
3 |
9 |
3 |
6 |
|
2 |
7 |
2 |
9 |
2 |
4 |
2 |
1 |
2 |
Таблица 9
Средний балл по курсам и факультетам
|
Курс |
Средний балл |
Ранг |
|
1 |
2 |
3 |
|
Первый |
||
|
Второй |
||
|
Третий |
Окончание таблицы 9
|
1 |
2 |
3 |
|
Четвертый |
||
|
Пятый |
||
|
По факультету |
Задание 6
10. В приложении Word создать документ с заголовком диаграммы, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.
Формы документов:
Таблица 10
Свод лицевых счетов пенсионеров за январь
|
№ лицевого счёта |
Фамилия |
Сумма причита-ющейся пенсии |
Удержанияпо исполнит. документам |
Выплачено пенсионеру |
|
1 |
2 |
3 |
4 |
5 |
|
И1212 |
Иванов |
900 |
125 |
|
|
А1245 |
Антонов |
1200 |
200 |
|
|
П1270 |
Петров |
560 |
25 |
|
|
Д1645 |
Дубовицкий |
456 |
||
|
С1767 |
Сидоров |
304 |
100 |
Таблица 11
Свод лицевых счетов пенсионеров за февраль
|
№ лицевого |
Фамилия |
Сумма причита-ющейся пенсии |
Удержанияпо исполнит. документам |
Выплачено |
|
1 |
2 |
3 |
4 |
5 |
|
И1212 |
Иванов |
950 |
130 |
|
|
А1245 |
Антонов |
1250 |
210 |
|
|
П1270 |
Петров |
610 |
30 |
|
|
Д1645 |
Дубовицкий |
506 |
5 |
|
|
С1767 |
Сидоров |
374 |
100 |
Таблица 12
Свод лицевых счетов пенсионеров за январь и февраль
|
№ лицевого |
Фамилия |
Сумма причита-ющейся пенсии |
Удержанияпо исполнит. документам |
Выплачено |
|
1 |
2 |
3 |
4 |
5 |
|
И1212 |
Иванов |
1850 |
||
|
А1245 |
Антонов |
2450 |
||
|
П1270 |
Петров |
1170 |
||
|
Д1645 |
Дубовицкий |
962 |
||
|
С1767 |
Сидоров |
678 |
Задание 7
Формы документов:
Таблица 13
Справка по объему заказов за июнь 1999 г., млн руб.
|
Название |
Сумма |
Отклонение |
|
|
по плану |
фактическая |
||
|
1 |
2 |
3 |
4 |
|
СМУ-7 |
200 |
220 |
|
|
СМУ-24 |
450 |
460 |
|
|
СМУ-6 |
670 |
675 |
|
|
СМУ-7 |
720 |
710 |
|
|
СМУ-24 |
300 |
350 |
Таблица 14
Справка по объему заказов за июль 1999 г., млн руб.
|
Название |
Сумма |
Отклонение |
|
|
по плану |
фактическая |
||
|
1 |
2 |
3 |
4 |
|
СМУ-7 |
235 |
245 |
|
|
СМУ-2 |
154 |
161 |
|
|
СМУ-6 |
567 |
572 |
|
|
СМУ-75 |
455 |
459 |
|
|
СМУ-24 |
318 |
321 |
|
|
СМУ-3 |
675 |
682 |
|
|
СМУ-75 |
1234 |
1256 |
Таблица 15
Справка по объему заказов за июнь-июль 1999 г., млн руб.
|
Название |
Сумма |
Отклонение |
|
|
по плану |
фактическая |
||
|
1 |
2 |
3 |
4 |
|
СМУ-7 |
155 |
1175 |
20 |
|
СМУ-2 |
154 |
161 |
7 |
|
СМУ-6 |
1237 |
1247 |
10 |
|
СМУ-75 |
1689 |
1715 |
26 |
|
СМУ-24 |
1068 |
1131 |
63 |
|
СМУ-3 |
675 |
682 |
7 |
Задание 8
гр. 3 табл. 17=гр. 3табл.16*1,1 (если значение гр. 5 табл.16<100);
гр. 3 табл. 17=гр. 3табл. 16*1,05 (если значение гр. 5 табл. 16>100, но <105);
гр. 3 табл. 17=гр. 3табл. 16*1,01 (если значение гр. 5 табл. 16>105). Результат округлить до целого значения.
гр.4=гр.3 табл16/гр.3табл17*100. Результат округлить до целого значения.
10. В приложении Word создать документ с заголовком диаграммы, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.
Формы документов:
Таблица 16
Сведения о выполнении плана по себестоимости товарной продукции
|
№ п/п |
Наименование калькуляционных статей расходов |
Утверждено на 1999 г. (млн руб.) |
Выполнено в 1999 г. (млн руб.) |
Процент выполнения плана |
|
1 |
2 |
3 |
4 |
5 |
|
1. |
Сырьё и материалы |
3017 |
3121 |
103,4 |
|
2. |
Полуфабрикаты |
26335 |
26334 |
100 |
|
3. |
Топливо и энергия |
341 |
353 |
103,5 |
|
4. |
Зарплата производственных рабочих |
5670 |
3448 |
94 |
|
5. |
Цеховые расходы |
1738 |
1634 |
94 |
|
6. |
Общезаводские расходы |
2926 |
3109 |
106,3 |
|
7. |
Прочие расходы |
276 |
444 |
160,9 |
|
Итого |
38303 |
38443 |
100,4 |
Таблица 17
План себестоимости товарной продукции
|
№ п/п |
Наименование калькуляционных |
Запланировано на 2000 г. |
|
|
Сумма |
Процент |
||
|
1 |
2 |
3 |
4 |
|
1. |
Сырьё и материалы |
3277 |
|
|
2. |
Полуфабрикаты |
27651 |
|
|
3. |
Топливо и энергия |
371 |
|
|
4. |
Зарплата производственных рабочих |
4037 |
|
|
5. |
Цеховые расходы |
1912 |
|
|
6. |
Общезаводские расходы |
2955 |
|
|
7. |
Прочие расходы |
279 |
|
|
Итого |
40482 |
Задание 9
Таблица 18
Анализ дебиторской задолженности по срокам погашения, млн руб.
|
Отгрузка – всего |
Оплата |
|||||
|
Месяц |
Сумма |
Январь |
Февраль |
Март |
Апрель |
Май |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
|
Январь |
462000 |
46200 |
154800 |
184800 |
||
|
Февраль |
693000 |
89300 |
57200 |
208600 |
2500 |
|
|
Март |
646800 |
78600 |
48700 |
238500 |
||
|
Апрель |
548900 |
214100 |
10800 |
|||
|
Май |
478400 |
324000 |
||||
|
Итого |
2829100 |
46200 |
244100 |
520600 |
671400 |
75800 |
Таблица 19
Анализ дебиторской задолженности по срокам погашения, %
|
Отгрузка – всего |
Оплата, % от отгрузки |
||||||
|
Месяц |
Сумма |
Январь |
Февраль |
Март |
Апрель |
Май |
Задол-женность |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
Январь |
100 |
10 |
34 |
40 |
16 |
Окончание таблицы 19
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
Февраль |
100 |
13 |
37 |
30 |
20 |
||
|
|
100 |
2 |
8 |
37 |
13 |
||
|
Апрель |
100 |
39 |
59 |
||||
|
Май |
100 |
8 |
32 |
СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ
Содержание
|
Введение…………………………………………………………………. Основы работы в Excel…………………………………………………. 1. Основные понятия Excel………………………………….. 2. Выделение строк и столбцов………………………………………… 3. Типы данных………………………………………………………….. 4. Копирование, перемещение содержимого ячеек…………………… Упражнения……………………………………………………………… Контрольные задания…………………………………………………… Литература……………………………………………………………….. |
3 4 4 5 5 6 7 40 53 |
ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ
В ПРОФЕССИОНАЛЬНОЙ ДЕЯТЕЛЬНОСТИ
Учебное пособие
(практикум по Microsoft Excel)
Часть 1
Составитель
Киселева Людмила Николаевна
Редактор Г.А.Рахимова
Технический редактор А.М.Сибагатуллина
________________________________________________________________
Подписано к печати 06.05.2008 г. Формат 60х84 1/16.
Гарнитура «Таймс». Бумага писчая. Отпечатано на ризографе.
Уч.-изд. л. 3,5. Усл. печ. л. 3,2. Тираж 200 экз. Заказ № 22. Цена договорная.
Типография Башкирского экономико-юридического техникума.
450105, г. Уфа, ул. М. Рыльского, 9/1.