Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно

• Название: Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
• Анкор: Конспект лекций по Excell
• Дата: 15.02.2022
• Размер: 3.95 Mb.
• Имя файла: 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12.pdf
• Тип: Конспект лекций; #363174
• страница: 14 из 24

Диапазон данных:
Поле редактирования Диапазон – служит для указания диапазона ис- ходных данных, на основании которых построена диаграмма. Диапазон, в свою очередь, может состоять из отдельных поддиапазонов, разделен- ных символом-разделителем элементов списков.
Переключатель Ряды в – указывает, как расположены ряды данных в указанном диапазоне:
•
строках – если ряды данных расположены в строках, или
•
столбцах – если ряды данных расположены в столбцах.
2.
Вкладка Ряд:
Список Ряд – служит для указания требуемого ряда данных.
Поля редактирования служат для указания:
•
Имя – имени ряда данных, которое используется в легенде.
•
Значения – диапазонов ячеек, которые содержат значения (точки) ряда данных.
•
Подписи оси X – диапазонов ячеек, которые содержат метки оси ка- тегорий X текущего ряда данных.
Кнопки позволяют:
•
Добавить – добавить новый ряд данных. После щелчка по ней необ- ходимо заполнить также поля редактирования Имя, Значения и Под-
писи оси X.
•
Удалить – удалить существующий ряд данных. Предварительно в списке Ряд следует указать подлежащий удалению ряд данных.
Удалить, предварительно выделенный, ряд данных на диаграмме можно:
1)
нажав клавишу Delete, или
2)
по команде Очистить из контекстного меню, или
3)
по команде Правка → Очистить → Прогрессия, или
4)
по щелчку на кнопке Удалить на вкладке Ряд окна диалога Исходные
данные – как было описано выше.
Отдельную точку данных можно удалить, только переопределив ряд
данных полностью.
5.4.
Формула ряда данных
Связь между диаграммой и исходными данными, по которым она по-
строена, осуществляется с помощью формул рядов данных.

129
Содержание каждой их таких формул ряда данных отображается в поле редактирования строки формул при выделении на диаграмме соответствую- щего ряда данных. Например, для первого ряда данных «План» для диаграм- мы представленной на рис. 5.1 она выглядит следующим образом:
Формула ряда данных имеет такой синтаксис:
=РЯД(<имя>, <категории>, <значения>, <порядковый номер>), где:
имя – текстовая константа, или ссылка на ячейку, содержащую имя ряда данных, которое используется в легенде.
категории – абсолютные ссылки на ячейки рабочего листа, содержащие метки оси категорий X.
значения – определяет, какие ячейки рабочего листа содержат значения ряда данных (для оси значений Y).
порядковый номер – устанавливает порядок расположения рядов данных на диаграмме.
Содержимое формулы ряда данных можно изменить:
1)
непосредственно в строке формул.
2)
на вкладке Ряд окна диалога Исходные данные.
5.5.
Пользовательские типы диаграммы
Пользовательский тип диаграммы создается пользователем на основе построенной и отформатированной ранее диаграммы, которая будет служить, затем, в качестве основы (шаблона) для других данных.
Для создания пользовательского типа диаграммы необходимо выпол- нить такие шаги:
1.
Активизировать диаграмму, которая будет использоваться в качестве шаблона.
2.
Открыть окно диалога Тип диаграммы (аналогичное первому окну Масте- ра диаграмм) на вкладке Нестандартные по одноименной команде из кон- текстного, или основного меню Диаграмма.
3.
Переключатель Вывести установить в положение дополнительные. При этом отобразится список дополнительных диаграмм.
4.
Щелкнуть по кнопке Добавить.
5.
В появившемся окне диалога Добавление нового типа диаграмм:

130
а)
ввести в поле редактирования:
Название – название нового пользовательского типа диаграммы.
Описание – описание создаваемого типа диаграмм.
б)
нажать кнопку OK.
В результате в окне диалога Тип диаграммы на вкладке Нестандарт-
ные при установке переключателя Вывести в положение дополнительные в списке Тип появится новый пользовательский тип диаграммы
Для удаления не нужного пользовательского типа диаграммы необходимо:
1.
Выделить его в списке Тип.
2.
Щелкнуть по кнопке Удалить.
3.
В появившемся при этом окне сообщения щелкнуть по кнопке:
OK – чтобы подтвердить удаление, или Отмена – чтобы отменить удаление.
Все нестандартные типы диаграмм хранятся в рабочих книгах:
1.
Встроенные нестандартные типы диаграмм – в файле XL8GALRY.XLS, а
2.
Добавляемые пользователем – в XLUSRGAL.XLS.
5.6.
Установка типа диаграммы по умолчанию
Установка типа диаграммы по умолчанию выполняется для того, чтобы при регулярном построении диаграмм использовался один и тот же тип. При этом любой из встроенных стандартных и нестандартных, а также пользова- тельских типов диаграмм можно установить в качестве типа диаграммы, ис- пользуемого по умолчанию.
Для того чтобы изменить тип диаграммы, используемый по умолчанию необходимо:
1.
Активизировать окно диалога Тип диаграммы (аналогичное первому окну
Мастера диаграмм) по одноименной команде из контекстного, или основ- ного меню Диаграмма.
2.
В открывшемся в результате окне диалога:
а)
выбрать требуемый тип диаграммы,
б)
щелкнуть по кнопке Сделать стандартной,

131
в)
нажать кнопку:
Отмена – чтобы закрыть окно диалога Тип диаграммы без применения выбранного типа к текущей диаграмме, или OK – для изменения типа текущей диаграммы.
Установленный таким образом тип диаграммы по умолчанию будет приме- няться далее ко всем, автоматически создаваемым, диаграммам.
5.8.
Размещение на диаграмме текста и рисунков
Текст является неотъемлемой частью диаграммы. Он присутствует в на- звании диаграммы. в надписях к осям, в легенде, в метках строк и столбцов.
После завершения построения диаграммы вы можете отредактировать ее текст. Для этого, находясь в режиме редактирования диаграммы, надо перейти в окне диалога Параметры диаграммы на вкладку Заголовки.
На диаграмме можно также разместить любой текст в произвольном месте.
Для этого необходимо:
1.
Сделать активным не относящийся к тексту элемент диаграммы.
2.
Ввести текст, который при вводе будет отображаться в строке формул.
3.
Выделить мышью диаграмму, при этом введенный текст будет помещен на диаграмме в рамку.
4.
Переместить введенный текст в любое место на диаграмме.
После этого с введенным текстом можно обращаться , как с любым объектом диаграммы (форматировать, редактировать).
Аналогично обстоит дело и с рисунками. Выполнение команды
Вставка → Рисунок основного меню позволяет вставлять произвольные ри- сунки в диаграмму (рис. 5.3.)
Рис. 5.3 – Текст и рисунки на диаграмме

132
ЛЕКЦИЯ № 6.
БАЗЫ ДАННЫХ
Внутренние базы данных Microsoft Excel реализуются с помощью спи-
сков данных – прямоугольных блоков ячеек, содержащих однотипную инфор- мацию. Каждый из таких списков состоит из множества строк, называемых
записями. Каждая из записей, в свою очередь, делится на отдельные части, ко- торые называются полями. Записи характеризуют объект, а поля свойства объекта.
Причем в базах данных существует очень жесткое требование к типу
полей – каждое поле во всех записях базы данных может содержать только
данные одного, вполне определенного, типа. Первая строка в такой базе дан-
ных содержит названия ее полей.
При работе с базами данных на рабочих листах Microsoft Excel может быть одновременно задействовано до 3-х диапазонов ячеек, каждому из кото- рых может быть присвоено специальное имя:
1.
Диапазон базы данных – База_данных – область на рабочем листе, высту- пающая в роли базы данных.
2.
Диапазон критериев – Критерии – область, куда могут записываться кри- терии отбора записей из базы данных.
3.
Диапазон извлеченных значений – Извлечь – область рабочего листа, в которую помещаются данные, извлеченные как из баз данных, так и других источников, каким-либо образом.
Пример организации базы данных на рабочем листе Microsoft Excel со всеми тремя диапазонами приведен на рис. 6.1.
Рис. 6.1 – Пример базы данных на рабочем листе Microsoft Excel

133
Диапазон базы данных может быть определен одним из следующих способов:
1.
Присвоив ему специальное имя База_данных. При этом Excel будет считать его диапазоном базы данных, и автоматически подставлять в соответствующие параметры команд.
Имя диапазону ячеек можно присвоить несколькими способами:
а)
с помощью диалогового окна Присвоение имени, которое раскрывается по команде Вставка → Имя → Присвоить, или
б)
выделив требуемый диапазон ячеек, и указав необходимое имя в поле
Имя, расположенном слева в строке формул.
При этом необходимо учитывать область действия имен. В первом случае можно указать область действия имен, как в пределах всей книги, так и в пределах конкретного листа. Во втором случае – только в пределах книги.
Например, имя База_данных доступно в пределах всей книги, а
Лист1!База_данных – только в пределах рабочего листа Лист1.
2.
Для большинства команд работы со списками диапазон базы данных можно определить непосредственно, указав необходимые адреса в соответствующих полях их параметров.
3.
Диапазон, выделенный перед выполнением команды работы со списком, принимается за диапазон базы данных.
4.
Диапазон базы данных Excel может определить автоматически – для этого достаточно, чтобы активная ячейка находилась в пределах списка. При этом в качестве списка принимается блок ячеек, отделенный от всех ос- тальных ячеек, по крайней мере, хотя бы одной пустой строкой, или пус- тым столбцом, или краем рабочего листа.
Необязательный
диапазон критериев используется при работе с Расширенным фильтром и позволяет задавать самые гибкие условия фильтрации данных. Также как и диапазон базы данных, он может содержать строку заголов- ка, в которой указываются имена полей поиска. Ему можно присвоить предопре- деленное имя Критерии – в этом случае он будет находиться автоматически.

134
Диапазон извлеченных значений предназначен для записи в него ре- зультатов обработки данных. При этом под результатом обработки данных понимается не только извлечение информации из базы данных на рабочем листе в виде списка, но и получение ее из любого источника данных, вообще, в том числе и из внешнего.
Диапазон извлеченных значений обладает следующими свойствами:
1.
Он является не обязательным.
2.
Он может располагаться на любом рабочем листе – не обязательно вместе с диапазоном базы данных.
3.
Он может иметь строку заголовка, с указанием имен требуемых полей.
4.
Для удобства работы ему можно присвоить зарезервированное имя Извлечь.
Диапазоны извлеченных значений могут быть двух видов:
•
неограниченными, и содержать только одну строку – заголовок, и
•
ограниченными – в месте со строкой заголовка указываются и строки, в которые, затем, будет производиться вывод результатов.
При проектировании баз данных в виде списков на рабочих листах MExcel необходимо учитывать следующие соображения и приемы работы с ними:
Списки баз данных, по возможности, располагать на отдельных рабочих листах, и присваивать им зарезервированное имя
База_данных с диапазоном видимости в пределах текущего листа. Если же на рабочем листе располагается несколько списков, то задание одному из них этого имени ограничит применение большинства команд только этим списком.
Текстовая строка заголовка с указанием имен всех полей поможет избежать многих непредвиденных ситуаций при работе с данными.
При ее использовании необходимо учитывать следующие особенности:
1.
Строка заголовка должна быть только первой, и только единственной.
Для организации «многострочных» заголовков необходимо «разламы- вать» строки с помощью «мягкого переноса», вставляемого по нажатию сочетания клавиш Alt + Enter.
2.
Длина имени поля не может превышать 255 символов.
3.
Имена полей в пределах строки заголовка диапазона базы данных долж- ны быть уникальными.
4.
Между строкой заголовка и первой строкой данных не должно быть пус- тых строк. Если же по каким-либо причинам между строкой заголовка и строками данных все-таки нужны пустые строки, то их ячейки необхо- димо, по крайней мере, заполнить пробелами.

135
5.
При ее отсутствии, в качестве строки заголовка будет использована пер- вая строка списка, а при неполном ее заполнении – появятся «безымян- ные» поля.
Ниже списка базы данных должно быть достаточно места для его роста – новые записи добавляются в конец списка, и при обнаружении там непус- тых строк, дальнейший ввод новых данных будет невозможен.
При расположении списка базы данных под неограниченным диапазоном извлеченных значений возможно наложение с потерей данных.
Быстро выделить весь диапазон списка можно по нажатию клавиш
Ctrl + Shift + *, предварительно активизировав любую из его ячеек.
Организация баз данных на рабочих листах в виде списков не может в полной мере заменить специализированные программы – Системы Управления База- ми Данных (СУБД). Microsoft Excel в этом случае целесообразно использо- вать как средство анализа содержимого подобных баз данных.
Над базами данных, как правило, можно выполнять только следующие основные операции:
1.
Добавление новых записей – загрузка базы данных.
2.
Удаление записей.
3.
Редактирование записей.
4.
Сортировка записей.
5.
Поиск и фильтрация записей.
Все они могут быть реализованы с помощью команд основного меню Данные, хотя существуют и другие, иногда даже более эффективные способы.
6.1.
Добавление, удаление и редактирование записей
Добавление новых записей (загрузка базы данных, заполнение, ввод и т.д.) может быть выполнено несколькими различными способами, каждый из которых обладает как своими достоинствами, так и определенными недостатками.
1.
Непосредственный ввод данных – это заполнение ячеек списка непосред- ственно с клавиатуры. При этом с целью его ускорения и предупреждения возможных ошибок, допустимо и целесообразно использование любых средств автоматизации: автозаполнения, автозавершения, выбор из списка и т.д. Это наиболее гибкий, и в тоже время, наиболее неэффективный спо- соб заполнения списка, в смысле контроля выхода за пределы именованно- го диапазона.
Привести в соответствие пределы именованного диапазона можно двумя различными способами:

136
1)
Переопределив его пределы после добавления новых данных с помощью диалогового окна Присвоение имени, которое активизируется по ко- манде Вставка → Имя → Присвоить.
2)
Раздвинув границы именованного диапазона с помощью вставки пустых строк в его пределах.
Это можно выполнить:
По команде Вставка → Строки.
С помощью окна диалога Добавление ячеек – выставив в нем пере- ключатель Добавить в положение строку.
Оно активизируется по команде:
а)
Добавить ячейки – из контекстного меню, или
б)
Вставка → Ячейки – основного меню.
При этом если до выполнения команды было выделено несколько строк, то окно диалога выводиться не будет, а количество добавленных строк будет равно количеству выделенных.
2.
С помощью Формы ввода данных. Этот способ устраняет основной не- достаток непосредственного ввода данных – проблему расширения имено- ванного диапазона при добавлении новых записей. Форма ввода данных представляет собой окно диалога, предназначенное для ввода, отображе- ния, поиска и редактирования только одной строки (записи) информации в базе данных. Оно активизируется по команде Данные → Форма. Во время добавления новых записей с помощью Формы ввода данных они дописываются в конец списка, а именованный диапазон расширяется при этом автоматически.
В заголовке диалогового окна Формы ввода данных отображается назва- ние текущего рабочего листа, на котором расположен список. Для наиболее удобного применения Формы ввода данных при добавлении записей в но- вый список его столбцы должны быть поименованы с помощью строки за-

137 головка. Добавление новых записей выполняется по нажатию кнопки До-
бавить. При этом поля ввода очищаются, и в них можно вводить новые данные в соответствии с именами полей добавляемой записи. Новая запись к списку добавляется также при переходе к другой записи, или закрытии
Формы ввода данных по нажатию клавиши Закрыть. Форма ввода данных позволяет одновременно отображать до 32 полей списка. Она также может быть использована для поиска, редактирования и удаления записей.
3.
С помощью Мастера шаблонов. При этом лист Excel преобразуется в форму для ввода информации, как правило, во внешнюю базу данных. В этом случае исключается возможность с помощью этой формы выполнять такие операции, как просмотр, редактирование, удалении и т.д. Поэтому для дальнейшей работы с такой базой данных необходимо использовать другие средства Microsoft Excel.
Удаление и редактирование записей списка базы данных может вы- полняться при помощи: