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

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

 

Введение в технологию табличного процессора Excel

 

Электронная таблица – самая распространенная и мощная информационная технология для профессиональной работы с данными. Первую электронную таблицу создали два студента Гарвардского университета (США) в 1979 году. Выполняя скучные экономические расчеты с помощью бухгалтерской книги, Дэн Бриклин и Боб Франкстон, который был знаком с программированием, создали первую электронную таблицу и назвали ее Visi Calc. Затем появились усовершенствованные версии электронных таблиц, например, Super Calc и др.

Следующим этапом в развитии электронных таблиц было создание фирмой Lotus в 1982 году программы Lotus 1-2-3, которая пользовалась огромным спросом.

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

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

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

Табличный процессор позволяет решать следующие задачи:

· Исследовать решения задач с помощью приемов «Что будет, если?», «Как сделать, чтобы?».

· Статистический анализ и прогноз состояния объектов.

· Задачи моделирования.

· Оптимизационные задачи.

· Обеспечивает работу с таблицей как с базой данных.

· Экономические задачи и др.

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

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

Основные понятия

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

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

Рабочая область таблицы состоит из строк и столбцов, имеющих свои имена. Имена строк – это их номера от 1 и далее. Имена столбцов – буквы латинского алфавита от A до Z, далее от AA до AZ, ВA до BZ и т.д. Максимальное число строк и столбцов определяется особенностями программы и может превышать 1 миллион ячеек, хотя на практике, в большинстве случаях, этого не требуется.

В современных версиях Excel (например, Excel 2002) имеются следующие ограничения: лист рабочей книги может иметь до 256 столбцов, пронумерованных от A до IV; число строк на листе не может быть более 65536; количество листов в рабочей книге ограничивается только доступной памяти и ресурсами системы, т.е. может исчисляться многими сотнями.

Пересечение строки и столбца образует ячейку, имеющую свой адрес. Адрес ячейки определяется номером столбца и номером строки, например, А2, С4 и т.д.

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

Внешние ссылки используются для организации межтабличных связей и могут указывать на ячейку других листов одной рабочей книги или ячейку любого листа другой рабочей книги. Например, внешние ссылки могут иметь вид: Лист5!А4, [Книга5.xls]!лист2!В6 и т.д. При загрузке таблицы, содержащей внешние ссылки, необходимо загрузить все связанные с ней таблицы.

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

Работа с блоками ячеек

С блоками ячеек можно выполнять следующие действия:

1)перемещать; 2)копировать; 3)удалять их содержимое; 4)присваивать им имена.

Типы входных данных в Excel

1. Символьные (текстовые) данные.

2. Числовые данные.

3. Даты.

4. Формулы.

5. Функции.

 

Упражнение 1.1.

Откройте табличный процессор Excel. В ячейку А1 введите текст «Введение в Excel» и нажмите клавишу Enter.

2. Увеличьте ширину ячейки двумя способами:

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

· Поместите курсор в ячейку А1, затем выберите пункт меню Формат/Столбец/Автоподбор ширины.

3. В ячейку В1 введите сегодняшнюю дату в виде 02.09.05 и нажмите клавишу Enter. Измените формат даты. Для этого:

· Поместите курсор в ячейку В1 и выберите пункт меню Формат ячейки.

· В появившемся окне во вкладке Число выберите Дата. Справа вам будут предложены форматы дат.

· Выберите пункт меню Формат ячейки. Выберите один из них на свое усмотрение и нажмите на кнопку ОК.

4. В ячейку С1 введите число 1, в ячейку С2 – число 2, в ячейку С3 – число 3 и так до числа 10. Выделите ячейки от С1 до С10. Отформатируйте числа. Для этого:

· Выберите пункт меню Формат ячейки.

· Во вкладке Число выберите пункт Числовой.

· В окне справа укажите количество десятичных знаков, равное 3 и нажмите кнопку ОК.

Введите последовательный ряд чисел от 1 до 10 в ячейки D1:D10, используя команду Заполнить. Для этого:

· Введите в ячейку D1 число1.

· Выделите ячейку D1.

· Выберите пункт меню Правка/Заполнить/Прогрессия.

· В появившемся окне Прогрессия выберите Постолбцам, Шаг 1,Тип – арифметическая, предельноезначение 10 и нажмите кнопку ОК.

· Задайте в ячейках D1:D10 количество знаков после запятой 2.

Последнее задание можно выполнить другим способом. Введите в ячейку Е1 число 1,00, в ячейку Е2 число 2,00. Выделите ячейки Е1 и Е2. Подведите курсор к нижнему правому углу выделенной области. Курсор превратится в маленький черный крестик. Нажмите левую кнопку мыши и протяните до десятой строки. Введите таким же способом в ячейки F1:F10 убывающий ряд чисел от 10,00 до 1,00.

5. Введите в ячейку G1 формулу: =E1*F1. Для этого поместите курсор в ячейку G1и поставьте знак равенства, который означает, что дальше последует формула. Поместите курсор в ячейку Е1, введите знак умножения, а затем поместите курсор в ячейку F1 и щелкните мышью.

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

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

6. Поместите курсор в ячейку G1, подведите мышь к нижнему правому углу, зацепите появившийся черный крестик мышью и протяните ее до десятой строки. В результате в ячейках G1:G10, будет результат перемножения столбцов Е1:Е10 и F1:F10. Для того чтобы увидеть, как подстраиваются адреса, выполните пункт меню Сервис/Параметры. В появившемся диалоговом окне поставьте галочку в окошке Формулы. Таким образом, Вы вошли в режим просмотра формул. В этом режиме можно просматривать формулы и редактировать формулы. Чтобы войти в основной режим, где можно увидеть результаты вычислений, нужно убрать галочку в окошке Формулы.

Относительная подстройка адресов (относительная адресация) происходит по правилу относительной ориентации клетки, которое гласит:

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

7. Чтобы запретить относительную подстройку адресов при копировании формул используется знак “$”. В этом случае используется абсолютная адресация.

Введите в ячейку Н1 формулу: =$E$1*$F$1 и нажмите клавишу Enter. С помощью черного крестика скопируйте эту формулу в ячейки Н2:Н10. В результате в ячейках Н1:Н10 будет одно и тоже число 10, являющееся результатом перемножения ячеек Е1 и F1. Просмотрите содержимое ячеек Н1:Н10 в режиме просмотра формул. Вводить абсолютные адреса можно также с помощью клавиши F4. Например, в ячейку Н1 можно было ввести формулу: =E1*F1, а затем поместить курсор на адрес E1 и нажать клавишу F4, переместить курсор на адрес F1 и также нажать клавишу F4. В результате формула будет иметь вид: =$E$1*$F$1. Такой прием значительно упрощает ввод формул, включающий относительные адреса.

Упражнение 1.2. Рассмотрим работу с блоками ячеек.

Выделите блок ячеек С1:Н10. Это можно выполнить либо с помощью мыши (удобно использовать, если блоки небольшие), либо путем выделения начального адреса (в нашем случае С1), а затем конечного адреса (в нашем случае Н10) при нажатой клавише Shift.

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

3. Выполните пункт 2, но при нажатой клавише Ctrl. Блок будет копироваться. Верните блок на прежнее место.

4. Для того чтобы очистить блок ячеек надо выделить его любым способом и нажать клавишу Del. Очистите блок А1:В10. Восстановите блок с помощью меню Правка/Отменить ввод.

5. Присвойте блоку ячеек Е1:Е10 имя Ряд1, а блоку F1:F10 – Ряд2. Для этого выделите блок Е1:Е10 и вызовите пункт меню Вставка/Имя/Присвоить. В окне Присвоение имени введите имя Ряд1 и нажмите кнопку Добавить, а затем кнопку ОК. Аналогично назовите блок F1:F10 именем Ряд2.

6. Установите курсор в ячейке I1. Вызовите Мастер функций (кнопка f_x), найдите категорию функций Математические и выберите функцию СУММ. В открывшемся окне СУММ поместите курсор в окошко Число1 и выполните команду Вставка/Имя/Вставить. Выберите имя Ряд1 и нажмите кнопку ОК. Аналогично в окошко Число2 вставьте имя Ряд2. В ячейке I1 появится число – 110, равное сумме значений чисел в ячейках E1:F10. Присвоение имен блокам ячеек широко используется для удобства работы с повторяющимися блоками ячеек, а также для удобства чтения результатов вычислений и отчетов.

Упражнение 1.3. Необходимо построить график функции , при а =35,12 и b =145,24. Значения x -аргумента функции изменяются в пределах от –10 до +10 с шагом h =0,5.

Решение данной задачи будем выполнять на втором листе Вашего файла. До настоящего момента он имел имя Книга1. Присвойте своему файлу имя «Введение в Excel», используя команду Сохранить как.

Технология решения задачи

1. Определите формат ячеек А1 и А2 как числовой с двумя знаками после запятой, и введите в ячейку А1 значение 35,12, а в ячейку А2 значение 145,24.

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

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

4. Определите формат ячеек В2:В42 как числовой с тремя знаками после запятой. Введите в ячейку В2 начальное значение аргумента –10, а в ячейку В3 следующее значение аргумента, равное числу –9,5. Выделите ячейки В2 и В3, а затем с помощью черного крестика заполните значения аргумента до конечного (строка 42).

5. Скопируйте формат этих ячеек с помощью кнопоки Формат по образцу. При этом мышь примет вид кисточки. Щелкните мышью по ячейке С2.

6. В ячейку С2 введите формулу:

=$a$1*sin($a$2*b2)+$a$2*cos($a$1*b2)

7. Затем с помощь черного крестика скопируйте формулу в ячейки С3:С42. Таким образом, Вы получили искомые значения заданной функции.

8. Выделите значения функции (ячейки С2:С42), и на их основе постройте график функции. Для этого щелкните по кнопке Мастер диаграмм на панели Нестандартная, затем следуйте указаниям мастера (тип – гладкие графики, ряды данных в столбцах). В результате получите следующий график:

 

 

Улучшите вид графика. Для этого:

1. Добавьте название графика, используя правую кнопку мыши для вызова контекстного меню (команда Параметры диаграммы/ Заголовки/ название диаграммы);

2. На ось Х вынесите полученные ранее значения аргумента функции (контекстное меню команда Исходные данные/Ряд/подписи оси Х введите все значения аргументов функции, выделив их мышкой в таблице);

3. Уберите заливку и границу с области построения (контекстное меню, команда Формат области построения);

4. Передвиньте ось ординат в координату (0;0) (щелкните по оси х правой кнопкой мыши и выберите Форматоси/Шкала/Пересечение с У в категории номер 21);

5. Определите легенду и разместите ее внизу графика. Легенда располагается над рядом значений функции, если ряды данных в столбцах и справа от ряда значений функции, если ряды данных в строках. В данном случае легенда расположена в ячейке С1. Чтобы увидеть легенду на графике, ее надо выделить до построения диаграммы вместе со значениями функции (С1:С42). Если диаграмма уже построена то, вызвав контекстное меню, выберите команду Исходные данные/Ряды, поставьте курсор в графу Имя и щелкните мышкой по ячейке С1.

Улучшенный график имеет следующий вид:

 

 

6. Проведите исследование функции. Для этого измените значения констант а и b: a =500, b =1000. Убедитесь, что при этом выполняется мгновенный пересчет значений функции.

7. Сохраните файл, используя кнопку на стандартной панели инструментов Сохранить.

Примечание: Для построения графиков нескольких функций в одних осях, необходимо:

· вычислить все ряды значений этих функций;

· выделить эти ряды и воспользоваться мастером диаграмм;

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

Индивидуальные задания.

Постройте на третьем листе Вашего файла «Введение в Excel»графики предложенных ниже функций. При этом для ввода степенной функции используйте мастер функций f_x ( категория Математические/Степень ), или на клавиатуре сочетание клавиш <Shift>+ 6 (после символа «^» поставьте число, указывающее степень). Значение π брать самим, равным 3,14. Для нахождения значений аргументов Х легче пользоваться Прогрессией.

 

№ задания	Функция	Интервал изменения аргумента х	Шаг	Значения параметров
	у1=(cos(apsinx)+lg(3+x)/cos(2+x) у2=х2(а+х)	[-1,1]	h=0,1	a=4,8; a=9,6
	у1=cos(ap+x)/(1+sin(0,55p+x) у2=х(а-х3)	[0,3]	h=0,25	a=0,25; a=0,5
	у1=27ax2/2,5+sin2x у2=(а- )4	[-1,1]	h=0,1	a=4,8; a=9,6
	у1=cos(ap+x)/(1+sin(0,55p+x)) у2=х3(а+х)3	[0,3]	h=0,25	a=0,25; a=0,5
	у1=(x2+1)(x-a)sin(ax2/(x+2)) у2=ах+х2	[-1,1]	h=0,2	a=2; a=10
	у1=364a2cos(ax)/(a2-1,8a+1,2) у2=(а-х)/	[1,3]	h=0,2	a=0,26; а=0,52
	у1=psin2(x2+a/2)+cosx у2=а3(х-а)	[0,3]	h=0,3	a=3,14; a=8,4
	у1=(sin(a-x2)-(a2+x2)1/2)/a2(1+x)1/2 у2=(а+х)	[0,1]	h=0,05	a=2,15; a=4,6
	у1=arctg(sin((a-x)/a)) у2=(ах+х2)ах	[-1,1]	h=0,05	a=3,63; a=8,65
	у1=sin2(0,8a+x)/(0,1p+x2) у2=а3(х-а)5	[0,p)	h=p/12	a=3,63; a=4,78

 

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

 

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

Связи между таблицами осуществляются с помощью внешних ссылок (адресов ячеек), которые содержат кроме имени столбца и номера строки имя файла и имя листа в нем, данные из которого используются. Например, ссылка [Svod.xls]!Лист5!С2 указывает, что будут использованы данные из ячейки С2, находящейся в файле Svod.xls на листе 5. Таблица, на которую есть внешние ссылки, называется дополнительной. Таблица, в которой есть внешние ссылки на другие таблицы, считается основной. При загрузке таблицы, содержащей внешние ссылки, необходимо загрузить все связанные с ней вспомогательные таблицы. Между таблицами возможны двусторонние связи, то есть первая таблица ссылается на вторую, а вторая может ссылаться на первую.

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

Упражнение 1.4. Необходимо создать ведомость назначения квартальной премии в фирме, исходя из средней заработной платы сотрудников за квартал. При этом руководство фирмы постановило:

· Сотрудникам, имеющим среднюю заработную плату менее 5000 рублей, назначается премия в размере100% от оклада;

· Сотрудникам, имеющим среднюю заработную плату более 5000 рублей, но менее 10000 рублей, назначается премия в размере 70% от оклада;

· Сотрудникам, имеющим среднюю заработную плату более 10000 рублей, назначается премия в размере 50% от оклада.

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

 

Рис. 1.1.

 

Для ее создания выполните следующие действия:

1. Откройте табличный процессорExcel. И сохраните Книгу под именем «Квартальная премия».