 |
Лабораторная работа №5. Ввод данных и использование формул в Microsoft Excel 2007
|
|
|
|
Целью лабораторной работы является изучение и закрепление навыков работы по вводу данных и использованию формул в Microsoft Excel 2007 [15].
Ввод данных в электронную таблицу
В ячейках электронной таблицы могут находиться данные трех типов: числовые значения (включая время и дату), текст, формулы. На рабочем листе, но в «графическом слое» поверх листа, могут также находиться рисунки, диаграммы, изображения, кнопки и другие объекты.
Ввод чисел
Числа вводятся с помощью верхнего ряда клавиатуры или числовой клавиатуры. В качестве десятичного разделителя применяется запятая или точка, можно вводить знаки денежных единиц. Если перед числом ввести «минус» или скобки, то оно считается отрицательным. Нули, набранные перед числом, игнорируются программой. Если необходимо получить значение с нулями впереди, его необходимо интерпретировать как текстовое.
Для представления чисел в Excel используется 15 цифр, при вводе числа из 16 цифр оно автоматически сохранится с точностью до 15 цифр. Числовые значения автоматически выравниваются по правой границе ячейки.
Ввод значений дат и времени
Excel для представления дат использует внутреннюю систему порядковой нумерации дат. (Так, самая ранняя дата, которую может распознать программа, – 1 января 1900 года, этой дате присвоен порядковый номер 1, следующей дате – порядковый номер 2 и т. д.). Даты вводятся в привычном для пользователя формате и распознаются автоматически. Временные значения также вводятся в одном из распознаваемом форматов времени. Представление даты и времени непосредственно на листе регулируется заданием формата отображения ячейки.
Ввод текста
Как текстовые значения воспринимаются все введенные данные, не распознаваемые как числа или формулы. Текстовые значения выравниваются по левой границе таблицы. Если текст не умещается в одной ячейке, то он располагается поверх соседних ячеек, если они свободны. Параметры расположения текста в ячейке задаются через формат ячеек.
|
|
|
Ввод формулы
Формулой считается любое математическое выражение. Формула всегда начинается со знака «=», может включать в себя, кроме операторов и ссылок на ячейки, встроенные функции Excel.
Форматы данных
После ввода в ячейку данных, Excel автоматически старается определить их тип и присвоить ячейке соответствующий формат – форму представления данных. Важно назначить правильный формат ячейки, чтобы, например, ячейка могла участвовать в вычислениях (быть не текстовой).
В Excel имеется набор стандартных форматов ячеек, которые могут применяться во всех книгах (рисунок 2.2.17). Активизировать его можно, выбрав Главная – Число – Числовой формат, либо по контекстному меню для выделенной ячейки на вкладке Число окна Формат ячеек.
Рисунок 2.2.17. Стандартные форматы
Изначально все ячейки таблицы имеют формат Общий. Использование форматов влияет на то, как будет отображаться содержимое в ячейках: общий – числа отображаются в виде целых чисел, десятичных дробей, если число слишком большое, то в виде экспоненциального; числовой – стандартный числовой формат; финансовый и денежный – число округляется до 2 знаков после запятой, после числа ставится знак денежной единицы, денежный формат позволяет отображать отрицательные суммы без знака «минус» и другим цветом; краткая дата и длинный формат даты – позволяет выбрать один из форматов дат; время – предоставляет на выбор несколько форматов времени; - процентный – число (от 0 до 1) в ячейке умножается на 100, округляется до целого и записывается со знаком %; дробный – используется для отображения чисел в виде не десятичной, а обыкновенной дроби; экспоненциальный – предназначен для отображения чисел в виде произведения двух составляющих: числа от 0 до 10 и степени числа 10 (положительной или отрицательной); текстовый – при установке этого формата любое введенное значение будет восприниматься как текстовое; дополнительный – включает в себя форматы Почтовый индекс, Индекс+4, Номер телефона, Табельный номер; все форматы – позволяет создавать новые форматы в виде пользовательского шаблона.
|
|
|
Использование средств, ускоряющих ввод данных
При вводе данных на листы таблицы могут быть использованы некоторые приемы, позволяющие ускорить их ввод.
1) Автозаполнение при вводе. При вводе одинаковых значений в несколько ячеек с помощью маркера автозаполнения (крестика в нижнем правом углу активной ячейки) можно скопировать значения в смежные ячейки. С помощью открывающегося контекстного меню по нажатию правой кнопки мыши после перетаскивания, можно задать дополнительные параметры автозаполнения (например, введя в ячейки числа 1 и 3, можно получить последовательность чисел с шагом 2 для выделенного диапазона ячеек).
2) Использование прогрессии. Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение ее значения (получается арифметическая или геометрическая прогрессия, список дат). Чтобы задать прогрессию, нужно выбрать кнопку Заполнить 
панели Редактирование вкладки Главная и в появившемся диалоговом окне Прогрессия задать параметры для арифметической или геометрической прогрессии.
3) Автозавершение при вводе. При помощи этой функции можно выполнять автоматический ввод повторяющихся текстовых данных. После ввода в ячейку текста Excel запоминает его и при следующем введении после набора первых букв слова предлагает вариант для завершения ввода. Для завершения ввода необходимо нажать «Enter». Доступ к этой команде можно также получить выбрав по контекстному меню по правой кнопке мыши пункт Выбрать из раскрывающегося списка. Функция автозавершения работает только с непрерывной последовательностью ячеек.
4) Использование автозамены при вводе. Автозамена предназначена для автоматической замены одних заданных сочетаний символов на другие при вводе. Например, можно задать ввод одного символа вместо ввода нескольких слов. Команда доступна по кнопке Office – Параметры Excel. В пункте Правописание - Параметры автозамены нужно задать текст и его сокращение.
|
|
|
5) Использование сочетания клавиш Сtrl+Enter для ввода повторяющихся значений. Для введения одних и тех же значений в несколько ячеек можно выделить их, ввести значение в одну ячейку и нажать Сtrl+Enter. В результате одни и те же данные будут введены во все выделенные ячейки.
Проверка данных при вводе
Если необходимо быть уверенным в том, что на лист введены правильные данные, можно указать критерии, которые являются допустимыми для отдельных ячеек или диапазонов ячеек. Для задания проверки выполните команду Данные – Работа с данными – Проверка данных. В появившемся окне (рисунок 2.2.18) задайте критерии проверки на вкладке Параметры, текст сообщения-подсказки пользователю для ввода на вкладке Сообщение для ввода, текст сообщения об ошибке на вкладке Сообщение об ошибке.
После применения команды Данные – Работа с данными – Обвести неверные данные все неверные данные будут обведены красными кружками.
Рисунок 2.2.18. Окно задания параметров проверки данных
Использование формул
Под формулой в Excel понимается математическое выражение, на основании которого вычисляется значение некоторой ячейки. В формулах могут использоваться: числовые значения; адреса ячеек (относительные, абсолютные и смешанные ссылки); операторы: математические (+, -, *, /, %, ^), сравнения (=, <, >, >=, <=, < >), текстовый оператор & (для объединения нескольких текстовых строк в одну), операторы отношения диапазонов (двоеточие (:) – диапазон, запятая (,) –для объединения диапазонов, пробел – пересечение диапазонов); функции.
Ввод формулы всегда начинается со знака «=». Результат формулы отображается в ячейке, а сама формула – в строке формул. Адреса ячеек в формуле могут вводиться вручную, а могут просто с помощью щелчка мыши по нужным ячейкам.
|
|
|
После вычисления в ячейке отображается полученный результат, а в строке формул в окне ввода – созданная формула.
Способы адресации ячеек
Адрес ячейки состоит из имени столбца и номера строки рабочего листа (например А1, BM55). В формулах адреса указываются с помощью ссылок – относительных, абсолютных или смешанных. Благодаря ссылкам данные, находящиеся в разных частях листа, могут использоваться в нескольких формулах одновременно.
Относительная ссылка указывает расположение нужной ячейки относительно активной (т. е. текущей). При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы (Пример записи ссылки: A2, С10).
Абсолютная ссылка указывает на точное местоположение ячейки, входящей в формулу. При копировании формул эти ссылки не изменяются. Для создания абсолютной ссылки на ячейку, поставьте знак доллара ($) перед обозначением столбца и строки (Пример записи ссылки: $A$2, $С$10). Чтобы зафиксировать часть адреса ячейки от изменений (по столбцу или по строке) при копировании формул, используется смешанная ссылка с фиксацией нужного параметра. (Пример записи ссылки: $A2, С$10).
Замечания
· Чтобы вручную не набирать знаки доллара при записи ссылок, можно воспользоваться клавишей F4, которая позволяет «перебрать» все виды ссылок для ячейки.
· Чтобы использовать в формуле ссылку на ячейки с другого рабочего листа, нужно применять следующий синтаксис: Имя_Листа!Адрес_ячейки (Пример записи: Лист2!С20).
· Чтобы использовать в формуле ссылку на ячейки из другой рабочей книги, нужно применять следующий синтаксис: [Имя_рабочей_книги]Имя_Листа!Адрес_ячейки (Пример записи: [Таблицы.xlsx]Лист2!С20).
Встроенные функции Excel
Каждая функция имеет свой синтаксис и порядок действия, который нужно соблюдать, чтобы вычисления были верными. Аргументы функции записываются в круглых скобках, причем функции могут иметь или не иметь аргументы, при их использовании необходимо учитывать соответствие типов аргументов. Функция может выступать в качестве аргумента для другой функции, в этом случае она называется вложенной функцией. При этом в формулах можно использовать до нескольких уровней вложения функций.
В Excel 2007 существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры (при этом активируется средство Автозаполнение формул, позволяющее по первым введенным буквам выбрать нужную функцию (рисунок 2.2.19)), а можно выбирать в окне Мастер функций, активируемом кнопкой 
на панели Библиотека функций вкладки Формулы или из групп функций на этой же панели, либо с помощью кнопки 
панели Редактирование вкладки Главная.
|
|
|
Рисунок 2.2.19. Автозаполнение формул
Формулы можно отредактировать так же, как и содержимое любой другой ячейки. Чтобы отредактировать содержимое формулы: дважды щелкните по ячейке с формулой, либо нажмите F2, либо отредактируйте содержимое в строке ввода формул.
Присвоение и использование имен ячеек
В Excel 2007 имеется полезная возможность присвоения имен ячейкам или диапазонам. Это бывает особенно удобно при составлении формул. Например, задав для какой-либо ячейки имя Итого_за_год, можно во всех формулах вместо адреса ячейки указывать это имя.
Имя ячейки может действовать в пределах одного листа или одной книги, оно должно быть уникальным и не дублировать названия ячеек. Чтобы присвоить имя ячейкам, нужно выделить ячейку или диапазон и в строке названия ввести новое имя. Либо воспользоваться кнопкой Присвоить имя панели Определенные имена вкладки Формулы и вызвать диалоговое окно (рисунок 2.2.20), чтобы задать нужные параметры.
Рисунок 2.2.20. Окно создания имени
Для просмотра всех присвоенных имен используйте команду Диспетчер имен. Также на листе можно получить список всех имен с адресами ячеек по команде Использовать в формуле – Вставить имена панели Определенные имена.
Для вставки имени в формулу можно применить команду Использовать в формуле и выбрать из списка необходимое имя ячеек.
Замечание. Имя может быть присвоено не только диапазонам ячеек, но и формуле. Это удобно при использовании вложенных формул.
Отображение зависимостей в формулах
Чтобы выявить ошибки при создании формул, можно отобразить зависимости ячеек. Зависимости используются для просмотра на табличном поле связей между ячейками с формулами и ячейками со значениями, которые были задействованы в данных формулах. Зависимости отображаются только в пределах одной открытой книги. При создании зависимости используются влияющие ячейки и зависимые ячейки.
Влияющая ячейка – это ячейка, которая ссылается на формулу в другой ячейке.
Зависимая ячейка – это ячейка, которая содержит формулу.
Чтобы отобразить связи ячеек, нужно выбрать команды Влияющие ячейки или Зависимые ячейки панели Зависимости формул вкладки Формулы. Чтобы не отображать зависимости, примените команду Убрать стрелки этой же панели.
Рисунок 2.2.21. Отображение влияющих ячеек
Режимы работы с формулами
В Excel установлен режим автоматических вычислений, благодаря которому формулы на листах пересчитываются мгновенно. При размещении на листе очень большого количества (до несколько тысяч) сложных формул скорость работы может заметно снизиться из-за пересчета всех формул на листе. Чтобы управлять процессом вычисления по формулам, нужно установить ручной режим вычислений, применив команду Формулы – Вычисление – Параметры вычислений – Вручную. После внесения изменений нужно вызвать команду Произвести вычисления (для пересчета данных на листе книги) или Пересчет (для пересчета всей книги) панели Вычисление.
Полезной возможностью по работе с формулами является отображение всех формул на листе. Это можно сделать, используя команду Формулы – Зависимости формул – Показать формулы. После этого в ячейках вместо вычисленных значений будут показаны записанные формулы. Для возврата в обычный режим нужно еще раз нажать кнопку Показать формулы.
Если формула возвращает ошибочное значение, Excel может помочь определить ячейку, которая вызывает ошибку. Для этого нужно активизировать команду Формулы – Зависимости формул – Проверка наличия ошибок – Источник ошибок. Команда Проверка наличия ошибок помогает выявить все ошибочные записи формул.
Для отладки формул существует средство вычисления формул, вызываемое командой Формулы – Зависимости формул – Вычислить формулу, которое показывает пошаговое вычисление в сложных формулах
Практикум:.
1. Сформировать таблицу расчета суммы ряда (варианты заданий по расчету суммы ряда – см. ниже). При формировании таблицы использовать встроенные функции, абсолютную и относительную адресацию, автозаполнение ячеек.
2. В зависимости от числа слагаемых n оформить таблицу следующим образом:
Таблица 19. 
| x i | 1 | 2 | … | n | S | Y |
| 0,1 | ||||||
| 0,2 | ||||||
| . | ||||||
| . | ||||||
| 1 |
Таблица 20. 
| i x | 0,1 | 0,2 | … | 1 |
| 1 | ||||
| 2 | ||||
| . | ||||
| . | ||||
| n | ||||
| S | ||||
| Y |
3. Используя условное форматирование, выделить отрицательные числа синим цветом, числа больше 1,5 – красным цветом.
4. Оформить таблицу. Образец оформления – ниже. Шаг изменения x в зависимости от варианта задания равен 0,1 (либо Pi/*).
5. Построить в одной координатной сетке (на одной диаграмме) графики s=f(x) и y=f(x).
6. Изучить возможности применения функций (список функций см. в варианте задания), привести пример работы каждой функции.
Таблица 21. Варианты заданий
| № вар. | Сумма S | Диапазон изм-я X | n | Функция Y | Функция Excel |
| 1 | 
| [0,1; 1] | 10 | 
| ГРАДУСЫ, СЕКУНДЫ |
| 2 | 
| [Pi/5; 9Pi/5] | 40 | 
| ЗНАК, ТДАТА |
| 3 | 
| [0,1; 1] | 10 | 
| НОД, МЕДИАНА |
| 4 | 
| [-0,2; 1] | 40 | 
| ОКРУГЛ, СРЗНАЧЕСЛИ |
| 5 | 
| [0,1; 1] | 15 | 
| ОСТАТ, СЧЕТЕСЛИ |
| 6 | 
| [0,1; 0,9] | 40 | 
| СУММЕСЛИ, ЗАМЕНИТЬ |
| 7 | 
| [0,1; 1] | 20 | 
| ЦЕЛОЕ, ЛЕВСИМВ |
| 8 | 
| [Pi/10; 9Pi/10] | 40 | Pi/4 | ДНЕЙ360, ПОВТОР |
| 9 | 
| [0,1; 1] | 10 | 
| НОМНЕДЕЛ И, ПРОПИСН |
| продолжение таблицы | |||||
| 10 | 
| [0,1; 0,8] | 40 | 
| ГРАДУСЫ, ПРОПНАЧ |
| 11 | 
| [Pi/5; 4Pi/5] | 40 | 
| ЗНАК, СЖПРОБЕЛЫ |
| 12 | 
| [1; 2] | 15 | 
| НОД, СИМВОЛ |
| 13 | 
| [0,1; 1] | 10 | 
| ОКРУГЛ, СОВПАД |
| 14 | 
| [0,1; 1] | 10 | 
| ОСТАТ, СЦЕПИТЬ |
| 15 | 
| [0,1; 1] | 20 | 
| СУММЕСЛИ, Т |
|
|
|
