Использование электронных таблиц как баз данных

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

Информация в базе данных состоит из набора записей, каждая из которых содер­жит один и тот же набор полей. Записи характеризуются порядковыми номерами, а каждое поле имеет заголовок, описывающий его назначение.

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

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

Так как база данных может включать огромное число записей (в программе Excel естественным пределом служит максимальное число строк рабочего листа — 65536), не всегда требуется отображать все эти записи. Выделение подмножества общего набора записей называется фильтрацией. Наиболее простым способом фильтра­ции в программе Excel является использование автофильтра.

Применение автофильтра. Включение режима фильтрации осуществляется коман­дой Данные → Фильтр → Автофильтр. При этом для каждого поля базы данных авто­матически создается набор стандартных фильтров, доступных через раскрываю­щиеся списки. Раскрывающие кнопки этих списков отображаются возле поля заголовка каждого столбца (рис. 13.13).

По умолчанию используется вариант Все, указывающий, что записи базы данных должны отображаться без фильтрации. Вариант Первые 10 позволяет отобрать опре­деленное число (или процент) записей по какому-либо критерию. Вариант Усло­вие позволяет задать специальное условие фильтрации. Кроме того, имеется воз­можность отбора записей, имеющих в нужном поле конкретное значение.

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

При выборе расширенной фильтрации командой Данные → Фильтр → Расширенный фильтр можно выполнить фильтрацию на месте или извлечь отфильтрованные записи и поместить их отдельно, на любой рабочий лист любой открытой рабочей книги.

Первоначальное построение сводной таблицы производится с помощью Мастера сводной таблицы. Для этого служит команда Данные → Сводная таблица. Первона­чально, как обычно, требуется выделить ячейку, относящуюся к базе данных.

Содержание сводной таблицы. Но одновременно с этим надо сформировать содер­жание и оформление сводной таблицы. Для выбора содержания надо щелкнуть на кнопке Макет. Сводная таблица состоит из четырех областей: Страница, Строка, Столбец и Данные (рис. 13.14).

Область Данные определяет собственно содержимое таблицы. В отличие от всех остальных областей, к данным, попадающим в ячейку таблицы, применяется функ­ция для итоговых вычислений (по умолчанию — суммирование). Если необходимо изменить эту функцию, надо дважды щелкнуть на соответствующей кнопке и выбрать нужную операцию из раскрывающегося списка.

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

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

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

• Область страницы располагается в верхней части диаграммы.

• Область категорий (включающая строки и столбцы промежуточной сводной таблицы) располагается в нижней части диаграммы или слева от нее.

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

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

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

При наличии ошибок другой категории нарушается логика работы программы. В этом случае сама программа Excel способна помочь в их поиске и исправлении. Эти ошибки также можно разделить на две группы: невычисляемые формулы и цик­лические ссылки.

Неверные формулы. Если получить значение в результате вычисления формулы по каким-то причинам невозможно, программа Excel выдает вместо значения ячейки код ошибки. Возможные коды ошибок и причины их появления приведены в таб­лице 13.2.

Циклические ссылки. При возникновении ошибок другой категории — цикличес­ких ссылок — программа Excel выдает сообщение об ошибке немедленно. При появ­лении новых формул их значения вычисляются сразу же, а циклическая ссылка делает невозможным вычисление данных в одной или нескольких ячейках.

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

Таблица 13.2. Стандартные сообщения программы об ошибках

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

Какая-то из формул в найденных таким образом ячейках должна заведомо содер­жать ошибку, исправление которой разомкнет цикл. Если рабочий лист содержит и другие ячейки с циклическими ссылками, соответствующие ошибки находят и исправляют точно таким же способом.

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

Выделив область рабочего листа, предназначенную для ввода данных определен­ного типа, дайте команду Данные → Проверка. Условия, накладываемые на вводи­мые значения, задаются на вкладке Параметры диалогового окна Проверка вводи­мых значений (рис. 13.18).

Рис. 13.18. Задание условий на вводимые параметры

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

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

Все инструменты контроля правильности электронных таблиц сосредоточены на панели инструментов Зависимости, которую можно открыть командой Сервис → Зависимости → Панель зависимостей. Здесь, в частности, имеются кнопки, анало­гичные кнопкам панели инструментов Циклические ссылки, позволяющие просле­живать влияние и зависимость ячеек.

Кнопка Источник ошибки позволяет найти влияющие ячейки для той ячейки, в кото­рой отображается один из описанных выше кодов ошибки. Здесь же есть кнопка добавления примечаний к ячейкам. Ячейка, содержащая примечания, помечается треугольником в верхнем правом углу, а сами примечания отображаются во всплы­вающем окне при наведении указателя мыши на данную ячейку.

Кроме того, панель инструментов Зависимости позволяет выделить ячейки, содер­жимое которых не отвечает заданным условиям правильности данных. Это осо­бенно удобно, если часть данных была введена до того, как были заданы эти усло­вия. Ячейки с неверным содержанием помечаются кружком.

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX) .

Создание базы данных в Excel

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

Работа с электронными таблицами

Электронные таблицы как программа автоматизации для математической, статистической, графической обработки текстовых и числовых данных. Основное понятие, содержание электронных таблиц, их применение для расчетов. Особенности построение диаграмм и графиков.

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Министерство образования и науки Российской Федерации

федеральное государственное бюджетное образовательное

· провидение однотипных расчетов над большими наборами данных;

· решение задач путем подбора значений параметров, табулирования формул;

· построения диаграмм и графиков по имеющимся данным.

Одним из наиболее распространенных средств работы с документами, имеющими табличную структуру, является программа Microsoft Excel.

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

Рабочая книга и рабочий лист. Строки, столбцы, ячейки

Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейке можно с помощью курсорных клавиш или указателя мыши.

Отдельная ячейка может содержать данные, относящиеся к оному из трех типов: текст, число или формула, — а также оставаться пустой. Программа Excel при сохранении рабочей книги записывает в файл только прямоугольную область рабочих листов, примыкающую к левому верхнему углу (ячейка А1) и содержащую все заполненные ячейки.

Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные так рассматриваются как текст. Ввод формулы всегда начинается с символа > (знака равенства).

Чтобы завершить ввод, сохранив введенные данные, используют кнопку Ввод в строке формул или клавишу ENTER. Чтобы отменить внесенные изменения восстановить прежнее значение ячейки, используют кнопку Отмена в строке формул или клавишу ESC. Для очистки текущей ячейки или выделенного диапазона проще всего использовать клавишу DELETE.

2. Содержание электронной таблицы

Правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко можно выполнить в «уме». Это гарантирует, что последующие редактирование таблицы не нарушит ее целостность и правильность производимых в ней вычислений.

Формула начинается со знака равенства. Могут использоваться знаки операций: “+” — сложение, “-“ — вычитание, “*” — умножение, “/” — деление, “^” — возведение в степень. В математике формулы “двумерные», а в Excel формулы нужно располагать в одной строке. Поэтому приходится вводить дополнительные скобки, которых нет в исходной формуле.

У логических функций аргументы могут принимать только два значения: ИСТИНА и ЛОЖЬ. Поэтому логические функции можно задать таблицей, где перечислены все возможные значения аргументов и соответствующие им значения функций. Такие таблицы называются таблицами истинности.

На практике логические выражения, как правило, не используются. Логическое выражение служит первым аргументом функции ЕСЛИ:

ЕСЛИ (лог_выражение, значение_если_истина, значение_если_ложь)

Во втором аргументе записывается выражение, которое будет вычислено, если лог_выражение возвращает значение ИСТИНА, а в третьем аргументе — выражение, вычисляемое, если лог_выражение возвращает ЛОЖЬ. В языках программирования высокого уровня этой функции соответствует оператор

Если лог_выражение то действие 1; иначе действие 2.

Логическое выражение — любое значение или выражение, которое при вычислении истинно, и другие, если оно ложно. Значение _ если _ истина — значение, которое возвращается, если логическое выражение истинно. Значение _ если _ ложь — значение, которое возвращается, если логическое выражение ложно.

Формула может содержать ссылки, то есть адреса ячеек, содержимое которых используется в вычислениях. Это означает, что результат вычислений формулы зависит от числа, находящегося в другой ячейке. Ячейка, содержащая формулу, таким образом, является зависимой. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значении ячейки, на которую указывает ссылка.

Ссылку на ячейку можно задать разными способами. Во-первых, адрес ячейки можно ввести в ручную. Другой способ состоит в щелчке на нужной ячейке или выборе диапазона, адрес которого требуется ввести. Ячейка или диапазон при этом выделяется пунктирной рамкой.

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

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

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

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

A втоматизации ввода

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

Автозаполнение смежных ячеек

В правом нижнем углу текущей ячейки имеется черный квадратик — маркер заполнения.

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

Автозаполнение смежных ячеек числами.

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

Рис 1

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

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

· Копировать — все ячейки будут содержать одно и то же число;

· Заполнить — ячейки будут содержать последовательные значения (с шагом арифметической прогрессии 1).

3. Применение электронных таблиц для расчетов

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

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

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

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

Пакет анализа. Обеспечивает дополнительные возможности анализа наборов данных. Выбор конкретного метода анализа осуществляется в диалоговом окне Анализ данных, которое открывается командой Сервис > Анализ данных.

Автосохранение — эта надстройка обеспечивает режим автоматического сохранения рабочих книг через заданный интервал времени. Настройка режима автосохранения осуществляется с помощью команды Сервис > Автосохранение.

Мастер суммирования. Позволяет автоматизировать создание формул для суммирования данных в столбце таблицы. При этом ячейки могут включаться в сумму только при выполнении определенных условий. Запуск мастера осуществляется с помощью команды Сервис > Мастер » Частичная сумма.

Мастер подстановок. Автоматизирует создание формулы для поиска данных в таблице по названию столбца и строки. Мастер позволяет произвести однократный поиск или предоставляет возможность ручного задания параметров, используемых для поиска. Вызывается командой Сервис > Мастер > Поиск.

Мастер Web-страниц. Надстройка преобразует набор диапазонов рабочего листа, а также диаграммы в Web-документы, написанные на языке HTML. Мастер запускается с помощью команды Файл > Сохранить в формате HTML и позволяет как создать новую Web-страницу, так и внести данные с рабочего листа в уже существующий документ HTML.

Поиск решения. Эта надстройка используется для решения задач оптимизации. Ячейки, для которых подбираются оптимальные значения и задаются ограничения, выбираются в диалоговом окне Поиск решения, которое открывают при помощи команды Сервис > Поиск решения.

Мастер шаблонов для сбора данных. Данная надстройка предназначена для создания шаблонов, которые служат как формы для ввода записей в базу данных. Когда на основе шаблона создается рабочая книга, данные, введенные в нее, автоматически копируются в связанную с шаблоном базу данных. Запуск мастера производится командой Данные > Мастер шаблонов.

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

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

Далее просто выделяем нужные нам ячейки и выбираем тип графика, который надо построить (Рис 4)

В результате мы получи график, с которым сможем производить дальнейшие действия (Рис. 5)

В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.

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

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

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

Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена (правка » удалить лист), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу DELETE.

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

Настройка авто фильтра для более сложных критериев. Для каждого столбца можно создать критерий, состоящий из одного или двух условий, соединённых логическими операторами И, ИЛИ.

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

· сразу копировать отфильтрованные записи в другое место рабочего листа;

· сохранять критерий отбора для дальнейшего использования;

· показывать в отфильтрованных записях не все столбцы, а только указанные;

· объединять оператором ИЛИ условия для разных столбцов;

· для одного столбца объединять операторами И, ИЛИ более двух условий;

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

1. Додж М., Стинсон К. “Эффективная работа с Microsoft Excel 2000” // Санкт-Петербург: издательство “Питер”. — 2000.

2. Лавренов С.М. “Excel. Сборник примеров и задач» // Москва: “Финансы и статистика». — 1999.

4. Острейковский В.А. “Информатика” // Москва: “Высшая школа». — 1999.

5. Рычков В. “Excel 2000” // Санкт-Петербург: “Питер»; самоучитель. — 2000.

6. Симонович С.В. “Информатика. Базовый курс” // Санкт-Петербург “Питер”; учебник для ВУЗов. — 2001.

7. “MS EXCEL 97. Наглядно и конкретно» // Москва; справочник. — 199

Размещено на Allbest.ru

Подобные документы

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

лабораторная работа [27,8 K], добавлен 17.09.2010

Программа Microsoft Excel для работы с таблицами данных и формулами. Абсолютные и относительные ссылки. Использование мастера функций, ввод ее параметров. Суммирование, построение диаграмм и графиков. Арифметические и логические табличные формулы.

курсовая работа [47,3 K], добавлен 28.11.2009

Процессор электронных таблиц Microsoft Excel — прикладная программа, предназначенная для автоматизации процесса обработки экономической информации, представленной в виде таблиц; применение формул и функций для производства расчетов; построение графиков.

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

презентация [773,1 K], добавлен 23.12.2013

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

Мнение эксперта

Знайка, самый умный эксперт в Цветочном городе

Если у вас есть вопросы, задавайте их мне!

Задать вопрос эксперту

Когда на основе шаблона создается рабочая книга, данные, введенные в нее, автоматически копируются в связанную с шаблоном базу данных. Если же вы хотите что-то уточнить, я с радостью помогу!

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

Работа с электронными таблицами

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов