Лекция № 7
Тема: «Основные операции работы с таблицами и данными в них»
План:
- Основные понятия в MS Excel
- Ввод данных в таблицу.
- Редактирование данных.
- Формат данных.
- Математические возможности. Формулы.
- Функции в Excel.
- Основные понятия в MS Excel
Microsoft Excel – программа, предназначенная для работы с электронными таблицами, которая позволяет хранить, организовывать и анализировать информацию.
В таблицах могут быть записаны данные различных типов: тексты, даты, формулы, числа и прочее.
Программа применяется при решении планово-экономических, финансовых, технико-экономических и инженерных задач, для статистической обработке информации, анализа данных и представления их в виде графиков и диаграмм.
Файлы Excel 2007 имеют расширение *.xlsx.
При запуске Excel на экране отображается окно приложения, в котором
открывается новая чистая рабочая Книга.
Рабочая книга состоит из рабочих листов, каждый из которых является электронной таблицей.
Электронная таблица (ЭТ) – это рабочие листы, состоящие из строк и столбцов (имеет вид матрицы).
На пересечении строк и столбцов образуются ячейки с уникальными именами.
Именно ячейки являются основным элементом электронной таблицы.
В ячейки могут вводиться данные, на которые можно ссылаться по именам ячеек. К данным относятся: числа, даты, время суток, текст или символьные данные и формулы.
Активная ячейка – ячейка, в которой на данный момент находится курсор.
По умолчанию открывается три рабочих листа, переход к которым можно осуществить, щелкая на ярлычках, расположенных внизу книги.
При необходимости в книгу можно добавить рабочие листы или удалить их из книги. Книга с входящими в нее рабочими листами сохраняется на диске в виде
отдельного файла с уникальным именем.
Одно из главных достоинств электронных таблиц автоматический пересчет данных по ранее заданным формулам и обновление диаграмм при изменении значения, хранящегося в ячейке.
Окно приложения Microsoft Excel 2007 состоит из следующих основных областей (Рисунок 7.1)
Рисунок 7.1 – Окно программы
- Ввод данных в таблицу
Для ввода данных в ячейку необходимо:
- Указать ячейку, в которую необходимо ввести данные (подвести курсор к ячейке и дважды нажать кнопку мыши).
- Набрать число или текст и нажать клавишу ENTER.
Ввод символьных данных или текста
Текст в ячейке по умолчанию выравнивается по левому краю. Если текст не помещается в ячейку, то он перемещается в следующую ячейку при условии, что она свободна. Чтобы поместить текст только в одной ячейки, необходимо увеличить ширину столбца или разрешить перенос по словам.
Ввод чисел
Числа в ячейке выравниваются по правому краю. Как правило, числа в ячейку вводятся в одном из встроенных форматов.
Ввод длинной последовательности значений или
последовательных рядов данных
К операциям ввода длинной последовательности относятся: Автозаполнение и Заполнение ряда.
Для применения автозаполнения надо ввести в ячейку первое значение из распознаваемой последовательности и выделить эту ячейку. Затем подвести указатель мыши к маркеру заполнения (черный квадратик внизу слева выделенной ячейки), нажать левую кнопку мыши и удерживая ее протянуть вдоль строки или столбца, а затем отпустить кнопку мыши. В результате выделенная область заполнится данными.
Для заполнения ряда надо заполнить два значения ряда, затем выделить их и воспользуясь маркером заполнения расширить ряд. После заполнения ряда, справа от ряда будет отображаться кнопка Параметры автозаполнения. При щелчке на кнопке открывается список команд, которые можно выполнить над ячейками ряда: копирование ячейки; заполнить; заполнить только форматы и заполнить только значения.
- Редактирование данных
К операциям редактирования относятся:
- Редактирование данных в ячейках;
- Перемещение и копирование ячеек и блоков ячеек;
- Вставка и удаление ячеек, строк, столбцов и листов;
- Поиск и замена данных.
Для редактирования данных в ячейках необходимо дважды щелкнуть на
редактируемой ячейки или нажать кнопку F2, и когда в ней будет отображаться курсор, выполнить требуемые операции.
При перемещении ячейки или блока ячеек надо выделить эту ячейку (блок ячеек) вокруг ее будет отображаться полужирная рамка. Затем подвести указатель мыши к маркеру ячейки и когда изображение указателя изменится с белого крестика на четырех направленную стрелку черного цвета, нажать левую кнопку мыши и, удерживая ее, переместить указатель мыши к требуемой ячейке. Для завершения операции надо отпустить кнопку мыши.
При копировании выполняются аналогичные действия, только необходимо удерживать кнопку Ctrl.
Удаление ячеек, строк и столбцов
Самый простой способ – выделить ячейку и нажать Delete или:
- Выделить диапазон ячеек для удаления
- На вкладке Главная в группе Ячейки выбрать кнопку Удалить
- Выбрать пункт меню Удалить ячейки
- В окне Удаление ячеек выбрать требуемую операцию.
Вставка ячеек, строк и столбцов
- Выделить ячейку (строку/столбец) относительно которого нужно выполнить вставку ячейки (строки/столбца)
- На вкладке Главная в группе Ячейки выбрать кнопку Вставить
- Выбрать:
а) вставить строки на лист (строка вставится выше выделенной строки);
б) вставить столбцы на лист (столбец вставится левее выделенной строки);
в) ячейки, если необходимо выполнение вставки единичной ячейки. При этом в появившемся окне вы выбираете требуемую вставку.
- Формат данных
Форматирование ячеек электронных таблиц является необходимым условием работы с данными.
Форматирование ячеек осуществляется с помощью кнопок группы Число или вывода контекстного меню Формат ячеек.
Это окно имеет шесть вкладок: Число, Выравнивание, Шрифт, Граница, Заливка, Защита. Окно диалога открывается при щелчке левой кнопкой мыши на стрелке группы Число на вкладке Главная.
На вкладке Число окна Формат ячеек можно назначать числовые форматы ячейкам электронных таблиц. Причем форматы ячейкам электронных таблиц можно назначать как до ввода данных, так и после их ввода в ячейки.
К числовым форматам относятся: Общий, Числовой, Денежный, Финансовый и т.д.
Для форматирования ячейки (ячеек) необходимо ее (их) выделить, затем открыть окно диалога Формат ячеек или раскрывающийся список Числовой формат в группе Число на вкладке Главная и назначить необходимый числовой формат.
К форматированию ячеек относятся и такие операции как:
- объединение ячеек;
- выравнивание и направление текста в ячейках;
- перенос по словам и т.д.
Эти операции выполняются в группе Выравнивание на вкладке Главная.
Границы, заливку и защиту ячеек можно отформатировать на соответствующих вкладках окна диалога Формат ячеек или на вкладке Главная.
- Математические возможности. Формулы
Для выполнения различных вычислений в таблицах Excel 2007 используют формулы и функции.
Формулы – это выражение, начинающееся со знака равенства и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций.
Эти формулы используются для расчетов, основанных на значениях, хранящихся в других ячейках рабочего места. Формулы позволяют выполнять обычные математические операции +, -, *, /,% ^, =, <,>, <=,> =, <> и оператор объединения текстовых данных &.
Вычисления с помощью формул всегда начинаются со знака равенства «=»!
Кроме того, Excel распознает трех адресных операторов для указания диапазона ячеек:
- Оператор диапазона (двоеточие)
например, B1: D1, A1: E10
2. Оператор объединения диапазонов (точка с запятой)
= СУММ (C5: C7; A3: C3)
3. Оператор пересечения диапазонов (пробел)
= СУММ (A5: B11 B8: C10), отсюда следует B8: B10
Например, если нужно сложить значения в ячейках B1 и B2 и вывести их
сумму в ячейке B5. Для этого в ячейку B5 нужно ввести = B1 + B2.
Координаты ячеек можно вводить, выбирая нужную ячейку мышью. Данный метод позволяет снизить риск появления ошибок, который имеет место при ручном вводе адресов и имен ячеек.
Для этого:
- Введите знак =
- Щелкните кнопкой мыши на ячейке, координаты которой должны стоять в начале формулы.
- Введите оператора.
- Щелкните кнопкой мыши на ячейке, используемой в формуле.
Формулы редактируются, копируются и перемещаются так же, как и содержимое ячеек.
Относительные, абсолютные и смешанные ссылки
Используемые в формуле ссылки на ячейки могут быть трех типов: относительные, абсолютные и смешанные.
Относительные ссылки в формуле автоматически изменяются при копировании в строке или столбце. При этом все используемые ссылки (адреса ячеек) в
формуле-источнике будут автоматически заменены в формуле-приемнике на аналогичные, соответствующие новому размещению формулы. По умолчанию ссылки
являются относительными.
Абсолютные ссылки на ячейки имеют постоянное значение. Т.е. при копировании формулы из ячейки в ячейку ссылки не изменяются. Абсолютные ссылки
обозначаются знаком доллара ($), например $А$1.
Абсолютную ссылку устанавливают при вводе адреса ячейки или после ввода – её выделяют и нажимают F4.
Смешанная ссылка является абсолютной по столбцу и относительной по строке или относительной по столбцу и абсолютной по строке.
Например, ссылка $А1 является абсолютной по столбцу А и относительной по строке 1. При копировании смешанной ссылки из ячейки в ячейку ее относительная ссылка меняется, а абсолютная остается неизменной.
При неправильном вводе формул программа вместо ответов выдает сообщения об ошибках следующего вида:
| Дел/0! | Деление на 0 |
| Ссылки! | Указана некорректная ссылка |
| #ПУСТО! | Неверно указано сечение диапазонов |
| #ИМЯ! | Указано недопустимое имя операнда (функции, диапазона) |
| #Н/д! | Нет данных в ячейке, на которой сделана ссылка. |
| #ЗНАЧ! | Задан аргумент недопустимого типа |
| #ЧИСЛО! | Невозможность вычисления значений (корень из отрицательного числа) |
По умолчанию Excel заново пересчитывает все формулы рабочего места всякий раз, когда вносятся изменения в ячейки, указанные в формуле.
- Функции в Excel
Функции Excel – это специальные, заранее созданные формулы для сложных вычислений, в которые пользователь должен ввести только аргументы.
Функции состоят из двух частей:
- Имя функции – описывает операцию, которую эта функция выполняет;
- Один или несколько аргументов – значения, адреса ячеек или другие функции, используемые основной функцией, они всегда заключены в круглые скобки.
Открывающая скобка ставится без пробела сразу после имени функции.
Например, в формуле «=СУММ(А2:А9)»:
СУММ — это имя функции А2:А9 — ее аргументы.
Эта формула суммирует все числа в диапазоне ячеек от А2 до А9.
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой.
В функции можно использовать до 30 аргументов.
Ввод функций в рабочем листе с помощью клавиатуры
- Установить курсор в ячейку для ввода функции.
- Ввести с помощью клавиатуры функцию.
- Перейти в другую ячейку или нажать Enter.
Ввод функций с помощью Мастера функций
- Установить курсор в ячейку для ввода функции.
- На вкладке Формула выбрать Вставить функцию.
- В окне Мастера функций определить тип используемой функции.
- В окне Аргумент функции указать аргумент выбранной функции или область аргументов.
Часто используемые функции: СРЗНАЧ, МИН, МАКС, СУММ.
Поскольку функция СУММ используется в Excel чаще других, на вкладке Главная и Формулы есть кнопка Автосумма.
Контрольные вопросы
- Основные понятия в MS Excel.
- Ввод текстовых и числовых данных в таблицу MS Excel.
- Применение автозаполнения и заполнения ряда в таблице MS Excel.
- Добавление, удаление строк, столбцов, листов рабочей книги.
- Какие операции относятся к форматированию ячеек? Что необходимо выполнить для форматирования?
- Как установить заливку и границу вокруг ячеек электронной таблицы?
- Понятие формулы в электронной таблице. Виды операций.
- Какие существуют операторы для указания диапазона ячеек?
- Какие в формуле используются ссылки на ячейки, и чем они отличаются?
- Часто встречающиеся функции в электронной таблице.