Электронные таблицы
Что такое электронные таблицы Электронная таблица – это программа, моделирующая на экране двумерную таблицу, состоящую из строк и столбцов. Основной задачей электронной таблицы является ввод данных в ячейки и обработка их по формулам.
Назначение и функции ЭТ • • ввод и редактирование данных, автоматизация ввода (включая автозаполнение, автозамену и проч. ); форматирование табличных данных с использованием стандартных средств, стилей, шаблонов; выполнение вычислений по формулам (при этом может использоваться обширный набор встроенных функций); анализ табличных данных (вывод промежуточных и общих итогов, создание сводных таблиц, подбор параметров, прогнозирование решений); графическое представление данных (построение графиков, диаграмм; включение рисунков, видеоматериалов, географических карт); работа со списками (упорядочение и фильтрация записей, поиск данных); коллективная работа с таблицами (обмен файлами в локальной сети, совместное использование и защита данных, обмен данными через Интернет); разработка программных приложений, основанных на встроенном языке программирования VBA (Visual Basic for Application).
Элементы интерфейса пользователя Excel • • • Строка формул – это панель в верхней части окна Excel, которая используется для ввода и редактирования содержимого ячейки. Содержимым ячейки может быть как постоянное значение (например, число или текст), так и формула. Поле имени – это текстовое окно слева от строки формул, в котором отображается имя выделенной ячейки либо элемент диаграммы. В этом поле можно быстро переопределить имя ячейки. Рабочая область листа – это графическое представление электронных таблиц. Рабочая область состоит из ячеек и заголовков строк и столбцов. Вкладки листов – эти элементы расположены в нижней части окна. Они напоминают вкладки в каталожном ящике. Щелчок мышью по какой-либо из вкладок открывает соответствующий лист рабочей книги. Граница вкладок листов – вертикальная черта правее вкладок листов, которая определяет размер области вкладок. Протянув за эту границу, можно изменить размер области вкладок.
Рабочие книги После запуска программы Excel по умолчанию будет раскрыт документ Книга 1. Интерфейс Excel является многодокументным, т. е. вы можете открывать другие документы электронных таблиц, однако в любой момент времени работать можно только с одним активным документом. Файлы рабочих книг имеют расширение. xls, например, именем файла может быть Книга 1. xls. В Excel предусмотрено также расширение. xlt для шаблонов документов.
Листы Рабочая книга состоит из листов, которые пронумерованы (Лист1, Лист2 ит. д. ) и размечены сеткой линий. Имена листов отображаются на вкладках (ярлычках) в нижней части окна книги над строкой состояния.
Ячейки Ячейка – это минимальный элемент электронной таблицы, который имеет адрес, составленный из имени столбца и имени строки, на пересечении которых расположена данная ячейка. Например, в записях А 1, В 2, С 6 буквами обозначаются столбцы, а цифрами – строки. Максимальное число столбцов, расположенных на листе, – 256. Первые столбцы обозначаются одним буквенным символом А, В, С и т. д. , а затем идут столбцы с двумя буквами АА, АВ, АС…IV. Ширина столбца измеряется в символах (мах=255). Максимальное число строк также ограничено и равно 65536 (216). Высота строки измеряется в пунктах (от 0 до 409).
Ввод данных в таблицу Excel – это запись в ячейки некоторых значений либо формул. Возможны два варианта ввода данных с клавиатуры: • ввод непосредственно в ячейку; • ввод в строку формул.
Редактирование данных Изменение данных • Операции изменения данных в ячейках очень просты. Можно использовать один из следующих приемов: • для замены данных на новые выделить ячейку и набрать в ней новые данные; • для редактирования внутри ячейки щелкнуть мышью по ячейке (активизация ячейки) и затем сделать двойной щелчок по ячейке или нажать F 2 (в ячейке появится курсор); • для редактирования в строке формул активизировать нужную ячейку, щелкнуть в строке формул и изменить данные. Завершается редактирование нажатием на клавишу Enter или щелчком вне редактируемой ячейки.
Редактирование данных Перемещение и копирование данных Операции копирования и перемещения данных могут осуществляться в Excel с помощью стандартных средств: • Drag and Drop; • буфер обмена; • маркер заполнения.
Редактирование данных Автозаполнение Существенно упростить ввод данных в электронную таблицу можно с помощью средства автозаполнения, которое обеспечивает заполнение ячеек данными из определенных последовательностей, предусмотренными в Excel. Такими последовательностями являются, например, дни недели, названия месяцев, прогрессии.
Форматирование данных Данные, вводимые в ячейки электронной таблицы, должны иметь определенный формат. Под форматом ячеек в Excel подразумевается весьма обширный набор параметров. Одни параметры задают способ отображения содержимого ячейки (шрифт, начертание текста, цвет букв, выравнивание в ячейке и т. д. ). Другие параметры определяют формат самой ячейки (размер, заливка ячейки, обрамление и т. д. ). К параметрам форматирования в Excel относится также формат данных: числовой, текстовый, денежный, дата и т. д.
Форматы данных Числа Для представления чисел в Excel существует ряд форматов. Просмотреть список этих форматов можно, выполнив команду Формат – Ячейки. Когда вводятся данные в ячейку, по умолчанию используется общий числовой формат. В этом формате можно вводить целые числа, десятичные дроби, а также числа в экспоненциальной форме. Перед отрицательным числом ставится знак минус либо оно заключается в круглые скобки. Число, введенное в ячейку, размещается в крайних правых позициях ячейки. Для ввода чисел предусмотрены встроенные числовые форматы. Эти форматы разделяются в Excel на категории: Денежный, Финансовый, Дата, Время, Процентный, Дробный, Текстовый и Экспоненциальный. Имеется также категория Дополнительный формат, которая включает в себя почтовые индексы и телефонные номера.
Форматы данных Логические значения Логическими значениями являются Истина и Ложь. Эти значения можно вводить в ячейки непосредственно с клавиатуры, а можно воспользоваться соответствующими встроенными функциями. Для логических функций используется общий числовой формат. Внутри ячейки слова Истина и Ложь располагаются по умолчанию по центру – в этом состоит их отличие от других чисел и от обычного текста.
Форматы данных Текст Для программы Excel признаком текста является наличие в записи пробелов и(или) нецифровых символов. Ячейки могут вмещать весьма объемные текстовые фрагменты – до 32767 символов. В отличие от чисел, текст в ячейке выравнивается по левому краю. Текст, набираемый в процессе ввода в ячейку, можно просмотреть в строке формул либо в самой ячейке. Если введенный текст достаточно длинный, то после ввода он будет полностью отображаться на рабочем листе лишь в случае, когда соседние справа ячейки пустые.
Форматы данных Дата и время В Excel могут обрабатываться данные, отвечающие дате и времени суток. Даты и время являются в Excel числами, причем их представление зависит от числового формата, назначенного ячейке. Как известно, программа Excel при вводе данных выполняет их автоматическое распознавание. Если данные напоминают «временной» формат, то программа присвоит им определенный формат даты или времени.
Работа с формулами Основным инструментом для обработки данных в Excel являются формулы. Формулы в Excel представляют собой выражения, описывающие вычисления в ячейках.
Компоненты формул Используя формулы, можно сравнивать данные в ячейках, складывать и умножать значения ячеек, находить средние значения, объединять содержимое ячеек и т. д. Формулы вписываются в строку формул и могут включать следующие компоненты: • Символ = , которым начинается запись формулы (этот символ вводится в ячейку с клавиатуры либо вызывается щелчком по кнопке = в строке формул); • Операторы, то есть инструкции для выполнения действий (например, +, -, *, и т. д. ); • Числа или текстовые значения (например, 0, 12 или Доход); • Функции, выбираемые из набора встроенных функций Excel (например, СУММ или COS); • Ссылки на ячейки и диапазоны – эти компоненты присутствуют, если в формулу нужно подставить значения, содержащиеся в других ячейках (например, А 2, С 3: С 15).
Операторы в Excel В таблицах Excel имеются четыре вида операторов: • арифметические; • текстовые; • операторы сравнения; • операторы ссылок (адресные операторы).
Арифметические операторы Эти операторы служат для выполнения арифметических операций над числами. В Excel могут применяться семь арифметических операторов. Символ оператора + / * ^ % Название оператора сложение вычитание отрицание деление умножение возведение в степень процент Пример формулы =1, 2+5, 5 =6 -5, 5 =-3, 3 =4/5 =5*6 =3^2 =25% Результат 3, 7 0, 5 -3, 3 0, 8 30 9 0, 25
Операторы сравнения При работе с числами и текстом применяются операторы сравнения. Эти операторы используются для присвоения утверждениям значений Истина или Ложь. Если утверждение верно, то ячейке, содержащей формулу, будет присвоено значение Истина. Если же утверждение неверно, то в ячейку будет занесено значение Ложь. Символ Название Пример Результат оператора формулы = Равно =2=3 Ложь > Больше =2>3 Ложь < Меньше =2<3 Истина >= Больше или равно =2>=3 Ложь <= Меньше или равно =2<=3 Истина <> Не равно =2<>3 Истина
Текстовый оператор В Excel имеется один текстовый оператор , или, другим словами амперсанд. Этот оператор объединяет последовательности символов из разных ячеек в одну последовательность, поэтому его называют также оператором объединения. Пусть в ячейки А 1 и А 2 введены слова «паро» и «воз» соответственно, а в ячейку А 3 записана формула =А 1 А 2, тогда результатом в ячейке А 3 будет слово «паровоз» .
Приоритет операторов • Адресные операторы (: , пробел). • Отрицание (используется для указания отрицательных чисел). • Процент (%). • Возведение в степень (^). • Умножение и деление. • Сложение и вычитание. • Объединение последовательностей символов. • Операторы сравнения.
Относительные и абсолютные ссылки Относительные ссылки – при копировании изменяются. Если вы записали в ячейку А 3 формулу =А 1+А 2 и скопировали эту формулу в ячейку В 5, то в итоге получите формулу =В 3+В 4. Абсолютные ссылки - не изменяются при копировании формулы. Абсолютные ссылки отличаются от относительных наличием знака доллара $. Например, рассматриваемая формула, записанная в абсолютных ссылках, будет выглядеть как =$A$1+$A$2, и эта формула не будет изменяться при копировании или перемещении в любую ячейку.
Диагностика ошибок в формулах #### - ширина ячейки недостаточна для размещения в ней числа, даты или времени. Расширить ячейку или изменить формат числа. #ИМЯ? – невозможность распознать используемое имя. Эта ошибка возникает, когда неправильно указано имя объекта. #ЗНАЧ! – попытка некорректного использования функции. . #ЧИСЛО! – появляется при неправильном представлении или использовании чисел. #ССЫЛКА! – означает неправильное употребление ссылок, имеющихся в формуле. #ДЕЛ/0! – попытка деления на нуль. #ПУСТО! – значение ошибки, появляющееся при задании в ссылке пустого множества ячеек. #Н/Д – сокращение от термина «неопределенные данные» . Это значение ошибки обычно специально вводят в ячейки, чтобы предотвратить вычисления в этих ячейках (например, при отсутствии данных). Когда необходимые данные появляются, их просто вводят в формулу.
ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ
Синтаксис функций Запись функции начинается с указания имени функции, затем следует список аргументов, заключенный в скобки. К примеру, функция нахождения максимального значения среди аргументов число 1, число 2 … имеет вид =МАКС(число 1, число 2 …) Аргументы – это величины, которые используются для вычисления значения функции. Результат вычисления функции называется возвращаемым значением.
Синтаксис функций =СУММ(1, 10, В 2: В 8) Эта формула возвращает значение, равное сумме чисел 1, 10 и чисел в ячейках от В 2 до В 8. В ЭТ Excel допускается вложение функций друг в друга, то есть использование значения, возвращаемого одной функцией, в качестве аргумента для другой функции. Функция, которая является аргументом в другой функции, называется вложенной. Например, в записи =СУММ(А 1: С 5, МАКС(D 1: E 3)) функция МАКС, которая возвращает максимальное значение в диапазоне D 1: E 3, является вложенной.
Ввод функций Мастер функций вызывается командой: Вставка – Функция… Два списка: Категория и Функция. 10 категорий: • финансовые; • инженерные; • дата и время; • математические и т. д. • 10 недавно использовавшихся.
1. АВТОСУММИРОВАНИЕ Суммирование выделенных ячеек Выделить ячейки, содержимое которых нужно сложить, а затем щелкнуть по кнопке (Автосумма), расположенной на панели инструментов. Суммирование с автоматическим выделением слагаемых Активизировать ячейку, в которую нужно поместить сумму, и щелкнуть по кнопке . Программа занесет в текущую ячейку формулу (в данном случае =СУММ(А 1: А 5)) и выделит движущейся прерывистой линией те ячейки, которые будут просуммированы. Нажать Enter и получится результат в текущей ячейке.
2. Суммирование ячеек, удовлетворяющих определенному критерию СУММЕСЛИ(диапазон; условие; диапазон_суммирования) – группа математических функций. Функция предназначена для суммирования только ячеек, удовлетворяющих некому критерию. • диапазон – это диапазон, в котором определяется критерий; • условие – указывается в форме числа, выражения или текста; • диапазон-суммирования – это диапазон суммируемых ячеек. =СУММЕСЛИ(A 1: A 5; ">=1000")
3. Подсчет количества числовых значений в диапазоне СЧЕТ(значение 1; значение 2; …) – группа статистических функций =СЧЕТ(В 1: В 5)
4. Подсчет количества значений в диапазоне СЧЕТ 3(значение 1; значение 2; …) – группа статистических функций =СЧЕТ 3(В 1: В 5)
5. Подсчет количества пустых ячеек в диапазоне СЧИТАТЬ ПУСТОТЫ(диапазон) – группа статистических функций =СЧИТАТЬ ПУСТОТЫ(D 1: D 5)
6. Подсчет количества непустых ячеек в диапазоне, удовлетворяющих заданному условию СЧЕТЕСЛИ(диапазон; условие) – группа статистических функций. • диапазон – это диапазон, в котором определяется критерий; • условие – указывается в виде числа, выражения или текста и определяет какие ячейки надо подсчитывать. =СЧЕТЕСЛИ(Е 1: Е 5; “>=1000”)
7. Расчет среднего значения СРЗНАЧ(диапазон 1; диапазон 2; …) – группа статистических функций = СРЗНАЧ (Е 1: Е 5)
8. Определение максимального значения МАКС(диапазон 1; диапазон 2; …) – группа статистических функций. =МАКС(A 1: A 5)
9. Определение минимального значения МИН(диапазон 1; диапазон 2; …) – группа статистических функций. =МИН(A 1: A 5)
10. Функция текущей даты СЕГОДНЯ() – возвращает текущую дату компьютера
11. Функция текущей даты и времени ТДАТА() – возвращает текущую дату и время в числовом формате
12. Функция определения дня недели ДЕНЬНЕД(дата_как_число; тип) – преобразует дату в числовом формате в номер дня недели. Если тип не указан или равен 1, то первым днем недели считается воскресенье, последним (7 -м) – суббота. Если тип равен 2, первый день недели – понедельник.
13. Функция определения номера месяца МЕСЯЦ(дата_как_число) – преобразует дату в числовом формате в номер месяца
Пример 1. Определить день недели даты рождения G 8 – занесем дату рождения в числовом формате: например, 14. 09. 1994. G 9 – введем формулу =ДЕНЬНЕД(G 9; 2) G 9=3
Функция определения количества дней между двумя датами ДНЕЙ 360(НАЧАЛЬНАЯ_ДАТА; КОНЕЧНАЯ_ДАТА; МЕТОД) Пример 13. Определить количество дней от даты рождения по текущую дату. Пошаговыми действиями Мастера функций в ячейку G 10 ввести формулу =ДНЕЙ 360(G 8; СЕГОДНЯ()).
14. Функция проверки условия ЕСЛИ(условие; выражение 1; выражение 2) – группа логических функций. В текущую ячейку заносится величина, вычисленная в соответствии с выражением 1, если условие (одно или несколько) истинно; в противном случае эта величина вычисляется по выражению 2. =ЕСЛИ(А 11>1000; A 11/10)
15. Использование функции И ЕСЛИ(И(условие 1; условие 2); выражение 1; выражение 2). Вычисления выражения 1 выполняется только при истинности всех указанных условий; в противном случае вычисляется выражение 2. =ЕСЛИ(И(А 11>900; A 11<1500); A 11*10; A 11)
16. Использование функции ИЛИ ЕСЛИ(ИЛИ(условие 1; условие 2); выражение 1; выражение 2). В случае истинности одного из условий (условия 1 или условия 2) расчет текущей величины выполняется по выражению1; в противном случае выполняется расчет по выражению 2. =ЕСЛИ(ИЛИ(А 11>1000; A 11<1000); A 11*10; A 11)
Скачать презентацию Электронные таблицы Что такое электронные таблицы Электронная
ЭТ.ppt