Способы копирования (специальная вставка)

Глава 2. Microsoft Office Excel 2007

Основы работы в EXCEL

Цель работы.Изучить:

· ввод и редактирование данных в ячейках;

· виды данных в ячейках;

· ввод формул;

· форматирование данных в ячейках;

· способы копирования (специальная вставка);

· автозаполнение рядов данных, прогрессия;

· адресация ячеек;

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

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

· копирование, перемещение данных;

· форматирование таблиц данных.

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

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

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

В табличном процессоре Excel двумерная электронная таблица называется рабочим листом (Sheet). Рабочий лист состоит из столбцов А, В, С,.... (всего 16384 столбцов) и строк 1,2,3, ..., 1048576. Место на пересечении строки и столбца называется клеткой, или ячейкой (Cell). Каждая ячейка имеет адрес: А1, С2, В12 и так далее. Количество листов в книге ограничено объемом доступной оперативной памяти компьютера.

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

Ввод и редактирование данных в ячейках

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

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

1. Два щелчка в ячейке, появится курсор ввода и можно редактировать содержимое ячейки;

2. Один щелчок в строке формул, появится курсор ввода в строке формул и можно редактировать содержимое ячейки;

3. Нажать функциональную клавишу F2, появится курсор ввода в текущей ячейке и можно редактировать содержимое ячейки.

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

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

На рис. 2.1.1 приведена ячейка В2, куда занесена формула =А2+100. Результат выполнения формулы будет число 300 и на результат наложен денежный числовой формат с числом десятичных знаков, равным двум. В итоге имеем окончательный результат в ячейке 300,00р.

Рис. 2.1.1. Пример задания в ячейке В2 формулы, результата реализации формулы (значения) и формаат (значения в формате).

Текст в ячейке начинается с буквы, может быть сочетание цифры и букв. Выравнивание текста в ячейке по умолчанию осуществляется по левому краю ячейки. Форматирование текста в ячейке после его ввода осуществляется через вызов диалогового окна «Формат ячейки». Это окно можно вызвать сочетанием клавиш «Ctrl+1». На рис. 2.1.2 приведено диалоговое окно «Формат ячейки», где можно регулировать выравнивание текста, изменять его ориентацию, устанавливать шрифт и осуществлять другие действия.

Рис. 2.1.2. Диалоговое окно «Формат ячеек»

Число начинается с цифры или знака. После ввода числа в ячейку оно выравнивается по умолчанию по правому краю ячейки (см. рис. 2.1.1).

Необходимо отметить ряд особенностей работы с числами.

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

Вторая особенность касается отображения числа в ячейке. Часто встречающаяся ошибка – отображение числа в ячейке принимают за округление числа. Например, введем число 14,357 в ячейку В2 (рис. 2.1.3.)

Рис. 2.1.3. Реальное число 14,357 в ячейке

Затем вызовем диалоговое окно «Формат ячейки» и в разделе «Число» установим число десятичных знаков, равное 2. Результат этих действий приведен на рис. 2.1.4.

Рис. 2.1.4. Отображение числа 14,357 в ячейке после задания числа десятичных знаков, равное 2

Как можно видеть в ячейке имеем число 14,36, а на самом деле число осталось таким же, каким и было, так как в строке формул имеем число 14,357. И поэтому все операции с этой ячейкой, это будут операции с числом 14,357, а не с числом 14,36. Если бы в диалоговом окне «Формат ячейки» было установлено число десятичных знаков равное 1, то в ячейке В2 можно увидеть число 14,4, но на самом деле в ячейке (в строе формул) по-прежнему будет число 14,357.

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

Функция округления имеет следующий синтаксис:

ОКРУГЛ(число;число_разрядов)

Введем эту функцию в ячейку В3 для округления числа в ячейке В2 до числа разрядов равного 1. Таким образом, в ячейке В3 будет функция

ОКРУГЛ(В2;1)

Результат выполнения функции приведен на рис. 2.1.5.

Рис. 2.1.5. Результат применения функции ОКРУГЛ

Если в ячейках В2 и В3 установить отображение чисел с числом десятичных знаков равное 1, то оба числа будет отображены как 14,4. Только в ячейке В2 реальное число останется как 14,357, а в ячейке В3 отображение числа 14,4 и его реальное значение будут совпадать.

Третья особенность по работе с числами в Excel касается установки его формата. Его установка осуществляется в окне «Формат ячеек», на закладке «Число» (рис. 2.1.6).

Рис. 2.1.6. Закладка «Число» в окне «Формат ячеек»

Так на рис. 2.1.1 на число в ячейке В2 наложен денежный (рублевый) формат. Не рекомендуется устанавливать формат числа самостоятельно путем ввода соответствующих символов. Это может привести к тому, что число преобразуется в текст. Исключение может быть для символа «%». Но надо помнить, что вводя в ячейку число с форматом, например, 0,5%, это будет соответствовать значению 0,005 (без наложенного процентного формата). Таким образом, в ячейку можно ввести сразу 0,5% или сначала ввести 0,005, а затем наложить на введенное значение процентный формат.

Четвертая особенность по работе с числами. Необходимо помнить, что для Excel дата – это число в формате даты. А раз это число, с ним можно совершать арифметические и логические операции. Например, можно вычитать одну дату из другой. На рис. 2.1.7 приведен пример вычитания двух чисел в формате даты, а результат вычитания имеет формат числа. Если результат получается в формате даты, то его переводят в формат числа через вызов окна «Формат ячеек» (Ctrl+1). В приведенном примере, если бы результат вычитания был бы представлен в формате даты, то в ячейке В3 было бы не 271, а дата 27.09.1900.

Рис. 2.1.7. Пример вычитания одной даты из другой

Формула заносится в ячейку, начиная со знака равенства «=». На рис. 2.1.1 и рис. 2.1.7 можно видеть, как занесенная в ячейку формула приводит к результату вычисления по формуле.

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

Способы копирования (специальная вставка)

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

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

Ввести в ячейку А1 число 0,5.

Ввести в ячейку В1 формулу А1+0,2 и наложить на результат процентный формат. В итоге получим результат, представленный на рис. 2.1.8.

Рис. 2.1.8. Результаты выполнения примера

Выполнить копирование ячейки В1 в ячейки и с условиями, указанными ниже. При копировании использовать контекстное меню и опцию «Специальная вставка». Итак использую опцию «Специальная вставка» скопировать из В1:

в С1 (выбрать действие вставить «все»);

в С2 (выбрать действие вставить «все»);

в С3 (выбрать действие вставить «значение»);

в С4 (выбрать действие вставить «форматы»);

в D2 (выбрать действие вставить «все»);

Ввести в ячейку С4 цифру 1.

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

Рис. 2.1.9. Пример реализаций опции «Специальная вставка»

Основной вывод, который можно сделать в том, что с помощью действия «Специальная вставка» можно копировать различное сочетание данных из ячейки.

При анализе полученных данных в ячейках С1, С2, D2 необходимо обратить внимание на полученные в результате копирования формулы и тогда станет ясно, почему результаты в этих ячейках не совпадают с результатом ячейки-источника В1.

В ячейку С3 вставили только значение из В1, таким образом ни формулы ни формата в этой ячейке не будет, а процентный формат в 100 раз больше его значения. Таким образом, переход от процентного формата числа (70%) к его значению в Excel осуществляется делением на 100 (0,07).

В ячейку С4 был скопирован только формат, т.е. ни формулы ни значения в этой ячейке нет, а только процентный формат. Поэтому, если вводить в эту ячейку число, ему сразу присваивается процентный формат. Следует заметить, что нажатие клавиши Del стирает из ячейки все данные кроме формата. Формат можно изменить, вызвав действием Ctrl+1 окно «Формат ячеек».

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

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

Например,

Январь, февраль, …

Пн, Вт, …

1, 2, …

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

Прогрессия

Инструмент «Прогрессия» полезный инструмент Excel, который может быть применен при работе с большим количеством чисел любого формата, располагающихся по арифметической или геометрической прогрессии.

Инструмент «Прогрессия» можно найти на закладке «Главная», в разделе «Редактирование», в действии «Заполнить». Вид окна инструмента приведен на рис. 2.1.10.

Рис. 2.1.10. Диалоговое окно «Прогрессия»

Интерес представляет реализация прогрессии чисел в формате даты. Предлагается выполнить такое задание. Необходимо составить последовательность дат через два дня на третий на период до конца 2011 года. Для этого ввести в ячейку А1 первое число в формате даты, например, 01.12.2010. Далее вызвать диалоговое окно «Прогрессия» и выполнить действия: Расположение - по столбцам, Тип - арифметическая, Единицы – день, шаг – 3, Предельное значение: 31.12.2011. Проверить полученный результат. Аналогично можно выполнять прогрессии с обычными числами.