Ввод, редактирование и форматирование данных

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

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

Форматирование ячеек. Форматирование в Excel предполагает ряд действий по установке форматов данных, параметров шрифтов и выравнивания, границ, заливки. Форматируемые ячейки или диапазон должны быть выделены, затем меню ФОРМАТ ► ЯЧЕЙКИ вызывает диалоговое окно «Формат ячейки», имеющее шесть вкладок: ЧИСЛО, ВЫРАВНИВАНИЕ, ШРИФТ, ГРАНИЦА, ВИД, ЗАЩИТА устанавливаются необходимые параметры.

Вкладка ЧИСЛО позволяет установить формат ячейки как числовой. В поле «Числовые форматы» приведены различные типы форматов, которые выбираются щелчком мыши или с помощью клавиш управления курсором. Опишем числовые форматы.

«Числовой» — устанавливает число знаков после запятой и, при необходимости, разделитель групп разрядов (три разряда — пробел, начиная от запятой) удобен для представления чисел с фиксированной запятой, применимы все арифметические операции.

«Денежный» — число, которое сопровождается знаком валюты р., $, ?, ¥ и т.д. С точки зрения общего формата запись в ячейке 12,34$ является текстом, но если формат ячейки объявлен как денежный, то это число, с которым могут производиться арифметические действия.

«Финансовый» — то же, что и денежный, но выравнивание в столбцах происходит по запятой; удобен тем, что длина записи числа отражает ее величину, это снижает вероятность ошибки при просмотре документа.

«Дата» — позволяет отобразить дату по одному из шаблонов. Дата есть количество дней, прошедших с первого января 1900 г. С датой можно работать как с числом, т.е. если в ячейке А1 содержится дата 01.04.09, а в ячейке В1 мы наберем формулу = А1 + 1, то результат получится 02.04.09, но если ячейку А1 переформатировать в числовой формат, то получится 39904.

«Время» — служит для отображения времени по одному из предлагаемых шаблонов. Надо помнить, что время — это доля суток от 0 часов, т.е. число 0,25 в формате «дата- время» даст 6 часов утра, 0,5 — 12 часов.

«Процентный» — число в ячейке умножается на 100 и добавляется символ %.

«Дробный» — число представляется в виде обыкновенной дроби (приближенно) по шаблону

«Экспоненциальный» — число представляется в виде мантиссы и порядка; удобен для приближенных вычислений с плавающей запятой в различных научно-технических расчетах.

«Текстовый» — содержимое, каким бы оно ни было, воспринимается процессором как текст. Арифметические операции недопустимы.

«Дополнительный» — то же, что и текстовый, но текст форматируется по маске (образцу): номер телефона, почтовый индекс и т.д.

Вкладки ВЫРАВНИВАНИЕ и ШРИФТ. Некоторые команды этих вкладок дублируются на панели инструментов «Форматирование», аналогично диалоговым окнам выбора шрифта Word.

Вкладка ГРАНИЦА — позволяет установить толщину, форму, цвет линий границ любых ячеек. Сначала устанавливается «тип линии» и цвет, затем указывается, к каким границам выделенного диапазона их применить.

Вкладка ВИД — определяет цвет и узор заливки ячеек.

Вкладка ЗАЩИТА — позволяет установить или снять защиту с выделенного диапазона, изменить режим отображения формул. Обычно это делается перед тем как защитить весь лист, т.е. если на рабочем листе сделаны расчеты, то с исходных данных защиту снимают, а весь лист с формулами и промежуточными результатами защищается (меню СЕРВИС ► ЗАЩИТА ► ЗАЩИТИТЬ ЛИСТ...). Защищаемые ячейки становятся недоступными для редактирования.

7.3 Вычисления в таблицах

Формулы электронных таблиц аналогичны выражениям на языке программирования высокого уровня. Формула может содержать числовые константы, ссылки (адреса ячеек, содержимое которых участвует в вычислениях), функции. Указанные элементы соединяются знаками арифметических операций, например, ^ (возведение в степень), * (умножить), / (разделить), + (плюс), - (минус). Круглые скобки могут изменять принятый в арифметике порядок действий.

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

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

Например, пусть в ячейке А1 содержится число 10, в ячейке В1 число 20, в ячейку С1 введем формулу =А1+В1+2. После завершения ввода (нажатие Enter) в ячейке С1 появится результат сложения чисел, содержащихся в ячейках А1 и В1, увеличенный на 2, т.е. 32. Если изменить число, хранящееся в ячейке А1, например, заменить на 20, то наличие формулы в ячейке С1 автоматически изменит ее значение на 42.

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

Автозаполнение. Если необходимо продублировать какое-либо данное (числовое или текстовое) в ряд смежных ячеек в строке или столбце, то можно ввести одно данное, затем перетащить маркер заполнения ячейки с введенным данным по строке или столбцу, произойдет автоматическое копирование содержимого ячейки. Иногда требуется заполнить смежные ячейки не одинаковыми, но похожими данными, например, «1 блок», «2 блок», «3 блок» или «ряд 1», «ряд 2», «ряд 3». Делается это также с помощью маркера заполнения, сначала выделяются первые ячейки («1 блок», «2 блок»), затем диапазон протягивается за маркер заполнения.

Автозаполнение ячеек формулами. Активизируем ячейку С1 из предыдущего примера и протянем ее за маркер заполнения вниз по столбцу. В ячейку С2 автоматически введется формула =А2+В2+2, в С3 =А3+В3+2 и т.д. При протягивании по столбцу номер столбца увеличивается. Вернемся в ячейку С1 и протянем ее по строке, получим: в ячейке D1 =В1+С1 , в El =C1+D1 и т.д., при протягивании по строке номер строки увеличивается. Таким образом, при протягивании формулы ссылки А1 и В1 модифицируются, такие ссылки называются относительными.

Ссылка вида $С$1будет неизменна при любом протягивании. Такие ссылки называются абсолютными. Чтобы ссылка не модифицировалась при протягивании по строке, ее следует записать как С$1; запретить модификацию по столбцу можно, применив ссылку $С1. Такие ссылки называются смешанными. Циклическое переключение типа текущей ссылки осуществляется нажатием функциональной клавиши F4.

Весьма неудобно было бы постоянно помнить, по каким ссылкам какие параметры находятся (например, в $С$1 находится параметр Х, в $D$4 — У и т.п.). Для этого в Excel есть возможность именования ячеек и диапазонов. Меню ВСТАВКА ► ИМЯ ► ПРИСВОИТЬ (в Exсel 2007 – Формулы ► Определённые имена ► Диспетчер имён) вызывает диалоговое окно, в котором можно создать (и изменить) имя и использовать его вместо абсолютных ссылок. В том же окне при необходимости можно удалить уже существующее имя.

Ссылки на другие листы и книги. Для обращения к значению ячейки, расположенной на другом рабочем листе, нужно указать имя этого листа вместе с адресом соответствующей ячейки. Например, находясь на листе 1, можно ввести в ячейку А1 формулу =ЛИСТ4!ВЗ+1 для обращения к ячейке ВЗ на рабочем листе ЛИСТ4. Если в названии листа есть пробелы, то оно (название) заключается в кавычки. Связывание двух ячеек можно упростить, для чего на листе 1 в ячейке А1 набрать знак = , затем через корешок обратиться к листу 4 и щелкнуть по ячейке ВЗ.

Копирование ячеек, содержащих формулы. Техника копирования, перемещения, удаления ячеек, содержащих формулы, такая же, как и ячеек, содержащих данные. Чтобы скопировать содержимое ячейки или диапазона, нужно активизировать ячейку или выделить диапазон, вызвать меню ПРАВКА ► КОПИРОВАТЬ или правой кнопкой мыши вызвать контекстное меню пункт КОПИРОВАТЬ, активизировать ячейку, являющуюся началом диапазона, куда должны быть скопированы данные, меню ПРАВКА ► ВСТАВИТЬ или контекстное меню пункт ВСТАВИТЬ, затем нажать клавишу [Enter]. Но если в формуле содержатся относительные ссылки, то при копировании (а при использовании стиля ссылок R1C1 – и перемещении) они модифицируются.

Рассмотрим на примере. Пусть в ячейке С3 содержится формула = A1+$B1+C$1+$D$1. Скопируем её в ячейку Е6, т.е. на два столбца правей и на три строки ниже. Тогда все относительные адреса формулы в ячейке Е6 увеличатся на два по столбцу, на три по строке. В результате в ячейке Е6 получим формулу =C4+$B4+E$1+$D$1.

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

Удобно вводить функции с помощью МАСТЕРА ФУНКЦИЙ меню ВСТАВКА (в версии 2007 – вкладка Формулы ► Вставить функцию) или кнопки f_x. (В заголовке диалогового окна «Мастера функций» сообщается, что выполняется первый из двух шагов.) На первом шаге выбирается группа функций в поле «Категория» и затем сама функция в списке «Функция», после выбора которой имя функции со скобками заносится в строку формул. Следующий шаг – окно «Аргументы функции», в котором отображаются поля для ввода аргументов, краткое описание функции и аргументов, а также их текущие значения. Если аргумент является обязательным, то его название указывается жирным шрифтом.

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

СУММ(число1; число2; …) – суммирует аргументы. Например, СУММ(А1:В2;С3;4) определит сумму значений диапазона А1:В2;С3 и числа 4

МИН(число1; число2; …) – минимальное значение аргумента.

МАКС(число1; число2; …) – максимальное значение аргумента.

СРЗНАЧ(число1; число2; …) – среднее арифметическое значение аргумента.

СЧЁТ(число1; число2; …) – находит количество числовых значений аргумента.

ОСТАТ(число; делитель) – находит остаток от деления.

Среди логических функций отметим функцию, аналогичную оператору «ЕСЛИ-ТО-ИНАЧЕ»: ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).

Диаграммы

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

Строятся диаграммы с помощью Мастера (Конструктора) диаграмм. Вызывается через меню ВСТАВКА ► ДИАГРАММА или нажатием кнопки на панели инструментов. Построение (редактирование) диаграммы выполняется за четыре достаточно независимых друг от друга этапа, на которых выбираются тип диаграммы, исходные данные, параметры оформления и размещения.

ТИП диаграммы задаёт форму представления данных, выбирается из доступных стандартных вариантов (Гистограмма , График , Круговая , Линейчатая , Точечная , Кольцевая , Пузырьковая , Лепестковая и др.) или из файла шаблона.

ВЫБОР ДАННЫХ в простых случаях часто осуществляется заранее, путём выделения диапазона перед построением диаграммы. Но всегда остаётся возможность задания или уточнения диапазона (рядов данных) на данном этапе. Ряды данных могут быть не просто несмежными, но и вообще располагаться на разных листах.

ОФОРМЛЕНИЕ диаграммы заключается в выборе МАКЕТА (отображение Заголовков, Осей, Линий сетки, Легенды, Подписей данных, Таблицы данных и др.) и СТИЛЯ (форматирование – задание размера, цвета, формы, других эффектов отображения элементов диаграммы – текста, линий, фигур, фоновой заливки).

РАСПОЛОЖЕНИЕ диаграммы может задаваться как на имеющемся в книге рабочем листе, так и на специально создаваемом отдельном листе диаграммы.

Списки

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

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

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

Диалоговое окно Сортировка данных открывается командой меню ДАННЫЕ ► СОРТИРОВКА. В окне указываются поля, по значениям которых следует упорядочить записи (например, сначала по фамилии, затем по имени), и направление сортировки (по возрастанию или по убыванию).

Для автоматической фильтрации данных следует выделить заголовки списка и выполнить команду меню ДАННЫЕ ► ФИЛЬТР ► АВТОФИЛЬТР. После этого в таблице рядом с названиями полей появятся пиктограммы со стрелками – раскрывающиеся списки. Щелчок по одной из этих стрелок выводит меню с перечнем значений данного поля. Выбор какого-либо пункта из этого перечня задаёт условие отбора. После чего в списке отобразятся только те строки (записи), у которых значение данного поля совпадает с выбранным значением, т.е. произойдет фильтрация данных. При этом пиктограмма со стрелкой будет изображена другим цветом или знаком.

Если необходимо вернуться к полному списку, то нужно выбрать опцию ВСЕ в перечне значений поля, по которому выполнялась фильтрация. Здесь же есть опция УСЛОВИЕ, позволяющая задать числовое условие фильтрации. Например, выбрать все записи, значение поля у которых больше заданного, и т.п. Отключение фильтра выполняется той же командой меню, что и включение. Расширенный фильтр позволяет задавать диапазон фильтрации, указывать диапазон условий отбора и копировать полученные результаты в новое место.

На рисунке 7.1 приведен пример электронной таблицы, созданной при помощи Microsoft Office Excel. В ячейках Е4¸Е19 рассчитывается стоимость материалов на 100 пар обуви путем умножения данных в ячейках С4¸С19 на данные в ячейках D4¸D19. В ячейках Е4¸Е19 записаны формулы: Е4*D4, Е5*D5, … Е19*D19 хотя на экране дается уже результат вычисления. В ячейке Е20 вычисляется итог путем суммирования данных в ячейках Е4¸Е19. В ячейке записана формула суммы. После того, как таблица готова, можно менять в ней данные, а произведения и сумма будет рассчитываться, и изменяться автоматически.

7.6 Вопросы и тестовые задания для самоконтроля

1 Электронная таблица - это:

1) устройство ввода графической информации в ПК;

2) компьютерный эквивалент обычной таблицы;

3) устройство ввода числовой информации в ПК;

4) данные только числового типа.

2 Основным элементом электронных таблиц является:

1) ячейка (клетка);

2) столбец;

3) строка;

4) таблица.

3 Ячейка электронной таблицы определяется:

1) областью пересечения строк и столбцов;

2) именами столбцов;

3) номерами строк.

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

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

6 Перечислите и поясните существующие форматы представления символьных данных в ячейках электронной таблицы.

7 Что такое формула в электронной таблице и ее типы. Приведите примеры.

8 Что такое функция в электронной таблице и ее типы. Приведите примеры.

9 Как указывается блок (диапазон) ячеек при выполнении какой-либо команды?

10 Что такое абсолютная, относительная и смешанная ссылка?

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

12 При перемещении ячейки с формулой стиля А1...

1) не изменяются любые ссылки на ячейки, содержащиеся в формуле;

2) не изменяются только абсолютные ссылки, содержащиеся в формуле;

3) не изменяются только относительные ссылки, содержащиеся в формуле;

4) не изменяются только константы, содержащиеся в формуле;

13 При перемещении ячейки с формулой стиля R1C1...

1) не изменяются любые ссылки на ячейки, содержащиеся в формуле;

2) не изменяются только абсолютные ссылки, содержащиеся в формуле;

3) не изменяются только относительные ссылки, содержащиеся в формуле;

4) не изменяются только константы, содержащиеся в формуле.

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

1) не изменяется;

2) не изменяется только при абсолютных ссылках, содержащихся в формуле;

3) не изменяется только при относительных ссылках, содержащихся в формуле;

4) изменяется, если ссылки смешанные.

15 Программы Lotus 1-2-3 и Microsoft Office Excel являются

1) электронными таблицами;

2) текстовыми редакторами;

3) системами управления базами данных;

4) графическими редакторами.

16 Ссылка А1:С3 обозначает

1) диапазон из девяти ячеек;

2) диапазон из трёх ячеек (А1, В2 и С3);

3) диапазон из двух ячеек (А1 и С3);

4) диапазон, исключающий ячейки А1 и С3.

вернуться к содержанию