Исследование основных приемов работы с ячейками таблицы Excel

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и далее двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).

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

На данные, расположенные в ячейках, можно ссылаться в формулах как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используют прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например А1:С15. Если требуется выделить прямоугольный диапазон ячеек, это можно сделать протягиванием указателя от одной угловой ячейки до противоположной по диагонали. Рамка текущей ячейки при этом расширяется, охватывая весь выбранный диапазон. Чтобы выбрать столбец или строку целиком, следует щелкнуть на заголовке столбца (строки). Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.

Отдельная ячейка может содержать данные, относящиеся к одному из трех типов (текст, число или формула), а также оставаться пустой. Программа Excel при сохранении рабочей книги записывает в файл только прямоугольную область рабочих листов, примыкающую к левому верхнему углу (ячейка А1) и содержащую все заполненные ячейки. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные рассматриваются как текст. Ввод формулы всегда начинается с символа "=" (знак равенства).

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

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

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

Вычисления в таблицах программы Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Скобки позволяют изменять стандартный порядок выполнения действий. Если ячейка содержит формулу, то в рабочем листе отображается текущий результат вычисления этой формулы. Если сделать ячейку текущей, то сама формула отображается в строке формул. Правило использования формул в программе Excel состоит в том, что если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию можно легко выполнить "в уме".Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности производимых в ней вычислений.

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

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

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как Al, $A$1, A$1 и $A1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный (смешанная адресация).

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

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

Передача информации через буфер обмена имеет в программе Excel определенные особенности, связанные со сложностью контроля над этой операцией. Вначале необходимо выделить копируемый (вырезаемый) диапазон и дать команду на его помещение в буфер обмена. Попытка выполнить любую другую операцию приводит к отмене начатого процесса копирования или перемещения. Однако утраты данных не происходит, поскольку "вырезанные" данные удаляются из места их исходного размещения только в момент выполнения вставки. Место вставки определяется путем указания ячейки, соответствующей верхнему левому углу диапазона, помещенного в буфер обмена, или путем выделения диапазона, который по размерам в точности равен копируемому. Вставка выполняется командой Правка ® Вставить. Для управления способом вставки можно использовать команду Правка ® Специальная вставка. В этом случае правила вставки данных из буфера обмена задаются в открывшемся диалоговом окне.

Задание 3.2.1. Отработать практически и отразить в отчете по ЛР все указанные действия по вводу данных в ячейки на листе Задание 1:

1) В ячейку А1 введите слово текст, в ячейку В2 введите число 13.

2) Отформатируйте строки 1, 2 и 3 следующим образом: тип шрифта Tahoma, размер 11 пт, выравнивание по центру, начертание полужирное и курсив. Для этого воспользуйтесь пунктами меню Формат ® Ячейки ® Выравнивание и Формат ® Ячейки ® Шрифт.

3) Присвойте ячейке С1 значение ячейки А1, указав в строке формул ее смешанный адрес A$1, нажимая на функциональную клавишу клавиатуры F4.

4) Скопируйте ячейку С1 в ячейку С2 путем ее перетаскивания, затем скопируйте ячейку С2 в ячейку Е1 через буфер обмена, и сравните данные находящиеся в ячейках С2 и Е1, определите почему это произошло.

5) В диапазон ячеек Е1:Е10 введите соответственно числа 1 – 10, присвойте ячейке Е11 с помощью строки формул значение суммы диапазона ячеек Е1:Е10, набрав формулу СУММ(Е1:Е10).

6) Измените данные в ячейке Е7, присвоив этой ячейке абсолютный адрес ячейки А1, и сравните результаты вычисления в ячейке Е11.

7) Вырежете строку 8 и вставьте ее в строку 22, и сравните результаты вычисления в ячейке Е11.

8) Курсором выберите диапазон ячеек H10:J21 и объедините эти ячейки. В данную объединенную ячейку введите дату своего рождения и отформатируйте по своему усмотрению.

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

10) Сделать выводы по результатам проделанной работы.

Задание 3.2.2. Отработать практически и отразить в отчете по ЛР все указанные действия по созданию таблицы, приведенной на рис. 2, и форматированию ячеек на листе Задание 2:

Рис. 2 – Создаваемая таблица

1) Оформите заголовок таблицы следующим образом: тип шрифта Times New Roman, размер 12 пт, начертание полужирное: выравнивание по горизонтали – по центру, по вертикали – по центру; установите переключатели Перенос по словам и Объединение ячеек, очертите границы таблицы и ячеек. Для этого:

- курсором выделите ячейки А1:D1, правкой кнопкой мыши вызовите контекстно-зависимое меню Формат ячеек;

- во вкладке Выравнивание, Шрифт и Граница выполните указанные действия;

- наберите текст заголовка и сохраните файл.

2) Заполните и оформите строку заголовков столбцов А3:D4 следующим образом: тип шрифта Tahoma, размер 11 пт, выравнивание по центру, начертание полужирное и курсив. Введите требуемые переменные. Для введения подстрочного индекса установите переключатель Подстрочный во вкладке Шрифт.

3) Введите в ячейку А4 данные, представляющие собой разность арифметической прогрессии, а ячейки А5:А13заполните путем копирования А4.

4) Заполните ячейки B4-B13 числами от 1 до 10 с шагом 1.

5) В ячейку С4 введите значение первого члена арифметической прогрессии. В ячейку С5 введите формулу n-го члена арифметической прогрессии и скопируйте ее в остальные нижние ячейки этого столбца, применив абсолютную ссылку на ячейку А4.

6) В ячейку- D4 введите формулу суммы n первых членов арифметической прогрессии с учетом адресов ячеек и скопируйте формулу в нижние ячейки.

7) Отформатируйте ячейки C4:D13, установив для них выравнивание: по горизонтали – По правому краю, по вертикали – По нижнему краю, а также задав число десятичных знаков = 3 во вкладке Число.

8) Выполните автоподбор ширины ячеек, выделив всю таблицу и воспользовавшись пунктом меню Формат ® Ячейки ® Выравнивание.

9) Скройте 12 строку, щелкнув по ней правой кнопкой мыши, и нажав в контекстно-зависимом меню Скрыть. Отобразите скрытую строку 12, выделив строки 11 и 13, далее аналогичным образом.

10) Скройте столбцы В и С и отобразите их.

11) Обрамите таблицу по образцу: ячейки C4:C13 сделайте ярко-зеленого цвета, тип узора - 12.50% серый, а ячейки D4:D13 сиреневого цвета, воспользовавшись пунктом меню Формат ® Ячейки ® Вид.

12) Сохраните изменения в файле.

13) Сделать выводы по результатам проделанной работы.

3.3. Исследованиесредств автоматизации ввода данных

В связи с тем, что таблицы часто содержат повторяющиеся или однотипные данные, программа Excel содержит средства автоматизации ввода. К числу предоставляемых средств относятся: автозавершение. автозаполнение и автозаполнение формулами.

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

Автозаполнение числами. Этот метод используется при работе с числами. В правом нижнем углу рамки текущей ячейки имеется черный квадратик - маркер заполнения. При наведении на него указатель мыши (он обычно имеет вид толстого белого креста) приобретает форму тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция "размножения" содержимого ячейки в горизонтальном или вертикальном направлении.

Если ячейка содержит число (в том числе дату, денежную сумму), то при перетаскивании маркера происходит копирование ячеек или их заполнение арифметической прогрессией. Для выбора способа автозаполнения следует производить специальное перетаскивание с использованием правой кнопки мыши. Пусть, например, ячейка А1 содержит число 1. Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши, перетащите маркер заполнения так, чтобы рамка охватила ячейки А1, В1 и С1 и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1, 2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка —> Заполнить —> Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке ОК автоматически заполняются ячейки в соответствии с заданными правилами.

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

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

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

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

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

Задание 3.3.1. Отработать практически на листе Задание 3 и отразить в отчете по ЛР все указанные действия по исследованию средств автоматизации:

1) Для исследования средств автозавершения ввода:

- в ячейки А3:А5 с помощью средств автоматизации введите слово текст;

- в ячейку А6 введите слово такси;

- в ячейки А7:А10 продолжите введение слова тест;

2) Для исследования средств автозаполнения числами:

- в ячейку М1 введите число 1;

- заполните ячейки М2:М10 числами путем перетаскивания маркера;

- в ячейку О1 введите число 1 и путем форматирования ячейки придайте ей денежное значение в долларах;

- заполните ячейки О2:О10 денежными значениями путем перетаскивания маркера;

- в ячейку Р1 введите число 2;

- заполните ячейки Р2:Р10 числами в геометрической прогрессии, задав команду Правка ® Заполнить ® Прогрессия;

- с помощью автозаполнения в ячейки А11:А19 введите нумерацию строк таблицы;

3) Для исследования средств автозаполнения формулами:

- в ячейку М11 с помощью мастера формул ввести произведение ячеек М1:М10;

- в ячейку О11 с помощью мастера формул ввести сумму ячеек О1:О10;

- в ячейку Р11 с помощью строки формул ввести сумму ячеек Р1:Р10;

4) сделать выводы по результатам проделанной работы.

Задание 3.3.2. Отработать практически на листе Задание 3 и отразить в отчете по ЛР все указанные действия по автовычислениям:

1) На листе Задание 3 своей рабочей книги создайте экзаменационную ведомость по дисциплине «Программное обеспечение ЭВМ» согласно рис. 2.

Рис. 2 – Пример экзаменационной ведомости

Для этого:

- внесите фамилии студентов вашей группы и поставьте им оценки по экзамену (задействуйте все оценки);

- рассчитайте количество полученных оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), воспользовавшись функцией СЧЕТЕСЛИ, выбрав Вставка ® функции ® Статистические;

- рассчитайте общее количество полученных оценок.

2) Отформатируйте экзаменационную ведомость, как текстовый документ.

3) Сделать выводы по результатам проделанной работы.