Конспект. Электронные таблица Excel
Учитель информатики Батракова Л.В.
Основные понятия электронных таблиц
Документ, созданный в MS Excel, называется рабочей книгой. Рабочая книга состоит из набора рабочих листов. Листов в книге Excel может быть до 255. Каждый рабочий лист имеет имя, по умолчанию Лист1, Лист2, Лист3. Названия листов отображаются в нижней части листа. С помощью ярлычков, на которых написаны имена листов, можно переключаться между рабочими листами, входящими в рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть на его ярлычке, либо нажать правой кнопкой мыши на ярлычке и выбрать пункт Переименовать. Листам можно давать произвольные имена длиною до 31-го символа, исключая символы * : / \ ? [ ]
Рис.1 Вид окна при запуске Excel.
Чтобы переместить рабочий лист в другую позицию в той же рабочей книге, нужно мышью перетащить его ярлычок в соответствующее место.
Если пользователю необходимо добавить новый рабочий лист, он может сделать это, выполнив команду Вставка / Лист. Для удаления листа следует выполнить команду Правка / Удалить лист, при этом необходимо помнить о том, что удалённый лист восстановить невозможно.
Пространство рабочего листа состоит из строк и столбцов. По умолчанию каждый рабочий лист имеет 256 столбцов, (каждый столбец имеет имя, озаглавленное латинской буквой) и 65536 строк (нумерация от 1 до 65536), таким образом, всего на листе имеется 16777216 ячеек.
На пересечении строк и столбцов образуются ячейки таблицы.
Ячейка – это минимально адресуемый элемент рабочего листа. Имя ячейки (адрес) состоит из имени столбца и номера строки, например, A10 или D23. Адресация ячеек используется при записи формул. Одна из ячеек всегда является активной, и в ней производятся операции ввода и редактирования. Группа ячеек называется диапазоном. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах, например: А5:C20.
Выделение ячеек с помощью мыши
Таблица 1
-
Чтобы выделить:
|
Действия пользователя
|
Лист
|
Щёлкнуть мышью на сером прямоугольнике, расположенном на пересечении строки с номером 1 и столбца с именем А
|
Столбец
|
Щёлкнуть на заголовке столбца (на имени столбца)
|
Строку
|
Щёлкнуть на номере строки
|
Ячейку
|
Щёлкнуть на ячейке мышью
|
Диапазон несмежных ячеек
|
Выделить мышью первый диапазон. Удерживая клавишу CTRL, выделить другой диапазон и т. д.
|
Ввод, редактирование и форматирование данных
Ввод текста и чисел
Ввод данных осуществляется непосредственно в текущую ячейку или в строку формул. Содержимое строки формул всегда соответствует содержимому текущей ячейки, так что оба способа равнозначны.
Программа Excel может работать с тремя типами данных, которые могут размещаться в ячейках электронной таблицы - это текст, число и формула. Текст по умолчанию выравнивается по левому краю ячейки, а числа по правому.
Ввести данные в ячейку можно несколькими способами, например:
Сделать ячейку активной, то есть поместить курсор в нужную ячейку и один раз щёлкнуть левой клавишей мыши.
Ввести данные с клавиатуры
Нажать клавишу Enter
или
Сделать двойной щелчок мышью на нужной ячейке
Ввести данные с клавиатуры
Нажать клавишу Enter
Редактировать данные можно также несколькими способами, например:
Для замены одних данных в ячейке другими следует сделать её активной и ввести новые данные.
или
Сделать активной ячейку
Выполнить двойной щелчок на редактируемой ячейке
или
Сделать активной ячейку
Нажать клавишу F2
или
Сделать активной ячейку
Сделать щелчок в строке формул
Для удаления содержимого ячейки или диапазона ячеек необходимо выделить очищаемую ячейку или диапазон и нажать клавишу Delete.
3. Выравнивание и изменение ориентации текста и чисел
Excel позволяет выравнивать текст и числа, как по горизонтали (влево, вправо, по центру), так и по вертикали (по верхнему краю, посередине, нижнему краю). Кроме того, пользователь может управлять углом поворота текста и переносить слова в ячейке.
Рис. 2.
Чтобы выровнять содержимое ячейки или диапазона:
Выберите ячейку или диапазон
Выполните команду Формат / Ячейки. На экране отобразится диалоговое окно Формат ячеек (см. рис.3)
Используя элементы управления вкладки Выравнивание этого диалогового окна, можно установить то выравнивание, которое необходимо.
Форматирование содержимого ячеек
Визуально человеку проще работать с теми данными, которые представлены в отформатированном виде. Для того чтобы отформатировать данные по любому формату, имеющемуся в Excel необходимо:
Выделить ячейку или диапазон ячеек, подлежащих форматированию.
Выполнить команду Формат / Ячейки. Откроется диалоговое окно Формат ячеек ( см. рис.4)
Выберите вкладку Число этого диалогового окна и установите тот тип формата, который вам нужен
Рис. 3.
Рис. 4.
5. Основные типы форматов
Таблица 2
Формат
|
Описание
|
Общий
|
Этот формат принят по умолчанию
|
Числовой
|
Число отображается с заданным количеством десятичных знаков после запятой
|
Денежный
|
После изображения числа может помещаться знак валюты
|
Финансовый
|
Отличается от денежного вынесением знака минус для отрицательных чисел в крайнюю левую позицию ячейки
|
Дата
|
Эта категория позволяет вывести дату по одному из пятнадцати форматов
|
Время
|
Эта категория позволяет вывести время по одному из восьми форматов
|
Процентный
|
Этот формат позволяет выводить число в виде процентов со знаком % и определять количество выводимых знаков после десятичной точки
|
Дробный
|
Дробный формат используется для представления чисел в виде смешанного числа
|
Экспоненциальный
|
В этом формате число представляется в виде mE±p, где m – мантисса числа, Е – символ, обозначающий основание десятичной ССЧ, p – порядок числа, например число 5,34´1025 записывается как 5.34Е+25
|
Текстовый
|
Применение этого формата к числовому значению позволяет рассматривать его как текст
|
Дополнительный
|
Дополнительный формат предназначен для работы с почтовыми индексами, телефонными номерами, адресами
|
Все форматы
|
Эта категория позволяет создавать пользовательские форматы
|
6. Вычисления в электронных таблицах
Вычисления в таблицах осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединённые знаками математических операций, операций сравнения, операций конкатенации. Формула, введённая в ячейку, должна начинаться со знака равенства.
Примеры формул
Таблица 3
Формула
|
Описание
|
=В1+В2
|
Складывает содержимое ячеек В1 и В2
|
=А1^(1/3)
|
Возводит в степень (1/3) содержимое ячейки А1
|
=СУММ(А1:А5)
|
Возвращает сумму значений из диапазона А1:А5
|
При вводе в формулу ссылки на ячейку или диапазон ячеек можно не набирать эту ссылку с клавиатуры, а выбрать нужную ячейку или диапазон ячеек мышью методом выделения.
7. Ошибки, возвращаемые формулами
Таблица 4
Ошибка
|
Описание
|
#Н/Д
|
Н/Д – является сокращением термина Неопределённые Данные. Помогает предотвратить использование ссылки на пустую ячейку
|
#ЧИСЛО!
|
Функция с числовым аргументом использует неприемлемый аргумент
|
#ИМЯ?
|
Ошибка в написании имени или используется несуществующее имя
|
#ССЫЛКА!
|
Используется ссылка на несуществующую ячейку
|
#ЗНАЧ!
|
Вместо числового или логического значения введён текст, и Excel не может преобразовать его к нужному типу данных
|
#ДЕЛ/0!
|
В качестве делителя используется ссылка на ячейку, в которой содержится ненулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль)
|
#ПУСТО!
|
Используется ошибочная ссылка на ячейку
|
#############
|
Данные не помещаются в ячейку по ширине, следует увеличить ширину столбца
|
Ссылки на ячейки
На рис.5 представлена формула для сложения двух чисел. Вместо чисел в формуле используются имена ячеек или, как говорят, ссылки на ячейки. Ссылки выполняют роль адресов ячеек, содержимое которых используется в вычислениях. Ссылку на ячейку можно задать несколькими способами:
Ввести адрес ячейки с клавиатуры
В
ыполнить щелчок на нужной ячейке или выделить мышью нужный диапазон ячеек.
Рис. 5.
В Excel при работе с формулами используется два типа адресации ячеек: относительная и абсолютная.
По умолчанию, ссылки на ячейки в формулах рассматриваются как относительные. Это означает, что адреса ячеек при копировании формул из одной ячейки в другую изменяются автоматически, то есть меняются относительно исходной формулы. Как видно из примера на рис.6 при копировании формулы, которая находится в ячейке С1, происходит автоматическая переадресация ячеек.
Рис. 6.
При абсолютной адресации адреса ячеек при копировании формулы не изменяются. На рис. 7 показан пример из которого видно, что если копировать формулу =В2*В4, которая находится в ячейке С4 обычным способом с помощью маркера автозаполнения, то результат будет неверным, потому что адресация будет меняться относительно исходной формулы. А нам нужно, чтобы адрес ячейки B2 не изменялся. Для того чтобы создать абсолютную ссылку на ячейку, достаточно перед именем строки и столбца поставит знак $. (рис.8)
Рис. 7.
Рис. 8.
Кроме абсолютной ссылки на ячейку, имеются ещё два типа абсолютных ссылок:
Абсолютная ссылка на строку. В этом случае знак $ ставится только перед номером строки. Например, С$4 – это абсолютная ссылка на четвёртую строку.
Абсолютная ссылка на столбец. В этом случае знак $ ставится только перед именем столбца. Например, $С4 – это абсолютная ссылка на столбец С.
В качестве примера можно привести расчёт таблицы умножения (рис. 9). В ячейку В2 следует ввести формулу: =$A2*B$1 и растянуть её за маркер заполнения на диапазон B2:J10.
Рис. 9.
Копирование содержимого ячеек
Копирование и перемещение ячеек в Excel можно осуществить способом перетаскивания или через буфер обмена. При работе с небольшим числом ячеек удобно использовать способ перетаскивания, при работе с большими диапазонами удобнее пользоваться буфером обмена.
Чтобы способом перетаскивания скопировать содержимое ячейки, следует поставить указатель мыши на границу текущей ячейки (указатель мыши примет вид белой стрелки) и, удерживая клавишу CTRL, перетащить в нужное место рабочего листа содержимое ячейки. Чтобы способом перетаскивания переместить содержимое ячейки, следует поставить указатель мыши на границу текущей ячейки (указатель мыши примет вид белой стрелки) и перетащить в нужное место рабочего листа содержимое ячейки.
2 команды:
Правка / Копировать и Правка / Вставить (копирование)
или
Правка / Вырезать и Правка / Вставить (перемещение).
Можно также использовать перетаскивание с помощью правой кнопки мыши. В этом случае при отпускании кнопки появляется специальное меню, из которого можно выбрать конкретную выполняемую команду (рис.10)
Рис. 10.
Действия по копированию и перемещению содержимого ячеек можно выполнить, используя соответствующие кнопки на стандартной панели: Копировать, Вырезать и Вставить.
Копирование и перемещение данных через буфер обмена нужно выполнять следующим образом: необходимо выделить копируемый (вырезаемый) диапазон и дать команду на его помещение в буфер обмена: Правка / Копировать или Правка / Вырезать. Место вставки определяется путём указания ячейки, соответствующей верхнему левому углу диапазона, помещённого в буфер обмена. Вставка выполняется командой Правка / Вставить.
Автоматизация процесса ввода
Для автоматизации ввода текстовых данных используется метод автозавершения. Его применяют при вводе в ячейки повторяющихся данных. Такая ситуация возникает при использовании рабочей книги Excel в качестве базы данных. При вводе текста в очередную ячейку программа проверяет соответствие введённых символов данным, имеющимся в этом столбце выше. Если обнаружено совпадение, введённый текст автоматически дополняется. Нажатие клавиши Enter подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.
При работе с числами используется метод автозаполнения. В правом нижнем углу текущей ячейки имеется чёрный квадратик, если поставить на него указатель мыши, то он превращается в чёрный крестик – это маркер заполнения.
Чтобы точно сформулировать условия заполнения ячеек следует выполнить команду Правка / Заполнить / Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке Ок Excel автоматически заполняет ячейки в соответствии с заданными критериями.
Рис. 11.
Использование стандартных функций
Функции в Excel используются для выполнения стандартных вычислений. При вводе формулы обращение к встроенной функции можно осуществить с клавиатуры, либо вызвать Мастер функций. Для этого в Excel есть соответствующая кнопка ( ) на стандартной панели: Вставка функции, а также пункт меню Вставка / Функция. В появившемся окне Мастер функций - шаг 1 из 2 пользователь может обратиться к более 400 встроенным функциям, которые объединены в девять групп:
Финансовые
Дата и время
Математические
Статистические
Ссылки и массивы
Работа с базой данных
Текстовые
Логические
Проверка свойств и значений
Если пользователь не знает, к какой категории относится функция, можно выбрать полный алфавитный перечень, а название категории 10 недавно использовавшихся говорит само за себя.
Рис. 12.
В таблице 5 приведён неполный список категории Математические.
Таблица 5
Функция
|
Назначение
|
ABS
|
Возвращает модуль (абсолютную величину) числа
|
ACOS
|
Возвращает арккосинус числа. Если нужно преобразовать результат из радиан в градусы, то умножьте его на 180/ПИ()
|
ASIN
|
Возвращает арксинус числа
|
ATAN
|
Возвращает арктангенс числа
|
COS
|
Возвращает косинус заданного угла
|
EXP
|
Возвращает число е, возведённое в указанную степень
|
LN
|
Возвращает натуральный логарифм числа
|
LOG
|
Возвращает логарифм числа по заданному основанию. Если основание опущено, то оно полагается равным 10
|
LOG10
|
Возвращает десятичный логарифм числа
|
SIN
|
Возвращает синус заданного угла
|
TAN
|
Возвращает тангенс заданного угла
|
КОРЕНЬ
|
Возвращает положительное значение квадратного корня из неотрицательного числа
|
МОБР
|
Возвращает обратную матрицу
|
МОПРЕД
|
Возвращает определитель матрицы
|
МУМНОЖ
|
Возвращает произведение матриц
|
НЕЧЁТ
|
Возвращает число, округлённое до ближайшего нечётного целого
|
ОСТАТ
|
Возвращает остаток от деления
|
ОТБР
|
Усекает число до целого, отбрасывая дробную часть числа так, что остаётся целое число
|
ПИ()
|
Возвращает значение числа p. У этой функции пустой список аргументов
|
ПРОИЗВЕД
|
Возвращает произведение чисел, заданных в качестве аргументов
|
РАДИАНЫ
|
Преобразует градусы в радианы
|
СТЕПЕНЬ
|
Возвращает результат возведения числа в степень
|
СУММ
|
Возвращает сумму всех чисел, входящих в список аргументов
|
ФАКТР
|
Возвращает факториал числа
|
ЧЁТН
|
Возвращает число, округлённое до ближайшего чётного целого
|
В таблице 6 приведён неполный список категории Дата и время.
Таблица 6
Функция
|
Назначение
|
ГОД
|
Возвращает год, соответствующий указанной дате
|
ДАТА
|
Возвращает порядковый номер указанной даты
|
ДЕНЬ
|
Возвращает день месяца указанной даты
|
ДЕНЬНЕД
|
Возвращает номер дня недели указанной даты
|
ДНЕЙ360
|
Возвращает количество дней между двумя датами на основе 360-дневного года
|
МЕСЯЦ
|
Возвращает месяц, соответствующий указанной дате
|
МИНУТЫ
|
Возвращает минуты, соответствующие указанной дате
|
СЕГОДНЯ
|
Возвращает текущую дату в числовом формате
|
СЕКУНДЫ
|
Возвращает секунды, соответствующие указанной дате
|
ТДАТА
|
Возвращает текущую дату и время в числовом формате
|
ЧАС
|
Возвращает час, соответствующий указанной дате
|
В таблице 7 приведён список категории Логические функции.
Таблица 7
Функция
|
Назначение
|
ЕСЛИ
|
Возвращает первое значение, если логическое выражение при вычислении даёт значение ИСТИНА, и второе значение, если ЛОЖЬ
|
И
|
Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ
|
ИЛИ
|
Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ
|
ИСТИНА
|
Возвращает логическое значение ИСТИНА
|
ЛОЖЬ
|
Возвращает логическое значение ЛОЖЬ
|
НЕ
|
Меняет на противоположное логическое значение аргумента
|
12. Построение диаграмм и графиков
В программе Excel термин диаграмма используется для обозначения всех видов графического представления данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. Для построения диаграммы используют Мастер диаграмм, который запускается при щелчке на кнопке Мастер диаграмм на стандартной панели инструментов. Удобно заранее выделить область данных, на основе которых будет строиться диаграмма, но задать эту информацию можно и в ходе работы мастера. На первом шаге работы мастера выбирают форму диаграммы. Доступные формы перечислены в списке Тип на вкладке Стандартные. На вкладке Вид имеется несколько вариантов представления данных. На вкладке Нестандартные отображается набор полностью сформированных типов диаграмм с готовым форматированием.
Рис. 13
Задание:
1. Создать график функции
y = ax^3 - bx + c ( х изменяется от -20 до +20 с шагом 0.5; значения a,b,c задаюся в отдельных ячейках B9:B11). Для простоты a, b, c можно принять равными 1.
2. Дан интервал А1:А15 целых чисел. Сосчитать количество различных чисел в этом интервале.
четных
нечетных
которые делятся на 5
которые кончаются на 0.
3. Дан интервал ячеек А1:А30. Найти max суммы чисел из А1+А30, А2+А29, А3-А28,…, А15+А16.
|
