Подготовка табличных материалов
Электронные таблицы Excel являются рациональным средством подготовки табличных материалов. При формировании первоначальной таблицы для ручного ввода исходных данных пользователю доступно все пространство электронных таблиц. Обычно ввод данных начинается с первого листа. Первоначально формируются шапка и боковик таблицы. Шапка таблицы записывается по горизонтали в ячейках первой строки, начиная с ячейки А1, если она в ней не размещается, то используются следующие строки. Боковик таблицы записывается по горизонтали, занимая первый столбец начиная с ячейки А2.
Ввод исходных данных может осуществляться с цифровой клавиатуры. При этом режиме внизу в левом углу ниже полосы прокрутки высвечивается режим работы Excel: Ввод. Для цифрового ввода данных в таблицу, имеющую шапку и боковик, достаточно нажать, на клавиатуре компьютера клавишу Num lock. Процесс ввода удобнее вести поколонно, так как после заполнения каждой ячейки столбца (после нажатия клавиши Enter) Excel автоматически переходит вниз на следующую ячейку. Изменение направления набора осуществляется с помощью стрелок, имеющихся на клавиатуре. Можно выбирать конкретную ячейку с помощью мыши, устанавливая ее в интересующую ячейку. Исправления введенной информации осуществляются путем установки мыши в ячейку с ошибочной записью и набором нового значения (предыдущее значение может быть удалено с помощь клавиши Del). Если расчеты по этой таблице ведутся постоянно, то целесообразно создать соответствующий шаблон с возможностью контроля вводимых данных.
|
|
|
Заполненная таблица является основой для проведения аналитических расчетов.
Автоматическое суммирование
Использование функции автоматического суммирования: Поставить курсор в ячейку суммы и нажать кнопку Автоматическое суммирование 
на панели инструментов. Программа сама выделит ячейки пунктирной рамкой, в строке обработки появится команда =СУММ(адр1:адр2), а после нажатия на клавишу Enter в ячейке появится сумма.
Задание для самостоятельной работы с комментариями
1. Уменьшить/увеличить размер столбца А.
Выделите столбец А, поместите курсор на границу заголовка выделенного столбца. Указатель мыши превратится в двунаправленную стрелку. Перетащите мышью правую границу этого столбца влево/ вправо, чтобы уменьшить/увеличить ширину этого столбца.
2. Набрать в ячейке А1 «Электронная таблица». Поместите курсор в ячейку А1 и наберите нужный текст.
3. В А2 число 56.
Поместите курсор в ячейку А2 и наберите 56.
4. В A3 число 2.
Поместите курсор в ячейку A3 и наберите 2.
5. В А4 записать сумму этих чисел.
Поместите курсор в ячейку А4 и наберите =56+2, нажмите Enter.
|
|
|
6. В А5 записать сумму в виде формулы.
3. Функции дат и времени[3]
Программа Excel для Windows использует систему dam 1900, хранит даты как ряд последовательных номеров: по умолчанию 1 января 1900 г. имеет номер 1, а 16 февраля 2001-го – номер 36 938, так как интервал в днях между этими датами составляет 36 938. Конец отсчета – 31 декабря 9999 г. Годы с 00 до 29 воспринимаются как 2000 и 2029, годы 1900-1929, 2030 год и далее надо вводить полностью.
Нумерация дат позволяет выполнять вычисления: находить разность дат, прибавлять к дате дни и вычитать.
Функция =ДАТА(год; месяц; день) возвращает День.Месяц.Год, то есть аргументы вводятся в обратной последовательности, а выводится европейский (русский) стандарт даты. Например, функция =ДАТА(2006;12;1) возвратит 01.12.06.
Функции =СЕГОДНЯ() и =ТДАТА() не имеют аргументов в скобках. Сверившись с системными часами компьютера, функция =СЕГОДНЯ() возвращает сегодняшнюю дату, а функция =ТДАТА() возвращает и текущую дату, и текущее время.
Рассмотрим случай, когда в ячейку С5 введена функция =ТДАТА() и формат ячейки настроен так, чтобы показывать дату и время, например 1.12.06 11:15. В других ячейках можно вывести избирательные сведения с помощью функций, ссылающихся на ячейку С5.
|
|
|
Функция =ДЕНЬНЕД(С5) выведет день недели 6 (шестой день недели – суббота).
Функция =ГОД(С5) выведет 2006.
Функция =МЕСЯЦ(С5) выведет 12 (декабрь).
Функция =ЧАС(С5) выведет 11 (часов).
Функция =МИНУТЫ(С5) выведет 15 (минут).
Формат значений времени и дат устанавливает команда Формат, Ячейки, Число, Дата (или Время) по образцам или обозначениям формата:
ДД.ММ.ГГ – означает без пробела День.Месяц.Год, разделенные точкой, без точки в конце, например 31.12.98 и 13.08.07;
ЧЧ:ММ:СС или ЧЧ:ММ (без пробела) – означает, что часы, минуты и секунды будут представлены в виде 11:35:22 или 23:40.
Вычисления по формулам с датами. Даты и время нельзя умножать, делить, возводить в квадрат. Но можно вычитать из одной даты другую, число дней можно прибавлять к дате и вычитать из даты. Чтобы узнать, сколько дней между датами, надо из поздней даты вычесть раннюю.
Число дней между датами вычисляется по формуле разности, с записью каждой даты в кавычках по шаблону ="ДД.ММ.ГТ"–"ДД.ММ.ГГ". Например, формула ="30.06.2012"–"01.09-2007"возвратит число 1764 – дни за 5 лет обучения в вузе с учетом високосных лет. Еще пример: срок завершения работ по контракту 31.12.2006, фактический срок завершения работ 14.11.2007. Опоздание в днях вычисляет формула ="14.11.2007"–"31.12.2006".
|
|
|
Формулы вычислений с датами удобно записывать с адресами ячеек, в которых введены даты. Например; формула =К4-С4 из адреса ячейки К4 с поздней датой вычитает адрес ячейки С4, содержащей раннюю дату. Кавычки к адресу ячейки при такой записи не нужны. Если в С40 ввести прибытие по расписанию 10:14, в D40 фактическое прибытие 17:08, то время опоздания составит =C40-D40.
Чтобы узнать дату через определенное число дней, дни можно прибавлять к дате и вычитать. Например, прибавить 60 дней ="1.01.2006"+60 или вычесть 100 дней =СЕГОДНЯ()-100.
При вычитании и сложении можно применять функцию =ДАТА(год;месяц;день), у которой обратный порядок аргументов, разделенных точкой с запятой. Например:
=ДАТА(1945;05;09)-ДАТА(1941;06;22).
Аргументы функции можно представить адресами ячеек, в которых записаны числа, например: =ДАТА(С40;D40;Е40).
5. Абсолютные и относительные адреса ячеек[4]
Формула или функция таблицы может содержать ссылки на адреса ячеек, откуда требуется взять данные для вычислений. Структура таблицы чаще всего однородна по столбцам (иногда по строкам). Однородность означает, что действие, записанное в формуле первой строки таблицы, как правило, повторится для ячеек в других строках той же колонки, но со смещением адресов ячеек, на которые формула ссылается.
При копировании формул табличный редактор учитывает это важное свойство таблиц. Копирование формулы, содержащей относительные ссылки, в новую ячейку автоматически перестраивает ссылки, указывая измененные адреса ячеек. Обычная адресация ссылок в формулах и функциях, которая перестраивает адреса относительно нового положения копии ячейки с формулой, называется относительной адресацией. Если в какой-то ячейке записана формула с адресами сомножителей =В2*С2, то ее копирование в ячейку того же столбца на строку ниже изменит записанные в формуле ссылки на адреса ячеек, увеличив номер строки на +1. Формула перестроится как =ВЗ*СЗ (относительно нового места).
Чтобы ссылки на адреса не изменялись при копировании формулы или функции в другую ячейку, используют абсолютную адресацию ячеек (абсолютные ссылки). Например, адрес ячейки с курсом валют на товарном счете будет использован ячейками строк разных товаров, цена которых дана в валюте. Абсолютная адресация, которая при копировании не перестраивается, устанавливается символом $, например $D$7. Возможна смешанная адресация. Например, ссылка на адрес Н$5 разрешает при копировании изменять имя столбца Н, а номер строки 5 остается одним и тем же. Символ $ с клавиатуры набирать не обязательно, надо поставить курсор на адрес в формуле и нажать клавишу F4. Ссылка на адрес D7 превратится в $D$7, а после еще одного нажатия в D$7 и т.д.
Другой вариант абсолютного адреса – дать имя ячейке или диапазону и сделать в формулах ссылку не на адреса, а на это имя командой Вставка, Имя, Вставить. Если, например, ячейке, где выполняется автосуммирование данных, присвоить имя Итого, то можно написать формулу =Н7/Итого. Имя ячейки Итого как абсолютный адрес будет использоваться для расчета долевой части каждой позиции в строках таблицы.
Дополнительно
6.1. Ячейки и их адресация[5]
Электронные таблицы состоят из столбцов и строк. Столбцы озаглавлены буквами латинского алфавита и их двухбуквенными комбинациями (А, В, С, ..., АА, ... IV). Строки озаглавлены цифрами (1,2,3...). Всего рабочий лист может содержать до 256 столбцов и до 65536 строк.
Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и номера строки, например А28, Р45 и т.п.
Формат указания адреса ячейки называется ссылкой. Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования всегда проводятся в активной ячейке. Адрес и содержимое текущей ячейки выводятся в строке ввода электронной таблицы. Переместить рамку активной ячейки можно при помощи клавиш управления курсором или мышью. Данные в ячейке могут быть основными, т. е. не зависящими от других значений ячеек в таблице, и производными, т. е. определяемыми по значениям других ячеек при помощи вычислений.
Диапазон (блок) ячеек. В электронных таблицах можно работать как с отдельными ячейками, так и с группой ячеек, которые образуют блок. В качестве блока может рассматриваться строка или часть строки, столбец или часть столбца, а также прямоугольник, состоящий из нескольких строк, столбцов или их частей. Адрес блока ячеек задается указанием ссылок первой и последней его ячеек, между которыми ставится разделительный символ, например двоеточие <:> или две точки <..>. Каждая команда табличного процессора требует указания блока ячеек, в отношении которого она будет выполнена.
Блок используемых ячеек можно выделить двумя путями: непосредственно набором с клавиатуры начального и конечного адресов ячеек, формирующих диапазон, либо выделением соответствующей части таблицы с помощью клавиш управления курсором.
Вычисления в Excel
Вычисления в электронных таблицах Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций.
Ссылки на ячейки можно задать разными способами. По умолчанию ссылки на ячейку в формулах рассматриваются как относительные.
Относительные ссылки – это ссылки, которые при копировании формулы изменяются автоматически в соответствии с относительным расположением исходной ячейки и создаваемой копией (Н4).
Абсолютные ссылки – это ссылки, которые при копировании не изменяются ($Н$4).
Смешанные ссылки – это ссылки, которые сочетают в себе и относительную и абсолютную адресацию ($Н4, Н$4).
Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу <F4>.
Функции в Excel предназначены для вычисления базовых величин, необходимых при проведении сложных финансовых, статистических, математических и т. д. расчетов. Методика использования функций требует соблюдения определенной технологии.
1. На рабочем листе в отдельных ячейках осуществляется подготовка значений основных аргументов функции.
2. Осуществляется вызов Мастера функции с помощью команды Вставка → Функция или нажатием одноименной кнопки на панели инструментов Стандартная < fx >.
3. Выполняется выбор категории функции. В списке Функция содержится полный перечень доступных функций выбранной категории. В нижней части окна приведен краткий синтаксис и справка о назначении выбираемой функции. Кнопка Справка вызывает экран справки для встроенной функции, на которой установлен курсор. Кнопка От мена прекращает работу Мастера функций. Кнопка Готово переносит в строку формулы синтаксическую конструкцию выбранной встроенной функции. При нажатии на кнопку <Далее> осуществляется переход к работе с диалоговым окном выбранной функции.
4. Выполняется выбор в списке требуемой функции, в результате выбора появляется диалоговое окно для ввода аргументов. Для каждой функции существует регламентированный по составу и формату значений перечень аргументов.
5. В поле ввода диалогового окна можно вводить как ссылки на адреса ячеек, содержащих собственно значения аргументов, так и сами значения аргументов.
6. Если аргумент является результатом расчета другой встроенной функции Excel, возможно организовать вычисление вложенной, встроенной функции путем вызова Мастера функции одноименной кнопкой, расположенной перед полем ввода аргументов.
7. Для отказа от работы со встроенной функцией нажимается кнопка Отмена.
8. Завершение ввода аргументов и запуск расчета значения встроенной функции выполняется нажатием кнопки Готово.
9. Формула начинается со знака = (равно). Далее следует имя функции, а в круглых скобках указываются аргументы в последовательности, соответствующей синтаксису функции. В качестве разделителей аргументов используется выбранный при настройке Windows разделитель, обычно это точка с запятой (;) или запятая (,).
Например, в ячейку С13 введена формула:
=ДОХОД(В16;В17;0.08;47.727;100;2;0).
Отдельные аргументы функции могут быть как константами, так и ссылками на адреса ячеек.
Подбор параметров Excel . Вычислительные возможности Excel позволяют решать как прямые, так и обратные задачи. Выполнять исследование области допустимых значений аргументов, подбирать значение аргументов под заданное значение функции.
Для подбора параметров используется команда Сервис → Подбор параметра. В диалоговом окне задается требуемое значение функции: в поле Изменение значения ячейки указывается адрес ячейки, содержащей значение одного из аргументов функции. Excel решает и обратную задачу: подбор значения аргумента для заданного значения функции. В случае успешного завершения подбора выводится окно, в котором указан результат – текущее значение функции для подобранного значения аргумента, новое значение аргумента функции содержится в соответствующей ячейке.
При нажатии кнопки ОК. подобранное значение аргумента сохраняется в ячейке аргумента, при нажатии кнопки Отмена происходит восстановление значения аргумента. При неуспешном завершении подбора параметра выдается соответствующее сообщение о невозможности подбора аргументов.
Диспетчер сценариев в Excel
Для вариантных финансовых расчетов, основанных на задании различных значений аргументов функции, целесообразно воспользоваться сценарным подходом, реализованным средствами Excel.
Диспетчер сценариев используется для создания списка значений для подстановки в изменяемые ячейки листа. Каждый сценарий является набором предположений, который можно использовать для прогнозирования результатов пересчета листа. Используя диспетчер сценариев, можно: создавать несколько сценариев, в каждом из которых содержится до 32 значений подстановки в ячейки листа; присваивать имена, сохранять и выполнять сценарии листа; создавать итоговые отчеты по сценариям; объединять сценарии; защищать сценарии от изменений; скрывать сценарии; автоматически отслеживать изменения сценария.
Сценарий – именованная совокупность значений изменяемых ячеек. Для ячеек, являющихся аргументами функций, можно задавать различные значения. Команда Сервис → Сценарий вызывает диалоговое окно Диспетчер сценариев для ячеек текущего рабочего листа.
В окне Сценарии представлен список сценариев текущего рабочего листа. Возможно объединение сценариев, находящихся в открытых книгах или на других листах текущей рабочей книги при нажатии кнопки Объединить. Для создания нового сценария следует нажать кнопку <Добавить>, при этом появляется новое диалоговое окно.
В поле Название сценария вводится имя нового сценария – последовательность символов, максимальная длина имени не более 255 знаков.
В окне Примечание можно записать поясняющий сценарий текст. По умолчанию сюда заносится имя пользователя и дата создания сценария. Имя пользователя можно изменить с помощью команды Сервис → Параметры, вкладка Общие, поле Имя пользователя.
С помощью переключателя Запретить изменения реализуется защита значений изменяемых ячеек от редактирования. Переключатель Скрыть позволяет не показывать имя сценария в списке. При нажатии на <ОК> появляется диалоговое окно для ввода значений изменяемых ячеек.
Для просмотра результатов подстановки значений изменяемых ячеек по определенному сценарию в диалоговом окне Диспетчера сценариев следует выбрать из списка имя сценария и нажать кнопку Вывести.
Excel выполняет подстановку значений изменяемых ячеек сценария и производит расчет значения функции. Все изменения будут отражены на рабочем листе в ячейках, содержащих формулы и имеющих ссылки на изменяемые ячейки сценария, новые результаты выводятся.
Кнопка Закрыть обеспечивает выход из окна Диспетчера сценариев, при этом в изменяемых ячейках сохраняются значения последнего участвовавшего в просмотре сценария. Кнопка Отчет предназначена для подготовки отчетов по сценариям, при ее нажатии появляется диалоговое окно для выбора типа итогового отчета.
В поле Ячейки результата указывается адрес ячеек, значения которых зависят от изменяемых ячеек сценариев.
Формируется два вида отчетов:
итоги сценария – табличный отчет, содержащий для каждого сценария состав изменяемых ячеек и значение выбранных результатных ячеек;
свободная таблица результатов подстановки значений в изменяемые ячейки и вычисления результатов подстановки.
Дата добавления: 2021-04-05; просмотров: 52; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!