Тема 4. 2. Информационные объекты табличного документа и действия с ними

ТЕМА 4.2. ИНФОРМАЦИОННЫЕ ОБЪЕКТЫ ТАБЛИЧНОГО ДОКУМЕНТА И ДЕЙСТВИЯ С НИМИ

 

ОГЛАВЛЕНИЕ

4.2.1 Объекты табличного документа. 1

4.2.2. Рабочая книга. 2

4.2.3. Выделение объектов табличного документа. 2

4.2.3. Действия с листами. 4

4.2.4. Действия с объектами листа. 5

Форматирование ячеек. 5

Выравнивание данных в ячейках. 8

Установка шрифта, границы и заливки ячейки. 9

Установка готовых стилей ячеек и таблиц. 9

Действия со строками и столбцами. 10

Действия с диапазоном. 11

4.2.5 Ввод и редактирование данных. 12

Ввод постоянных значений. 12

Ввод последовательностей. 13

Автозаполнение данных. 13

Ввод формул. 16

Типы ссылок. 17

Копирование формул. 22

Практикум 4.2. 23

Задание 1. Срок реализации продукта. 23

Задание 2. Расчет затрат на командировки. 24

Задание 3. Таможня. 26

Задание 4. Расчет квартплаты.. 28

Цели изучения темы:

Познакомиться:

· с основными объектами табличного документа;

· с технологией редактирования и форматирования объектов;

· с технологией ввода формул;

· с технологией ввода относительных и абсолютных ссылок;

· с технологией ввода имени ячейки и диапазона.

Объекты табличного документа

Как и в Word 2007 все объекты Excel 2007 разделяются на две большие группы: объекты, созданные непосредственно в среде и внедренные объекты. На Рис. 1 представлены объекты, созданные в среде Microsoft Excel 2007. Внедренные объекты ничем не отличаются от тех, которые рассматривались в текстовом процессоре Word 2007: рисунки, клипы, фигурный текст и т.д.

Каждый объект характеризуется набором параметров и методов обработки.

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

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

Рис. 1. Объекты табличного документа

Рабочая книга

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

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

Технология работы с файлами является единой для всех приложений Microsoft Office 2007: все команды доступны после нажатия кнопки Office.

При выборе команды Создать и нажатии кнопки Новая книга будет создана книга со стандартным именем Книга1 и с параметрами, установленными в шаблоне по умолчанию. Следующая новая книга в том же сеансе работы будет открыта под именем Книга2 и т.д. Можно создать книгу и на основе готовых шаблонов: «Выписка по счету», «Авансовый отчет», «Личный бюджет» и т.д.

Пользователь имеет возможность настроить параметры новой книги с помощью команды Office/Параметры Excel.

Действия с листами

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

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

Количество листов, которое будет иметь новая книга, выставляется в окне Параметры ExcelкнопкиOffice, по умолчанию их 3.

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

Важной при работе с листами является команда Переместить/скопировать.

Технология перемещения или копирования листа

1. В контекстном меню ярлычка листа выберите команду Переместить/скопировать.

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

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

3. В разделе Перед листом укажите месторасположение листа.

4. Если необходимо сделать копию листа, то установите флажок Создавать копию.

5. Нажмите ОК.

Действия с объектами листа

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

Все действия с ячейкой: удаление, вставка, очистка, форматирование и т.д. сосредоточены в ее контекстном меню и разбросаны по группам Ячейки и Число вкладки Главная ( Рис. 3 ).

Форматирование ячеек

Для установки форматов данных в ячейке используется команда Форматячеек, которая использовалась еще в предыдущих версиях Excel.

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

После выбора команды на экране появляется окно Формат ячеек (Рис. 4), которое имеет несколько вкладок: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

С помощью вкладки Число можно выбрать нужныйформат представления данных в ячейке и его отредактировать. На (Рис. 5) представлено содержимое одной и той же ячейки в разных форматах.

Рис. 5. Представление содержимого ячейки в разных форматах

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

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

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

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

Запись числа в экспоненциальном формате 1,23Е+4 на языке математики означает 1,23·10⁴ .

Все форматы представления данных кроме формата Текстовый хранятся в Microsoft Excel 2007 в виде чисел.

Рис. 6. Соответствие между числовым форматом и форматами
Краткая дата и Время

Например, даты хранятся в виде последовательности номеров. 1-ый номер имеет дата 1.01.1900, 2-ой – 2.01.1900 и т.д., а значение времени хранится в виде дробной части (как часть суток). На (Рис. 6) показано соответствие между данными в числовом формате и данными в формате краткой даты и времени.

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

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

В группе Число вкладки Главная тоже расположены команды, которые позволяют управлять форматом ячейки. В верхнем списке группы отображается формат текущей ячейки листа (по умолчанию – Общий). Открыв список, можно выбрать для нее другой формат ( Рис. 7 ). Помимо списка форматов в группе размещены кнопки Увеличить разрядность и Уменьшить разрядность , которые служат для увеличения (уменьшения) количества знаков после запятой. С помощью кнопки Формат с разделителем можно ввести разделитель тысяч (отступ) при отображении числа.

Действия с диапазоном

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

Когда в диапазон входят смежные ячейки, например, B3, B4, B5 (ячейки одного столбца) или D2, E2, F2, G2 (ячейки одной строки), то такой диапазон обозначается при помощи ссылок на первую и последнюю ячейки, между которыми ставится знак «:». Для нашего примера – B3:B5, D2:G2 соответственно. Если в качестве диапазона рассматривается блок смежных ячеек, то для обозначения диапазона используются ссылки на диагональные ячейки, например E4:G6. На (Рис. 15) представлены разновидности диапазонов.

Рис. 15. Примеры диапазонов ячеек

 

Если же ячейки диапазона являются несмежными, т.е. выделены с помощью клавиши Ctrl, то ссылки на ячейки перечисляются через «;», например, A10;B5;C1.

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

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

 

Важным объектом табличного документа является диаграмма.

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

О создании диаграмм, их настройке будет рассказано в Теме 4.4.

 

Ввод постоянных значений

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

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

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

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

После ввода данных числа автоматически выравниваются по правому краю, а текст – по левому.

Особенности ввода данных:

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

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

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

Ввод последовательностей

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

Помимо этого в Excel есть встроенные списки последовательностей:

· понедельник, вторник, среда, четверг, пятница, суббота, воскресенье;

· пн, вт, ср, чт, пт, сб, вс;

· январь, февраль, март, апрель, май, июнь, июль, август, сентябрь, октябрь, ноябрь, декабрь;

· янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек.

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

Автозаполнение данных

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

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

На (Рис. 16) представлен фрагмент таблицы, демонстрирующий некоторые возможности функции автозаполнения. Из рисунка видно, что числовые и текстовые данные при использвании автозаполнения копируются без изменений (столбцы B, C), а текст с числом на конце (столбцы H, I) автоматически меняется. Дни недели, месяцы, даты, время тоже автоматически изменяются (столбцы D:F).

Технология заполнения ячеек с помощью функции автозаполнения:

1 способ. Заполнение повторяющимися значениями или значениями, отличающимисяна 1

1. Введите данные в первую ячейку диапазона.

2. Выделите первую ячейку.

3. При нажатой левой кнопке мыши протащите маркер заполнения по ячейкам диапазона.

4. В конце нужного диапазона отпустите кнопку мыши.

5. В контекстном меню Параметров автозаполнения выберите пункт (Рис. 17):

· «копировать ячейки» – будут копироваться и значения, и форматы исходной ячейки;

· «заполнить» - значения ячеек диапазона будут заполнены последовательностями чисел (отличающимися на 1!), дат и т.д., будут скопированы и форматы исходной ячейки;

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

· «заполнить только значения» – будет копироваться только значение исходной ячейки.

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

На (Рис. 17) приведен пример заполнения ячеек последовательными датами. В этом случае появляются дополнительные параметры заполнения: по дням, по рабочим дням, по месяцам, по годам. В данном примере в качестве параметра выбрано значение Заполнить по рабочим дням. Если выбрать «по месяцам», то будет последовательность, состоящая из разных месяцев, но с одинаковой датой. С годами будет аналогично.

2 способ. Заполнение значениями, отличающимися на фиксированную величину шага с помощью окна Прогрессия

1. Введите данные в первую ячейку диапазона.

2. Выделите первую ячейку.

3. При нажатой ПРАВОЙ кнопке мыши протащите маркер заполнения по ячейкам диапазона.

4. В конце нужного диапазона отпустите кнопку мыши.

5. В контекстном меню выберите пункт Прогрессия.

6. В открывшемся окне Прогрессия (Рис. 18) заполните поля Расположение, Тип, Единицы. В группе Тип выберите один из следующих вариантов:

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

Геометрическая — для создания последовательности, в которой каждое следующее значение умножается на значение поля Шаг.

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

Автозаполнение — для создания такой же последовательности, как и с помощью маркера заполнения.

7. Введите значение шага.

8. Нажмите ОК.

3 способ. Заполнение последовательности с помощью выделения двух последовательных значений.

1. Введите начальное значение в первую из заполняемых ячеек.

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

Например, если требуется задать последовательность 3, 6, 9, 12..., введите в первые две ячейки значения 3 и 6.

3. Протащите маркер заполнения по всем заполняемым ячейкам.

В Excel 2007 на вкладке Главная в группе Редактирование есть команда Заполнить , которая позволяет создавать последовательности повторяющихся значений с помощью кнопок Вверх, Вниз, Вправо, Влево и ряды с помощью кнопки Прогрессия.

Технология заполнения ячеек с помощью команды Заполнить:

1. Ведите значение в первую ячейку.

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

3. В группе Редактирование вкладки Главная выберите тип заполнения.

Примечание. Если выбрать команду Прогрессия, то дальнейшие действия аналогичны 2-му способу, рассмотренному выше (Рис. 18).

Для заполнения ячеек данными используется также и копирование. Обычное копирование выделенных фрагментов с данными через буфер обмена выполняется по известному алгоритму. После копирования можно вставлять не все данные, а по выбору (например, только форматы или только значения), если использовать команду Вставить из группы Буфер обмена вкладки Главная.

В ячейку можно добавить примечание. Примечание к ячейке – это текстовая информация, сопровождающая ячейку, помещенная в специальное текстовое поле и прикрепленная к ячейке неразрывной связью. Ячейка с примечаниями отличается наличием красного треугольника в правом верхнем углу. Когда курсор мыши наводится на этот угол, раскрывается текстовое поле примечания. Для добавления примечаний нужно в контекстном меню ячейки выбрать пункт «Вставить примечание».

Редактировать содержимое ячейки можно в строке формул или непосредственно в ячейке.

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

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

Ввод формул

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

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

В качестве операндов используются:

1. ссылки - адреса ячеек;

2. числа;

3. тексты (вводятся в двойных кавычках, например "Неявка");

4. встроенные функции Microsoft Excel;

5. логические значения (например, ИСТИНА и ЛОЖЬ, условия типа А23=А45 и т.д.);

6. значения ошибок типа (#ДЕЛ/О!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!).

При перечислении ссылки разделяются точкой с запятой, например: А4; С5; С10:Е20.

Операнды в формулах соединяются с помощью символов:

арифметических операций: + - сложение; – - (вычитание); / - деление; * - умножение; ^ - возведение в степень.

операций отношения: >- больше; >= не меньше; < - меньше; <= - не больше; = - равно; <> - (не равно).

Для ввода формулы необходимо:

1) Выделить ячейку.

2) Нажать клавишу «=» (равно);

3) Набрать формулу.

4) Нажать Enter.

После этого в ячейке с формулой появляется результат вычисления по формуле. Саму формулу можно увидеть в строке формул или в ячейке с формулой, предварительно выделив нужную ячейку и нажав F2. На (Рис. 20) представлена сама формула, введенная в ячейку E4 и результат вычислений по формуле.

Рис. 20. Результат ввода формулы

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

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

Показать или скрыть строку формул можно с помощью соответствующей команды в группе Показать или скрыть вкладки Вид или используя настройки Excel (вкладка Дополнительно, группа Экран).

Типы ссылок

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

· относительные ссылки;

· абсолютные ссылки;

· смешанные ссылки;

· имена диапазонов.

Относительные ссылки

Относительные ссылки – это ссылки, которые меняются в формуле вместе со своим местоположением.

При копировании формул, содержащих относительные ссылки, действует так называемое правило «относительной ориентации», взаимного расположения ячеек: в формуле с относительными ссылками запоминается «путь» до ячеек с исходной информацией и он учитывается при вычислении значений операндов в скопированной формуле. Поясним сказанное.

На (Рис. 21) представлен пример записи в ячейку E2 формулы, содержащей относительные ссылки на данные, хранящиеся в ячейках A1 и B1.

Ссылка A1 указывает, что первый операнд выбирается из ячейки на одну строку выше и на четыре столбца левее той, в которой находится формула (E2).

Ссылка B1 указывает, что второй операнд выбирается из ячейки на одну строку выше и на три столбца левее той, в которой находится формула (E2).

При вычислении значений после копирования формулы, первый операнд должен брать информацию из ячейки C3 (на одну строку выше и на четыре столбца левее той, в которой находится скопированная формула), а второй из ячейки D3 (на одну строку выше и на три столбца левее той, в которой находится скопированная формула). В результате в ячейке G4 появится формула: = C3*D3. На Рис. 22 показаны результаты вычислений при использовании относительной ссылки (E2) и результаты вычислений после копирования формулы (G4).

При копировании формулы с относительной ссылкой в новую книгу или лист перед именем операнда появляется имя книги и листа, откуда производилось копирование. Например, формула «=Информатика!F4*В4» означает, что значение ячейки F4, находящейся на листе Информатика, умножается на значение ячейки B4 текущего листа. Все происходит в пределах одной книги.

При копировании формулы с относительной ссылкой в смежные ячейки меняется номер строки, если происходит копирование вниз (вверх) и имя столбца, если происходит копирование вправо (влево). Из (Рис. 23) видно, что ссылка на ячейку C6 при копировании вниз изменяется: последовательно увеличивается номер строки: С7, С8.

Рис. 23. Копирование формул

Абсолютные ссылки

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

На (Рис. 24) видно, что адрес ячеек с исходной информацией (A1, B1) после копирования не меняется, так как знак $ стоит и перед именем столбца и перед номером строки.

Не меняется и результат вычислений (Рис. 25).

Знак $ можно ввести с клавиатуры или с помощью клавиши F4. Каждое нажатие F4 приводит к изменению вида ссылки. Например, если в формуле есть ссылка на A1, то щелкнув на A1 и многократно нажимая F4, получим последовательно следующие варианты ссылок: $А$1, A$1,$A1, A1.

Обычно абсолютные ссылки указывают на ячейки, в которых расположены константы. На (Рис. 23) приведен пример использования абсолютной ссылки при вычислении подоходного налога. В ячейке С3 хранится константа – 0,13.

Смешанные ссылки

Смешанные ссылки – это ссылки, которые при копировании изменяются частично. Символ $ стоит или перед именем столбца, или перед номером строки ($R2, F$5). Например, при копировании формулы A$1сохранится номер строки, а имя столбца изменится (Рис. 26).

Имена диапазонов

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

Использование имен упрощает понимание действий. На (Рис. 28) приведен пример использования имен ячеек: ячейке B2 присвоено имя Цена, а ячейке C2 – Количество. При выделении ячейки или диапазона ячеек имена отражаются в поле имени. В формуле ссылки на ячейки заменяются их именами (Рис. 27).

Для работы с именами используется группа команд Определенные имена вкладки Формулы (Рис. 29).

Рис. 29. Группа Определенные имена
вкладки Формула

Имена могут вводиться в диалоговое окно Создание имени (Рис. 30), которое появляется при вызове команды Формулы/Определенные имена/Присвоить имя или непосредственно в поле имени.

Из диалогового окна Создание имени видно, что присвоение имени аналогично абсолютной ссылке.

Чтобы сослаться на имя в формуле нужно вызвать команду Формулы/Определенные имена/Использовать в формуле.

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

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

1. Создать. Служит для вызова окна Создание имени.

2. Изменить. Инициализирует окно Изменение имени, полностью идентичное окну Создание имени.

3. Удалить. Удаляет указанное имя из списка имен.

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

Рис. 31. Диалоговое окно Диспетчер имен

При создании имени необходимо помнить следующее:

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

2. В качестве имени нельзя использовать служебные имена: R, r, C, c.

3. Имя может содержать до 255 символов.

4. Ссылки на ячейки не могут быть именами (Например, A$B1)

Технология создания имени

1 способ. С использованием поля Имя.

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

2. Щелкнуть по полю Имя.

3. Ввести имя диапазона или ячейки/

4. Нажать Enter.

2 способ. При помощи окна Создание имен.

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

2. Вызвать диалоговое окно Создание имени. Для этого на вкладке Формулы в группе Присвоенные имена нажать кнопку Присвоить имя.

3. В поле Имя ввести нужное имя.

4. Из списка Область выбрать книгу или имя листа.

5. В поле Примечание ввести пояснительную информацию.

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

7. Нажать ОК.

Использование имен обеспечивает следующие преимущества:

1. Формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки. Например, формула «=Доход-Расход» гораздо понятнее, чем формула «=A5-C5»

2. При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте — в определении имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

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

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

1. Путем непосредственного ввода ссылок с клавиатуры.

2. Указанием мыши на ячейку.

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

Копирование формул

Копирование формул можно осуществлять несколькими способами:

1. С помощью буфера обмена. Это универсальный способ копирования. Позволяет копировать формулы в смежные и несмежные ячейки.

2. С помощью маркера заполнения. Формула копируется в смежные ячейки. Для этого: выделяется ячейка с формулой и за маркер заполнения формула распространяется на нужный диапазон ячеек.

3. С помощью команды Вставить. Формула копируется в смежные ячейки. Для этого: выделяется ячейка с формулой и все заполняемые ячейки, затем с помощью команды Вставить из группы Редактировать вкладки Главная выбирается направление копирования.

 

Практикум 4.2.

Задание 1. Срок реализации продукта

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

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

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

В ходе выполнения задания будет использоваться формат данных – Дата. С данными типа Дата можно работать как с обыкновенными числами: их можно складывать, вычитать и т.д.

На (Рис. 33) представлен образец выполненного задания.

Рис. 33. Образец выполненного Задания 1

Технология выполнения задания

Часть 1. Автозаполнение. Вычисления по формулам

1. Прочитайте теоретический материал темы 4.2

2. Откройте книгу Заготовки Excel.

3. Перейдите к листу Продукция фабрик.

4. Рассмотрите примечания, которые всплывают при наведении указателя на красную метку.

5. Заполните столбцы D, E, используя автозаполнение и копирование данных.

6. Заполните столбец G датами сначала по дням, используя автозаполнение.

7. Заполните столбец G датами по месяцам. Для этого:

· удалите все даты, кроме первой;

· выделите все ячейки столбца вместе с начальной датой;

· откройте список команд кнопки Заполнить группы Редактировать вкладки Главная;

· выберите пункт Прогрессия;

· в диалоговом окне Прогрессия в разделе Тип установите переключатель даты, а в разделе Единицы – переключатель месяц;

· нажмите ОК.

8. Рассчитайте срок хранения в сутках. Для этого в ячейку H8 введите формулу =F8*30 (будем считать, что в месяце 30 дней).

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

10. Рассчитайте срок реализации продукта, для этого в ячейку I8 введите формулу =G8+H8.

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

12. Заполните столбец B последовательными номерами любым известным вам способом (введением формулы, используя команду, Прогрессия и т.д.)

Задание 2. Расчет затрат на командировки

Задание выполняется на основе данных, размещенных в файле Заготовки Excel на листе Отчет за командировку.

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

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

Рис. 34. Образец выполненного Задания 2

Технология выполнения задания

Часть 1. Расчет планируемых расходов на командировку

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

Сумма на командировку =
Количество дней*(Расходы + Гостиничные)+ Средн. стоимость проезда*2

1. Откройте книгу Заготовки_ Excel.

2. Перейдите на лист Отчет за командировку.

3. Ознакомьтесь с всплывающими примечаниями в правом верхнем углу ячеек.

4. Определите количество дней, которое сотрудник был в командировке. Для этого в ячейку G17 введите формулу =E17-D17.

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

6. Составьте формулу расчета стоимости проезда в оба конца, для этого в ячейку H17 введите формулу =F17*2.

7. Рассчитайте общую сумму суточных, используя абсолютные ссылки на ячейки с «суточными» и относительные на количество дней в командировке. Для этого в ячейку I17 введите формулу:
=G17*($C$11+$C$12)

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

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

9. Рассчитайте сумму, выдаваемую бухгалтерией. Для этого в ячейку J17 введите формулу: =H17+I17.

Часть 2. Расчет фактических расходов на командировку

Фактические расходы на командировку составляют затраты на проезд в оба конца (по предъявляемым билетам) плюс затраты на проживание (по предъявляе<