Табличный процессор Microsoft Excel

Информатика

Тема 8. Табличный процессор M icrosoft Excel

Электронные таблицы

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

Функциональные возможности EXCEL

Табличный процессор Excel позволяет:

• Решать математические задачи:

• выполнять табличные вычисления (в том числе как обычный калькулятор), вычислять значения и исследовать функции, строить графики функций (например, sin , cos , tg и др.), решать уравнения, работать с матрицами и комплексными числами и т. п.
• выполнять табличные вычисления (в том числе как обычный калькулятор),
• вычислять значения и исследовать функции,
• строить графики функций (например, sin , cos , tg и др.),
• решать уравнения,
• работать с матрицами и комплексными числами и т. п.

2. Осуществлять математическое моделирование и численное экспериментирование

• Что будет, если? Как сделать, чтобы?
• Что будет, если?
• Как сделать, чтобы?

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

4. Реализовать функции базы данных

• ввод, поиск, сортировку, фильтрацию (отбор) анализ данных.
• ввод,
• поиск,
• сортировку,
• фильтрацию (отбор)
• анализ данных.

Функциональные возможности EXCEL

5. Вводить пароли или устанавливать защиту некоторых (или всех) ячеек таблицы, скрывать (прятать) фрагменты таблицы или всю таблицу.

6. Наглядно представлять данные в виде диаграмм и графиков.

7. Вводить и редактировать тексты, как в текстовом процессоре, создавать рисунки с помощью графического редактора Microsoft Office .

8. Осуществлять импорт-экспорт, обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях, и т. п.

9. Осуществлять многотабличные связи (например, объединять отчеты филиалов фирм).

Основные понятия электронных таблиц

• Документ MS Excel называют рабочей книгой .
• Рабочая книга представляет собой набор рабочих листов.
• Каждый рабочий лист имеет название , которое отображается на ярлычке листа .
• Табличное пространство рабочего листа состоит из строк и столбцов .

• максимальное количество столбцов 256 строки нумеруются числами от 1 до 65536.
• максимальное количество столбцов 256
• строки нумеруются числами от 1 до 65536.

Имена столбцов

Адрес текущей ячейки

Строка формул

Диапазон ячеек

Номера строк

Ярлычки рабочих листов

Ввод данных в ячейки

• В ячейки можно вводить данные различных типов , включая:
• Текст - любые данные, над которыми нет необходимости выполнять арифметические операции;
• Числа – числовые значения в различных форматах:

• 36 45,23 2Е-2
• 36
• 45,23
• 2Е-2

• Даты/время

• 18.04.01; 18-апр-01;
• 18.04.01;
• 18-апр-01;

• Формулы (включая функции)

• =12+34 =А2+В2
• =12+34
• =А2+В2

Вычисления в таблицах

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

• + сложение - вычитание * умножение / деление ^ возведение в степень
• + сложение
• - вычитание
• * умножение
• / деление
• ^ возведение в степень

• В правом нижнем углу ячейки, куда была введена формула маркер заполнения .
• При наведении на него указатель мыши приобретает вид тонкого черного крестика.
• Перетаскивание маркера позволяет копировать формулу в горизонтальном или вертикальном направлении.
• Этот метод называется автозаполнением .

Маркер заполнения

Форматирование ячеек

Для выполнения форматирования электронных таблиц необходимо:

• выбрать соответствующую ячейку или выделить диапазон ячеек ;
• выбрать пункт меню Формат~Ячейки ~;
• выбрать соответствующую закладку :

Листы с закладками служат для выполнения следующих функций:

• Число – настройка форматов чисел;
• Выравнивание – форматирование положения данных в ячейках;
• Шрифт – форматирование шрифтов данных;
• Граница – выбор обрамления таблицы;
• Вид – выбор способа заливки ячеек;
• Защита – защита ячеек и скрытие формул.

Построение диаграмм в электронных таблицах

• Выбор типа диаграммы зависит как от характера данных, так и от того, как вы хотите их представить.
• Наиболее часто используются следующие типы диаграмм :

• Круговая . Используется для отображения относительного соотношения между частями целого. Гистограмма . Используется для иллюстрации соотношения отдельных значений данных. Линейчатая . Используется для сравнения значений в определенный момент времени. График . Используется для отображения тенденций изменения данных за равные промежутки времени. С областями . Используется для подчеркивания величины изменения в течении определенного периода времени.
• Круговая . Используется для отображения относительного соотношения между частями целого.
• Используется для отображения относительного соотношения между частями целого.
• Гистограмма . Используется для иллюстрации соотношения отдельных значений данных.
• Используется для иллюстрации соотношения отдельных значений данных.
• Линейчатая . Используется для сравнения значений в определенный момент времени.
• Используется для сравнения значений в определенный момент времени.
• График . Используется для отображения тенденций изменения данных за равные промежутки времени.
• Используется для отображения тенденций изменения данных за равные промежутки времени.
• С областями . Используется для подчеркивания величины изменения в течении определенного периода времени.
• Используется для подчеркивания величины изменения в течении определенного периода времени.

Основные термины используемые в диаграммах

• Ряд данных – группа ячеек в пределах одного столбца или одной строки.
• Категории - отражают количество элементов в ряду. На рисунке 7 представлено 6 категорий для каждого ряда (данные за январь, февраль, март и т.д.)
• Легенда -о пределяет отдельные элементы диаграммы.
• Сетка – продолжение деления осей, улучшает восприятие и анализ данных на диаграмме.

Заголовок диаграммы

Сетка

Ряд данных

Легенда

Подписи оси Х

Построение диаграмм

• построения диаграммы используют Мастер диаграмм , запускаемый щелчком на кнопке Мастер диаграмм на стандартной панели инструментов
• Построение диаграмм состоит из нескольких этапов:

• Выбор типа диаграммы . На этом этапе выбирают форму диаграммы.
• Выбор типа диаграммы . На этом этапе выбирают форму диаграммы.

• Тип на вкладке Стандартные или Нестандартные Вид
• Тип на вкладке Стандартные или Нестандартные Вид
• Тип на вкладке Стандартные или Нестандартные
• Вид

Построение диаграмм

• Выбор данных .

• Если данные образуют единый прямоугольный диапазон, то их удобно выбирать при помощи вкладки Диапазон данных.

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

Построение диаграмм

• Оформление диаграммы .

• На вкладках оформления задаются:

• название диаграммы, подписи осей (вкладка Заголовки ); отображение и маркировка осей (вкладка Оси ); отображение сетки линий, параллельных осям координат (вкладка Линии сетки ); описание построенных графиков (вкладка Легенда ); отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных ); представление данных, использованных пи построении графика, в виде таблицы (вкладка Таблица данных ).
• название диаграммы,
• подписи осей (вкладка Заголовки );
• отображение и маркировка осей (вкладка Оси );
• отображение сетки линий, параллельных осям координат (вкладка Линии сетки );
• описание построенных графиков (вкладка Легенда );
• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных );
• представление данных, использованных пи построении графика, в виде таблицы (вкладка Таблица данных ).

Построение диаграмм

• Размещение диаграммы .

• Указывается следует ли использовать для размещения отдельный лист или один из имеющихся .
• Указывается следует ли использовать для размещения отдельный лист или один из имеющихся .

Построение диаграмм

Использование стандартных функций

• Функция в EXCEL определяется, как заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке.
• Стандартные функции используются в электронных таблицах только в формулах.
• Вызов функции состоит в указании в формуле имени функции , после которого в скобках указывается список параметров:

• =СУММ(D2:D7)
• =СУММ(D2:D7)

• Отдельные параметры разделяются в списке точкой с запятой.

• =СУММ(D2:D7;B2:B7;C2:C7)
• =СУММ(D2:D7;B2:B7;C2:C7)

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

Использование Мастера функций

• Мастер функций облегчает ввод и выбор нужной функции.

• В списке Категория выбирается категория, к которой относится функция в списке Функция – конкретная функция данной категории.
• В списке Категория выбирается категория, к которой относится функция
• в списке Функция – конкретная функция данной категории.

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

Обязательный параметр

Список функций

Необязательный параметр

Значение параметра

Описание параметра

Значение функции

Описание функции

Операции по обработке данных в электронных таблицах. Сортировка

• Сортировка – это упорядочение данных по возрастанию или убыванию.
• Для выполнения сортировки нужно выполнить следующие действия:

• Выбрать в таблице какую-либо непустую ячейку. Выполнить команду Данные - Сортировка . На экране появиться диалоговое окно Сортировка диапазона . В списке Сортировать указать поле, по которому будет сортироваться таблица. Если выбран переключатель Идентифицировать поля по подписям (первая строка диапазона) , то в выпадающем списке будут приведены названия столбцов, содержащиеся в строке1. Если же отмечена опция Идентифицировать поля по обозначениям столбцов листа , то первая строка рассматривается как обыкновенная запись, а не как ряд имен полей. В этом случае в выпадающем списке названия столбцов будут приведены в следующем виде: Столбец А, Столбец В, Столбец С и т.д. Выбрать порядок сортировки: по возрастанию или по убыванию. Для сортировки сразу по нескольким полям используются списки Затем по и В последнюю очередь , по . Нужно указать в них поля, по которым нужно будет упорядочивать данные, если значения предыдущих полей совпадут. Щелкните на кнопке ОК, чтобы отсортировать данные в указанном порядке.
• Выбрать в таблице какую-либо непустую ячейку.
• Выполнить команду Данные - Сортировка . На экране появиться диалоговое окно Сортировка диапазона .
• В списке Сортировать указать поле, по которому будет сортироваться таблица.
• Если выбран переключатель Идентифицировать поля по подписям (первая строка диапазона) , то в выпадающем списке будут приведены названия столбцов, содержащиеся в строке1. Если же отмечена опция Идентифицировать поля по обозначениям столбцов листа , то первая строка рассматривается как обыкновенная запись, а не как ряд имен полей. В этом случае в выпадающем списке названия столбцов будут приведены в следующем виде: Столбец А, Столбец В, Столбец С и т.д.
• Выбрать порядок сортировки: по возрастанию или по убыванию. Для сортировки сразу по нескольким полям используются списки Затем по и В последнюю очередь , по . Нужно указать в них поля, по которым нужно будет упорядочивать данные, если значения предыдущих полей совпадут.
• Щелкните на кнопке ОК, чтобы отсортировать данные в указанном порядке.

• Быстро отсортировать данные можно с помощью кнопок на панели инструментов

• Сортировка по возрастанию, чтобы отсортировать значения в возрастающем порядке или Сортировка по убыванию , чтобы отсортировать значения в убывающем порядке.
• Сортировка по возрастанию, чтобы отсортировать значения в возрастающем порядке или
• Сортировка по убыванию , чтобы отсортировать значения в убывающем порядке.

Сортировка данных

Фильтрация данных

• Фильтрация списка - вывод на экран только тех записей (строк), которые отвечают определенному критерию (условию)

Подведения итогов

• Меню Данные - Итоги

Подведения итогов

• Работа со структурой списка

Определения

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

Основные операции

• Решать математические задачи: выполнять вычисления и исследовать функции, строить графики функций, решать уравнения, работать с матрицами и комплексными числами и т. п.
• Осуществлять математическое моделирование и численное экспериментирование
• Проводить статистический анализ, осуществлять прогнозирование и оптимизацию.
• Реализовать функции базы данных - ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных.

Основные операции

• Вводить пароли или устанавливать защиту ячеек таблицы, скрывать фрагменты таблицы или всю таблицу.
• Наглядно представлять данные в виде диаграмм и графиков.
• Осуществлять импорт-экспорт, обмен данными с другими программами
• Осуществлять многотабличные связи
• Подготавливать выступления, доклады и презентации благодаря встроенному режиму презентаций.

. Основные понятия

• Документ MS Excel называют рабочей книгой .
• Рабочая книга представляет собой набор рабочих листов , каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц.
• Каждый рабочий лист имеет название , которое отображается на ярлычке листа , отображаемом в его нижней части.

Ярлычки рабочих листов

Основные понятия

• Табличное пространство рабочего листа состоит из строк и столбцов .
• Столбцы озаглавлены латинскими буквами (максимальное количество столбцов 256).
• Строки последовательно нумеруются числами от 1 до 65536

Имена столбцов

Номера строк

• На пересечении столбцов и строк образуются ячейки таблицы.
• Каждая ячейка имеет адрес , который сочетает в себе номера столбца и строки, на пересечении которых она расположена.
• Диапазон ячеек – данные расположенные в соседних ячейках, на которые можно ссылаться, как на единое целое

Адрес текущей ячейки

Строка формул

Диапазон ячеек

С1:С13

Типы данных

• Текст - любые данные, над которыми нет необходимости выполнять арифметические операции;
• Числа – числовые значения в различных форматах: 36; 45,23 ; 2Е-2;
• Даты/время – 18.04.01; 18-апр-01;
• Формулы (включая функции) - =12+34; =А2+В2.
• и т.д.

Вычисления в ЭТ

• Формула представляет собой математическое выражение, которое начинается со знака равно и может содержать числовые константы, ссылки на ячейки и функции Excel , соединенные знаками математических операций (+,-,*,/,^)

Вычисления в таблицах

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

• + сложение - вычитание * умножение / деление ^ возведение в степень
• + сложение
• - вычитание
• * умножение
• / деление
• ^ возведение в степень

• В правом нижнем углу ячейки, куда была введена формула маркер заполнения .
• При наведении на него указатель мыши приобретает вид тонкого черного крестика.
• Перетаскивание маркера позволяет копировать формулу в горизонтальном или вертикальном направлении.
• Этот метод называется автозаполнением .

Маркер заполнения

Форматирование ячеек

Для выполнения форматирования электронных таблиц необходимо:

• выбрать соответствующую ячейку или выделить диапазон ячеек ;
• выбрать пункт меню Формат~Ячейки ~;
• выбрать соответствующую закладку :

Листы с закладками служат для выполнения следующих функций:

• Число – настройка форматов чисел;
• Выравнивание – форматирование положения данных в ячейках;
• Шрифт – форматирование шрифтов данных;
• Граница – выбор обрамления таблицы;
• Вид – выбор способа заливки ячеек;
• Защита – защита ячеек и скрытие формул.

Построение диаграмм в электронных таблицах

• Выбор типа диаграммы зависит как от характера данных, так и от того, как вы хотите их представить.
• Наиболее часто используются следующие типы диаграмм :

• Круговая . Используется для отображения относительного соотношения между частями целого. Гистограмма . Используется для иллюстрации соотношения отдельных значений данных. Линейчатая . Используется для сравнения значений в определенный момент времени. График . Используется для отображения тенденций изменения данных за равные промежутки времени. С областями . Используется для подчеркивания величины изменения в течении определенного периода времени.
• Круговая . Используется для отображения относительного соотношения между частями целого.
• Используется для отображения относительного соотношения между частями целого.
• Гистограмма . Используется для иллюстрации соотношения отдельных значений данных.
• Используется для иллюстрации соотношения отдельных значений данных.
• Линейчатая . Используется для сравнения значений в определенный момент времени.
• Используется для сравнения значений в определенный момент времени.
• График . Используется для отображения тенденций изменения данных за равные промежутки времени.
• Используется для отображения тенденций изменения данных за равные промежутки времени.
• С областями . Используется для подчеркивания величины изменения в течении определенного периода времени.
• Используется для подчеркивания величины изменения в течении определенного периода времени.

Основные термины используемые в диаграммах

• Ряд данных – группа ячеек в пределах одного столбца или одной строки.
• Категории - отражают количество элементов в ряду. На рисунке 7 представлено 6 категорий для каждого ряда (данные за январь, февраль, март и т.д.)
• Легенда -о пределяет отдельные элементы диаграммы.
• Сетка – продолжение деления осей, улучшает восприятие и анализ данных на диаграмме.

Заголовок диаграммы

Сетка

Ряд данных

Легенда

Подписи оси Х

Построение диаграмм

• построения диаграммы используют Мастер диаграмм , запускаемый щелчком на кнопке Мастер диаграмм на стандартной панели инструментов
• Построение диаграмм состоит из нескольких этапов:

• Выбор типа диаграммы . На этом этапе выбирают форму диаграммы.
• Выбор типа диаграммы . На этом этапе выбирают форму диаграммы.

• Тип на вкладке Стандартные или Нестандартные Вид
• Тип на вкладке Стандартные или Нестандартные Вид
• Тип на вкладке Стандартные или Нестандартные
• Вид

Построение диаграмм

• Выбор данных .

• Если данные образуют единый прямоугольный диапазон, то их удобно выбирать при помощи вкладки Диапазон данных.

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

Построение диаграмм

• Оформление диаграммы .

• На вкладках оформления задаются:

• название диаграммы, подписи осей (вкладка Заголовки ); отображение и маркировка осей (вкладка Оси ); отображение сетки линий, параллельных осям координат (вкладка Линии сетки ); описание построенных графиков (вкладка Легенда ); отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных ); представление данных, использованных пи построении графика, в виде таблицы (вкладка Таблица данных ).
• название диаграммы,
• подписи осей (вкладка Заголовки );
• отображение и маркировка осей (вкладка Оси );
• отображение сетки линий, параллельных осям координат (вкладка Линии сетки );
• описание построенных графиков (вкладка Легенда );
• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных );
• представление данных, использованных пи построении графика, в виде таблицы (вкладка Таблица данных ).

Построение диаграмм

• Размещение диаграммы .

• Указывается следует ли использовать для размещения отдельный лист или один из имеющихся .
• Указывается следует ли использовать для размещения отдельный лист или один из имеющихся .

Построение диаграмм

Использование стандартных функций

• Функция в EXCEL определяется, как заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке.
• Стандартные функции используются в электронных таблицах только в формулах.
• Вызов функции состоит в указании в формуле имени функции , после которого в скобках указывается список параметров:

• =СУММ(D2:D7)
• =СУММ(D2:D7)

• Отдельные параметры разделяются в списке точкой с запятой.

• =СУММ(D2:D7;B2:B7;C2:C7)
• =СУММ(D2:D7;B2:B7;C2:C7)

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

Использование Мастера функций

• Мастер функций облегчает ввод и выбор нужной функции.

• В списке Категория выбирается категория, к которой относится функция в списке Функция – конкретная функция данной категории.
• В списке Категория выбирается категория, к которой относится функция
• в списке Функция – конкретная функция данной категории.

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

Обязательный параметр

Список функций

Необязательный параметр

Значение параметра

Описание параметра

Значение функции

Описание функции

Операции по обработке данных в электронных таблицах. Сортировка

• Сортировка – это упорядочение данных по возрастанию или убыванию.
• Для выполнения сортировки нужно выполнить следующие действия:

• Выбрать в таблице какую-либо непустую ячейку. Выполнить команду Данные - Сортировка . На экране появиться диалоговое окно Сортировка диапазона . В списке Сортировать указать поле, по которому будет сортироваться таблица. Если выбран переключатель Идентифицировать поля по подписям (первая строка диапазона) , то в выпадающем списке будут приведены названия столбцов, содержащиеся в строке1. Если же отмечена опция Идентифицировать поля по обозначениям столбцов листа , то первая строка рассматривается как обыкновенная запись, а не как ряд имен полей. В этом случае в выпадающем списке названия столбцов будут приведены в следующем виде: Столбец А, Столбец В, Столбец С и т.д. Выбрать порядок сортировки: по возрастанию или по убыванию. Для сортировки сразу по нескольким полям используются списки Затем по и В последнюю очередь , по . Нужно указать в них поля, по которым нужно будет упорядочивать данные, если значения предыдущих полей совпадут. Щелкните на кнопке ОК, чтобы отсортировать данные в указанном порядке.
• Выбрать в таблице какую-либо непустую ячейку.
• Выполнить команду Данные - Сортировка . На экране появиться диалоговое окно Сортировка диапазона .
• В списке Сортировать указать поле, по которому будет сортироваться таблица.
• Если выбран переключатель Идентифицировать поля по подписям (первая строка диапазона) , то в выпадающем списке будут приведены названия столбцов, содержащиеся в строке1. Если же отмечена опция Идентифицировать поля по обозначениям столбцов листа , то первая строка рассматривается как обыкновенная запись, а не как ряд имен полей. В этом случае в выпадающем списке названия столбцов будут приведены в следующем виде: Столбец А, Столбец В, Столбец С и т.д.
• Выбрать порядок сортировки: по возрастанию или по убыванию. Для сортировки сразу по нескольким полям используются списки Затем по и В последнюю очередь , по . Нужно указать в них поля, по которым нужно будет упорядочивать данные, если значения предыдущих полей совпадут.
• Щелкните на кнопке ОК, чтобы отсортировать данные в указанном порядке.

• Быстро отсортировать данные можно с помощью кнопок на панели инструментов

• Сортировка по возрастанию, чтобы отсортировать значения в возрастающем порядке или Сортировка по убыванию , чтобы отсортировать значения в убывающем порядке.
• Сортировка по возрастанию, чтобы отсортировать значения в возрастающем порядке или
• Сортировка по убыванию , чтобы отсортировать значения в убывающем порядке.

Сортировка данных

Фильтрация данных

• Фильтрация списка - вывод на экран только тех записей (строк), которые отвечают определенному критерию (условию)