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

 

В любой ячейке 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.

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

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

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

 

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

 

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

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

Напомним, что ввод формулы всегда начинается со знака равенства, сопровождается появлением формулы в текущей ячейке и дублировании набора в строке формул, завершается нажатием клавиши 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; …) – находит количество числовых значений аргумента.

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

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