Информ технологии Лекции Инф Л 11 Инф

Л 11 Инф

лекция

Лекция №11

Тема: «Возможности динамических электронных таблиц»

Для упрощения составления краткого конспекта лекции, допустимы следующие упрощения:

некоторые фрагменты выделены цветом, их следует записать;

остальные фрагменты написаны обычным (черным) цветом — их записывать не нужно.

Программа MS Excel (электронные таблицы) предназначена для работы с таблицами данных, преимущественно числовых.

Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без проведения расчётов вручную, а также в решении многих задач не применять специального программирования.

Особенность электронных таблиц заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчёт по заданным формулам выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчёту значений всех ячеек, которые с ней связаны формульными отношениями и, тем самым, к обновлению всей таблицы в соответствии с изменившимися данными.

Возможности электронных таблиц:

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

Загрузку программы MS Excel можно выполнить следующими способами:

— двойным щелчком по ярлыку MicrosoftExcel  на рабочем столе, если ярлык там находится;

— выполнением последовательности команд: Пуск, Программы, Стандартные, ярлык MicrosoftExcel;

— выполнением последовательности команд: Пуск, Найти, Файлы и папки. В появившемся диалоговом окне в строке Имя ввести  MicrosoftExcel(имя файла ярлыка программы MS Excel) и щелкнуть по кнопке Найти. После окончания поиска выполнить двойной щелчок по ярлыку MicrosoftExcel. По завершению  загрузки программы MS Excelзакрыть окно поиска.

Загрузка программы MS Excel заканчивается появлением на экране окна программы с открытым рабочим листом  с именем  “Лист1” стандартной рабочей книги с именем  “Книга1”.

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

  • щелчком левой кнопки мышки развернуть меню Сервис, щёлкнуть левой кнопкой мышки по строке Параметры и в появившемся диалоговом окне щёлкнуть мышкой по закладке Общие. В окошечке Листов в новой книге установить требуемое число  листов и щёлкнуть по кнопке Ok;
  • на панели инструментов щёлкнуть по кнопке Создать;
  • щелчком левой кнопки мышки развернуть меню Файл и щёлкнуть мышкой по строке Сохранить как…. В появившемся окне щёлкнуть по стрелке окна Мои документы. В раскрывшемся меню щёлкнуть по строке с адресом вашего каталога, а затем в нижнем окне <Имя файла > вместо стандартного имени записать своё название рабочей книги, после чего щёлкнуть по кнопке Сохранить. В последующем при работе с этим файлом такие действия не выполнять, если не требуется замена имени файла, а достаточно периодически щёлкать по кнопке Сохранить на панели инструментов.

Рабочее окно MS Excel

Основными элементами рабочего окна являются:

  • строка заголовка (в ней указывается имя программы) с кнопками управления окном программы и окном документа (Свернуть, Свернуть в окно или развернуть во весь экран, Закрыть);
  • строка основного меню (каждый пункт меню объединяет набор команд, объединяющих общую функциональную направленность) плюс окно для запроса справки и кнопка Закрыть окно;
  • панели инструментов (Стандартная и Форматирование);
  • поле Имя, вставка функции (fx) и Строка формул. Строка формул предназначена для ввода и редактирования значений или формул в ячейках. В поле Имя отображается адрес текущей ячейки;
  • рабочая область (активный рабочий лист);
  • полоса прокрутки;
  • строка перебора рабочих листов;
  • строка состояния.

Структура электронных таблиц

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

Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру. В окне документа отображается только текущий (активный) рабочий лист, с которым и ведётся работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа в нижней части окна. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же рабочую книгу.

Чтобы переименовать рабочий лист, надо дважды щёлкнуть мышкой на его ярлычке и заменить старое имя на новое или путём выполнения следующих команд: меню Формат, строка Лист в списке меню, Переименовать. А также установить указатель мышки на ярлык активного рабочего листа и щёлкнуть правой кнопкой мыши, после чего в появившемся контекстном меню щёлкнуть по строке Переименовать и выполнить переименование. В рабочую книгу можно добавлять (вставлять) новые листы или удалять ненужные. Вставку листа можно осуществить путём выполнения команд: меню Вставка , строка Лист в списке меню. Вставка листа произойдёт перед активным листом.  Выполнение выше изложенных действий можно осуществить с помощью контекстного меню, которое активизируется нажатием правой кнопки мышки, указатель которой должен быть установлен на ярлычке соответствующего листа. Чтобы поменять местами рабочие листы нужно указатель мышки установить на ярлычок перемещаемого листа, нажать левую кнопку мышки и перетащить в нужное место.

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист содержит 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами, от 1 до 65536.

На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Каждая ячейка имеет свой адрес. Адрес ячейки состоит из имени столбца и номера строки, на пересечении которых расположена ячейка, например, A1, B5, DE324. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. В текущий момент времени активной может быть только одна ячейка, которая активизируется щелчком мышки по ней и выделяется рамкой. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования данных всегда производятся только в активной ячейке.

На данные, расположенные в соседних ячейках, образующих прямоугольную область, можно ссылаться в формулах, как на единое целое. Группу ячеек, ограниченную прямоугольной областью, называют диапазоном. Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие, адрес первой ячейки и адрес последней ячейки диапазона, например, B5:F15. Выделение диапазона ячеек можно сделать протягиванием указателя мышки от одной угловой ячейки до противоположной ячейки по диагонали. Рамка текущей (активной) ячейки при этом расширяется, охватывая весь выбранный диапазон.

Способы адресации ячеек

В MS Excel имеются три способа адресации ячеек: относительная, как показано выше (A7), абсолютная и смешанная. Признаком абсолютной адресации является знак $.

Если знак $ предшествует имени столбца и номеру строки  $C$12, $A$2:$d $24, то будет абсолютный адрес ячейки или диапазона ячеек. Абсолютная адресация применяется в случаях, когда в формулах необходимо осуществлять ссылку на одну и ту же ячейку (один и тот же диапазон ячеек).

Если знак $ предшествует имени столбца $B7, то будет абсолютный адрес столбца. Если знак $ предшествует номеру строки D$23, то будет абсолютный адреса строки. Это примеры смешанной адресации.

Для изменения способа адресации, при редактировании формулы, нужно выделить ссылку на ячейку и нажать клавишу F4. При одном нажатие будет абсолютный адрес ячейки. При двух нажатиях будет абсолютный адрес строки. При трёх нажатиях будет абсолютный адрес столбца. При четырёх  нажатиях будет относительный адрес ячейки.

ОСНОВНЫЕ ДЕЙСТВИЯ В ТАБЛИЧНОМ ПРОЦЕССОРЕ.

Отдельная ячейка может содержать данные, относящиеся к одному из следующих типов: число, дата, текст или формула, а также оставаться пустой.

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

Данные в любой ячейке могут относиться к одному из следующих типов:

  • число;
  • текст;
  • формула.

Числовой тип. Чтобы содержимое ячейки идентифицировалось программой как число, оно должно содержать только цифры, запятую (знак десятичной дроби) или знак минус (для отрицательного числа). Не допускаются посторонние символы и пробелы. Над числами могут производиться математические действия.

Текстовый тип. Последовательность любых символов

Формулы. Вводятся по следующим правилам.

  • Формула должна начинаться со знака равенства.
  • Операндами в формулах являются адреса ячеек, числа и функции.
  • Нельзя опускать знаки математических действий, не допускаются пробелы и посторонние символы.
  • Порядок действий традиционный (сначала — умножение и деление, затем — сложение и вычитание) Порядок действий может быть изменен с помощью круглых скобок. Число открывающих скобок должно быть равным числу закрывающих.

Пример формулы 1

=А5+2*(В5+С5^2)

Содержимое ячейки А5 складывается с удвоенной суммой: содержимое В5 плюс квадрат содержимого С5.

Пример формулы 2

=(А9/В8+(В8+С9)/2)*А10

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

Результат вычисления отобразится сразу же после завершения ввода в той ячейке, в которую введена формула.

2. Техника ввода формул

Адреса ячеек в формулах можно набирать на клавиатуре, а можно указывать на них щелчком левой кнопки мыши.

Если адрес набирается на клавиатуре, то раскладка клавиатуры должна быть латинской. Регистр значения не имеет.

3. Автоматический пересчет адресов при копировании ячеек с формулами.

Если ячейка с формулой копируется, то по умолчанию программа производит пересчет адресов в формулах.

Приведем пример расчета стоимости закупки.

Вводим  формулу  в  верхнюю  ячейку  и  копируем  ее  на  смежные  нижние  ячейки.  Номера строк в формуле пересчитаются автоматически.

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

Чтобы просуммировать содержимое ячеек, выделите их и щелкните по кнопке Автосумма на вкладке Главная.

2. Абсолютные и относительные адреса ячеек

Иногда от автоматического пересчета адресов при копировании формул приходится отказываться.

Рассмотрим простой пример. Требуется подсчитать общую стоимость трех видов товаров, которых должно быть закуплено, например, 8 комплектов.

Формулы для решения этой задачи показаны ниже.

В формулах в столбце D присутствуют три сомножителя, у двух из которых адреса должны пересчитаться, а у третьего — остаться неизменным. Это означает, что в таком виде формула не может быть скопирована на соседние ячейки, т.к. в этом случае пересчитается и адрес B3.

Чтобы иметь возможность копировать формулы такого рода, необходимо сообщить программе, что некоторый адрес должен оставаться неизменным. Это делается с помощью использования абсолютной адресации. Если в ячейку D7 ввести адрес в виде $B$3, то знаки доллара покажут, что данный адрес остается неизменным, и теперь эту формулу можно копировать.

Используется также смешанная адресация: например, $B3 или B$3.

Чтобы превратить относительный адрес в абсолютный, необходимо при ссылке на ячейку нажать клавишу F4.

3. Форматы данных

Форматирование текста в ячейках таблицы EXCEL многом сходно с форматированием в текстовом процессоре WORD. Одинаково выполняются в этих двух программах: выравнивание текста, смена шрифта, его стиля, размера и цвета.

Познакомимся с форматированием числовых данных. Напомним, что основное отличие между текстом и числовыми данными в EXCEL заключается в том, что над последними можно выполнять математические действия.

Формат числовых данных для конкретной ячейки или для диапазона можно установить в диалоговом окне Формат ячеек на вкладке Число. В это окно можно попасть через горизонтальное меню (Формат — Ячейки) или контекстное меню (щелчок правой кнопкой мыши и Формат ячеек).

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

4. Построение графиков

Для построения диаграммы проще всего сначала выделить диапазон таблицы, в котором находятся исходные данные, а затем выбрать тип диаграммы на вкладке Вставка.

После этого диаграмма появится, но ее необходимо отредактировать. Познакомьтесь с терминами, используемыми табличным процессором.

Редактировать элементы диаграммы удобно, пользуясь кнопками на вкладке Макет.