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

Задание формул, копирование формул, мастер функций, назначение и возможности в табличном процессоре Excel.

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

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

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

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

2. Разработка электронной таблицы, т.е. создание заголовка, шапки, внесение формул в расчетные колонки.

3. Ввод данных и получение расчетных значений.

4. Сохранение таблицы на внешнем носителе.

5. Вывод таблицы на печать.

После запуска Excel указатель ячейки, как правило, находится в ячейке А1 рабочего листа с именем Лист1. Данная ячейка является активной, в которую пользователь может вводить данные. В ячейки листа можно вводить два типа данных: константы и формулы. Константы разделяются на три основные категории: числовые значения, текстовые значения и значения дат и времени. Числовые значения могут содержать только цифры от 0 до 9 и специальные символы: + - Е ( ) . , % /. Текстовое значение может содержать практически любые символы.

Ввод константы

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

Зафиксировать ввод данных в ячейку можно следующими способами:

1) нажатием клавиши [Enter], после чего курсор переместится в следующую ячейку. Направление перехода к другой ячейке после ввода можно задать на вкладке Правка диалогового окна Параметры,открыв его по команде Сервис►Параметры;

2) с помощью клавиш управления курсором [¬], [­], [®], [¯] клавиатуры;

3) щелком левой клавишей мыши на другой ячейке рабочего листа.

Управление отображением текстовых значений в ячейках

 Если длина вводимого текста превышает ширину ячейки, то он будет выступать за правый край ячейки, как показано на рис. 5.2, и располагаться поверх соседней ячейки, пока последняя свободна. Но текст при этом хранится в одной ячейке (в данном примере – в ячейке А1).

 Чтобы отобразить длинный текст в одной ячейке можно увеличить ширину столбца, в котором находится эта ячейка. Для этого можно воспользоваться одним из способов: выделить ячейку и выполнить команду Формат►Столбец► Автоподбор ширины(рис. 5.3). То же сделает двойной щелчок мыши на правой границе заголовка столбца; или вручную, переместив мышью правую границу заголовка столбца, когда курсор приобрел вид двунаправленной стрелки.

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

разместить текст в несколько строк. Для этого нужно выделить ячейку, выполнить команду Формат► Ячейки►вкладка Выравнивание►флажок Переносить по словам, затем нажать [ОК]. На рис.5.4. показано, как будет выглядеть ячейка после такого преобразования. Также на вкладке Выравнивание окна Формат ячеек можно из открывающихся списков выбрать способ выравнивания содержимого ячеек по горизонтали и по вертикалии ориентацию текста в ячейке.

Управление отображением текстовых значений в ячейках

 Значения, которые хранятся в ячейках и появляются в строке формул, называются хранимыми значениями. Если в ячейку вводится длинное числовое значение, то Excel использует экспоненциальное представление числа. При этом точность значения выбирается такой, чтобы число можно было отобразить в ячейке. Например, если ввести в ячейку стандартной ширины число 1238567890123, то оно отобразится в виде 1,24Е+12.

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

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

Наиболее распространенные форматы представления числовых данных:

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

Числовой формат с фиксированным количеством десятичных знаков обеспечивает представление чисел в ячейках с заданной точностью.

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

Денежный формат обеспечивает такое представление чисел, где каждые три разряда разделены определенным разделителем. Добавляется также знак валюты страны - р.

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

- Выравнивание к левому краю ячейки.

- Выравнивание к правому краю.

- Выравнивание по центру ячейки.

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

 

Редактирование данных

 Для удаления содержимого одной или нескольких ячеек необходимо выделить ячейку (или блок ячеек) и выполнить команду Правка►Очистить► Содержимое или нажать [Delete] на клавиатуре.

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

Рассмотрим порядок создания и сохранения табл. 5.1 (исходные данные пока вводить не будем).

1. Загрузите Excel командой ПускПрограммыMicrosoft Excel.

2. Переименуйте Лист1. Для этого выполните команду Формат►Лист► Переименовать и измените имя листа Лист1 на Товары.

3. Введите заголовок таблицы Учет движения материалов на складах в ячейку с адресом А1. Для расположения заголовка по центру таблицы (рис. 5.5) выделите диапазон ячеек А1:F1 (т.к. таблица будет содержать 6 столбцов от А до F) и выполните команду Формат►Ячейки►вкладка Выравнивание. В поле по горизонтали выберите из списка – по центру выделения,в полепо вертикали – по центруинажмите [ОК].

4. Введите названия столбцов таблицы. Для этого:

 Объедините ячейки А2иА3, для чего выделите их, и выполните команду Формат►Ячейки►вкладкаВыравнивание,установите флажкиобъединение ячеекипереносить по словам. В списках по вертикали и по горизонтали установите выравнивание для заголовка первого столбца таблицы – по центру. Нажмите [ОК]. Затем в объединенные ячейки введите название № склада.

 Аналогичные действия выполните для ввода названия второго столбца таблицы – Код материала, объединив ячейки В2 и В3.

 Введите в ячейку С2заголовок Количество, шт. и расположите его по центру четырех столбцов. Для этого выделите диапазон С2:F2 и выполните команду Формат►Ячейки►вкладка Выравнивание, установите в поле по горизонталипо центру выделения, в поле по вертикалипо центру.

Примечание. Команда Формат►Ячейки►вкладка Выравнивание, установка в поле Выравнивание по горизонтали варианта по центру выделенияможет быть заменена использованием кнопки [Объединить и поместить в центре]

 В ячейки С3, D3, Е3, F3 введите соответствующие названия столбцов. Для ячеек C3 и F3 выполните команду Формат►Ячейки►вкладка Выравнивание, установите по горизонтали и по вертикали – по центру, а также флажок переносить по словам, чтобы текст распределился внутри выделенного диапазона в несколько строк.

6. В ячейку А9 введите – ИТОГО.

7. Выполните шрифтовое оформление ячеек для заголовка и «шапки» таблицы. Выделите ячейки А1:F3 и выполните Формат►Ячейки►вкладка Шрифт. Выберите в поле ШрифтArial, Начертаниекурсив, Размер шрифта12 пт. Для ячеек А4:F9 выберите Шрифт Arial, Начертаниеобычный, Размер шрифта12 пт.

8. Для задания обрамления таблицы выделите диапазон ячеек А2:F9 и выполните команду Формат►Ячейки►вкладка Граница. Выберите из списков тип и цвет линии, для указания расположения границ выполните щелчок на кнопках [Внешние] и [Внутренние] и нажмите [ОК].

9. Активизируйте Лист2, переименуйте его на Цены и создайте табл.5.2. Вид спроектированной таблицы представлен на рис. 5.6. Обратите внимание, что шапка таблицы размещена в одной строке.

 

Третьим типом данных, которые могут быть введены в ячейки рабочего листа, являются формулы. Excel распознает в ячейке формулу, если значение в ячейке начинается со знака равенства (=), знака плюс (+) или знака минус (-).

При вводе формулы необходимо учитывать, что:

формуле всегда должен предшествовать знак «=». Можно начать ввод формулы со знака плюс или минус, тогда Excel преобразует введенное выражение в соответствующую формулу, например, если ввести +А1+А2, то оно преобразуется в формулу вида =+А1+А2;

в формуле нельзя использовать символы русского алфавита

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

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

Арифметические операции можно осуществлять только над числами и результатом всегда является число. Рассмотрим арифметические операторы, используемые Excel.

Обозначение оператора Действие Примеры использования операторов Результат вычисления
+ (сложение) складывается два арифметических выражения =15+34 =А1+В7 сумма арифметических выражений ячеек А1 и В7
- (вычитание) вычитается из первого арифметического выражения второе =107-45 =C3-D1 разность арифметических выражений ячеек С3 и D1
/ (деление) делится первое арифметическое выражение на второе =64/4 =F12/11 частное арифметического выражения ячейки F12 и числа 11
* (умножение) перемножаются два арифметических выражения =15*13 =G2*G3 произведение арифметических выражений ячеек G2 и G3
% (процент) вычисляется процент от арифметического выражения =1% =A1% 0,01 процент от арифметического выражения ячейки А1
^ (возведение в степень) возводит первое арифметическое выражение в степень, заданную вторым арифметическим выражением =5^8 =D1^2 арифметическое выражение ячейки D1 в степени 2

Операторы сравнения используются для сравнения как числовых, так и текстовых значений, а результатом операций сравнения всегда являются логические значения ИСТИНА или ЛОЖЬ. В Excel используется шесть операторов сравнения.

Обозначение оператора Действие Примеры использования операторов Результат вычисления
= равно =F1=F2     =12=12 Проверяется равенство значений ячеек F1 и F2 ИСТИНА
> больше, чем =F1>F2     =34>57 Проверяется значение ячейки F1 больше, чем F2 ЛОЖЬ
< меньше, чем =F1<F2     =7<7 Проверяется значение ячейки F1 меньше, чем F2 ЛОЖЬ
>= больше или равно =F1>=F2     =234>=124 Проверяется значение ячейки F1 больше или равно F2 ИСТИНА
<= меньше или равно =F1<=F2     =567<=1789 Проверяется значение ячейки F1 меньше или равно F2 ИСТИНА
<> не равно =F1<>F2   =15<>13 Проверяется неравенство значений ячеек F1 и F2 ИСТИНА

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

Обозначение оператора Действие Примеры использования операторов Результат вычисления
& (соединение текстовых строк) присоединяет вторую текстовую строку к концу первой текстовой строки =А2&В2   =”табличный процессор”&”MS Excel 97” =12&56 Создается текстовая строка, как результат присоединения содержимого ячейки В2 к концу содержимого ячейки А2   Табличный процессор MS Excel 97

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

Обозначение оператора Действие Примеры использования операторов Результат вычисления
: (диапазон) ссылается на ячейки диапазона между указанными начальной и конечной ячейкой =СУММ(А1:А10)   Суммируются арифметические выражения ячеек от А1 до А10
; (объединение) ссылается на объединение указанных ячеек (диапазонов ячеек) =СУММ(В1;В2;С1;С2)   =СУММ(А1:А5;С1:С3) Суммируются арифметические выражения ячеек В1, В2, С1, С2 Суммируются арифметические выражения диапазонов ячеек А1:А5 и С1:С3
пробел (пересечение) ссылается на пересечение указанных диапазонов ячеек =СУММ(А1:В10 В1:С10)   Суммируются арифметические выражения ячеек, общих для диапазонов А1:В10, В1:С10, т.е. В1:В10

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

Оператор Обозначение Приоритет
Оператор диапазона :
Операторы объединения и пересечения ; пробел
Оператор процентов %
Оператор возведения в степень ^
Операторы умножения и деления * /
Операторы сложения и вычитания + -
Текстовый оператор &
Операторы сравнения = > < >= <= <>

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

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

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

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

Аргументами функций могут быть числовые и текстовые значения, логические значения, массивы, значения ошибок, или они могут вовсе отсутствовать для некоторых функций. Например, функция СУММ(А1:А10) суммирует значения диапазона ячеек А1:А10; ЧИСТРАБДНИ("10.05.1999";"20.05.1999") вычисляет количество рабочих дней между двумя датами, а функция СЕГОДНЯ() – текущую дату.

Обязательным при вводе функции является:

1.предшествующий ей знак =;

2.круглые скобки, в которые заключаются аргументы функции, причем скобки нужны даже в том случае, если для функции не требуется задавать список аргументов, как для функции СЕГОДНЯ();

3.отсутствие пробела между именем функции и списком аргументов;

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

 

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

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

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная.

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

$В$5; $D$12 — полные абсолютные ссылки.

Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца.

В$5, DS12 — частичная абсолютная ссылка, не меняется номер строки; $В5, S$12 — частичная абсолютная ссылка, не меняется наименование столбца.

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