Базы данных в MS Excel

1.1 Списки MS Excel как база данных

Списком MS Excel называется таблица, оформление которой отвечает следующим требованиям:

1) состоит из строк, называемых записями;

2) столбцы списка, называемые полями, должны содержать однородную (однотипную) информацию;

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

4) внутри списка не должно быть пустых строк и столбцов, которыми список отделяется от остальной части рабочего листа;

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

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

1.2 Создание списков

Первый, наиболее важный шаг при создании базы данных – это разработка хорошо продуманной структуры (организации хранения данных), которая и определяет возможности будущей обработки информации. Структура списка определяется структурой одинаково организованных записей его составляющих. Под структурой записи принято понимать совокупность ее полей (их имена, типы, назначение). Пусть, например, мы хотим создать список, содержащий информацию о студентах, обучающихся в ЛКГТК. Структура такого списка может быть достаточно простой (табл. 1). В ячейках списка чаще всего хранятся константы соответствующих основных типов: числовые, текстовые, даты и времени. Кроме того, допустимо использование так называемых вычисляемых полей, содержащих формулы. В нашем примере (рис. 1) это возраст.

Таблица 1 Структура списка студентов

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

1) ссылки на ячейки внутри списка, а это поля одной и той же записи, должны быть относительными;

2) ссылки на ячейки вне списка должны быть абсолютными.

Заполняется информацией список, как правило, вручную. Кроме того, основные действия со списком позволяет выполнять стандартная экранная форма (рис. 2), которая активизируется с помощью пункта меню Данные/Форма. К основным действиям по обработке списков относятся следующие: добавление, удаление, редактирование, просмотр и поиск записей. При работе со списком перед обращением к команде меню Данные в обязательном порядке необходимо активизировать любую ячейку внутри списка. В этом случае Excel автоматически распознает интервал списка. Excel имеет специальные, достаточно развитые возможности экспорта и импорта файлов баз данных, созданных другими средствами. Доступ к этим возможностям реализуется с помощью команд меню Файл/Сохранить как (Открыть) либо Данные/Внешние данные.

Рисунок 1 Список студентов ЛКГТК

Рисунок 2 Стандартная экранная форма

1.3 Сортировка списков

Под сортировкой списка, как и любого другого набора объектов, принято понимать расположение его записей в определенном порядке. Записи можно располагать в порядке возрастания-убывания числовых полей, в алфавитном (обратном алфавитному) порядке текстовых полей, в хронологическом порядке полей типа " дата и время". Поле, по которому производится сортировка, называется ключевым полем или ключом сортировки. Возможности сортировки реализуются с помощью кнопок Сортировка по возрастанию и Сортировка по убыванию инструментов Стандартная либо через команду меню Данные/Сортировка, которая позволяет отсортировать список за один прием максимум по трем полям (первичный ключ, вторичный и т.д.). В случае необходимости можно произвести сортировку и более чем по трем столбцам. В этой ситуации список сортируется последовательно, начиная с наименее важного поля. Сортировать можно и часть списка, предварительно ее выделив. После сортировки изменяется расположение строк списка, поэтому, если результаты сортировки по каким-либо причинам вас не устраивают, это действие необходимо незамедлительно отменить с помощью кнопки Отменить панели инструментов Стандартная.

1.4 Анализ списков с помощью фильтров

В конечном итоге основное назначение любой базы данных – это оперативный поиск необходимой информации по какому-либо запросу.

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

Для установки автофильтра на все поля списка достаточно обратиться к пункту меню Данные/Фильтр/Автофильтр. Можно установить автофильтр и для одного поля. Для этого достаточно его предварительно выделить: активизировав заголовок соответствующего столбца, нажать комбинацию клавиш Shift, Clrl+ после чего справа от заголовка появится кнопка , щелчок по которой раскрывает список значений данного столбца. Эти значения можно использовать для фильтрации. Кроме того, можно настроить автофильтр, выбрав из этого списка элемент (Условие...), после чего можно создать критерий (настроить пользовательский автофильтр), состоящий не более чем из двух условий, соединенных знаками операций И, ИЛИ. Каждое из этих условий представляет собой выражение логического типа, содержащее любые операции отношения (<, < =, =, о, >, > =). Пусть, например, нам необходим список студентов, родившихся в 1989 году. Пользовательский автофильтр для решения этой задачи приведен на рис. 3, а результаты фильтрации - на рис. 4.

Рисунок 3 Критерий для выборки студентов

Рисунок 4 Выборка студентов, родившихся в 1989 году

При создании текстовых критериев можно использовать символы шаблона: " *" - для обозначения последовательности любых символов произвольной длины, и "? " - для обозначения единичного символа, стоящего на определенном месте. Для включения символов шаблона в критерий в качестве обычных символов перед ними надо ставить тильду ”~”. Пусть, например, нам необходим список студентов, чьи фамилии начинаются с буквы " А" и заканчиваются буквой " а", или фамилия состоит из восьми любых букв. Один из возможных вариантов пользовательского автофильтра для решения этой задачи приведен на рис. 5, а результаты фильтрации - на рис. 6.

Рисунок 5 Критерий для выборки студентов

Рисунок 6 Выборка студентов, по выбранному критерию

Расширенный фильтр по сравнению с автофильтром обладает следующими преимуществами:

1) позволяет создавать критерии с условиями по нескольким полям;

2) позволяет создавать критерии с тремя и более условиями;

3) позволяет создавать вычисляемые критерии;

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

При работе с расширенным фильтром необходимо определить три области (рис. 7):

1) исходный диапазон (интервал списка) - область базы данных ($А$1: $Н$16);

2) диапазон условий (интервал критериев) - область, содержащая критерии фильтрации, которые могут находиться и на отдельном листе (Критерии! $А$2: $D$4);

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

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

При создании интервала критериев (рис. 8) необходимо помнить о следующих соглашениях:

1) диапазон условий должен состоять не менее чем из двух строк (первая строка - заголовки, которые рекомендуется просто копировать из заголовков столбцов списка, последующие – соответствующие критерии);

2) если условия располагаются в одной строке, то это означает одновременность их выполнения, т.е. считается, что между ними поставлена логическая операция И;

3) для истинности критерия, состоящего из условий, располагающихся в разных строках, требуется выполнение хотя бы одного из них, т.е. считается, что они соединены логической операцией ИЛИ;

4) интервал критериев должен располагаться выше или ниже списка, либо на другом рабочем листе;

5) в интервале критериев не должно быть пустых строк.

Рисунок 7 Окно диалога Расширенный фильтр

Рисунок 8 Критерии для выборки по Расширенному фильтру

При формировании текстовых критериев необходимо помнить о том, что:

1) если в ячейке содержится только один символ (рис. 8 - колонка А), то такому условию удовлетворяют любые тексты, начинающиеся с этого символа;

2) если содержимое ячейки представляет собой текстовую константу вида " > БУКВА" или " < БУКВА", то такому условию соответствует любой текст, начинающийся с этой и последующих БУКВ или начинающийся с предшествующих ей БУКВ;

3) для поиска текста на полное совпадение содержимое ячейки с критерием должно иметь вид =" =ТЕКСТ";

4) в текстовых критериях можно использовать символы шаблона.

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

1) заголовок столбца над вычисляемым критерием не должен совпадать ни с каким из имен полей списка, он может быть либо пустым, либо содержать текст, поясняющий назначение условия;

2) в самом условии ссылки на ячейки внутри списка должны быть записаны в относительной форме;

3) ссылки на ячейки вне списка должны быть абсолютными.

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

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

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

1) названные в честь отца;

2) самые младшие по возрасту;

3) самые старшие.

Интервал критериев для реализации этого запроса приведен на рис. 10, а полученная выборка - на рис. 11.

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

Рисунок 9 Выборка студентов по Расширенному фильтру

Рисунок 10 Критерии для реализации сложного запроса

Рисунок 11 Выборка, соответствующая критериям рис. 11

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

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

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

Рисунок 12 Критерии для реализации запроса

Рисунок 13 Выборка, соответствующая критериям рис. 12

Пусть, например, нам необходим список студентов, у которых в фамилии имеется хотя бы одна буква " С" независимо от регистра (маленькая или большая). Критерий для реализации данного запроса представлен на рис. 14. Результаты фильтрации показаны на рис. 15.

Рисунок 14 Критерии для реализации запроса

Рисунок 15 Выборка, соответствующая критериям рис. 14

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

Пусть, например, необходимо найти студентов, родившихся в пятницу или 23-го числа. Критерии для поиска таких студентов представлены на рис. 16. Выборка, полученная в результате фильтрации по выбранным критериям (рис. 16), представлена на рис. 17.

Пусть, например, необходимо выдать список студентов, родившихся под знаком зодиака " Весы" (с 21.09 по 23.10). Интервал критериев для реализации этого запроса приведен на рис. 18, а полученная выборка - на рис. 19.

Рисунок 16 Критерий для поиска студентов, родившихся в пятницу или 23-го числа

Рисунок 17 Выборка студентов соответствующая критериям рис.16

Рисунок 18 Критерий для поиска студентов, родившихся под знаком зодиака " Весы"

Рисунок 19 Выборка студентов соответствующая критериям рис.18

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

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

1. СЧЁТЕСЛИ(интервал; критерий) - возвращает количество ячеек в интервале, которые удовлетворяют критерию.

2. Например, подсчитать число студенток в списке можно по формуле =CЧETEСЛИ(F2: F16; " Ж").

3. СУММЕСЛИ(интервал; критерий; интервал_суммирования) возвращает сумму значений в ячейках из интервала суммирования, отфильтрованных в соответствии с критерием, применяемым к интервалу.

Каждая из оставшихся функций аналогична " обычной" статистической функции. Различие сводится к тому, что функции баз данных обрабатывают только те ячейки интервала, которые удовлетворяют заданным критериям. При этом их синтаксис одинаков: БДФУНКЦИЯ(база данных; поле; критерий). Необходимо обратить внимание на правила обращения к функциям баз данных:

1. первый аргумент задает весь список, а не отдельный столбец;

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

3. вторым аргументом может быть заголовок столбца в виде текстовой константы (имя поля) или порядковый номер поля в списке;

4. третий аргумент задает интервал критериев аналогично интервалу критериев расширенного фильтра.

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

Интервал критериев и формула для решения этой задачи представлены на рис. 20, а результат вычислений - на рис. 21.

Рисунок 20 Пример использования функции баз данных

Рисунок 21 Результат вычислений среднего возраста студентов

Пусть, например, необходимо вычислить максимальный возраст студентов группы 3Б90 и минимальный возраст студентов группы 7180.

Формулы для реализации этой задачи представлены на рисунке 22, а полученный результат - на рисунке 23.

Рисунок 22 Пример использования функций баз данных

Рисунок 23 Результаты вычислений по формулам рис. 22

1.8 Промежуточные итоги

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

Для вставки итогов выбирается команда меню Данные/Итоги. В появившемся диалоговом окне (рис. 24) необходимо выбрать:

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

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

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

- чтобы за каждым итогом следовал разрыв страницы, установить флажок Конец страницы между группами;

- чтобы итоги отображались над строками данных, а не под ними, снять флажок Итоги под данными;

Рисунок 24 Диалоговое ОКНО вставки Промежуточных итогов

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

Для удаления итогов необходимо:

1. установив курсор внутрь списка, содержащего итоги, выбрать команду меню Данные/Итоги;

2. в появившемся диалоговом окне нажать кнопку Убрать все.

При удалении итогов также удаляется структура и все разрывы страниц, которые были вставлены в список при подведении итогов.

Рассмотрим использование Промежуточных итогов на примере подсчета количества студентов, обучающихся в различных группах, и среднего по группам возраста. Исходные данные - список на рабочем листе Excel, представленный на рис. 11. Для решения данной задачи необходимо выполнить следующие действия:

- отсортировать список по полю ГРУППА;

- выбрать команду меню Данные/Итоги. В результате на экране появится окно диалога (рис. 24). В раскрывающихся списках выбрать:

При каждом изменении в - поле ГРУППА,

Операция - Количество,

Добавить итоги по - поле ГРУППА;

3. нажать кнопку ОК.

В результате выполненных действий исходный список студентов на рабочем листе Excel примет вид, представленный на рис. 25.

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

- При каждом изменении в - поле ГРУППА;

- Операция - Среднее;

- Добавить итоги по - поле ВОЗРАСТ;

- снять флажок Заменить текущие итоги;

- нажать кнопку ОК.

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

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

Рисунок 25 Фрагмент рабочего листа с итоговым количеством студентов по группам

Рисунок 26 Рабочий лист с итоговыми данными

1.9 Задание для выполнения контрольной работы «Базы данных в MS Excel»

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

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

1. описание предметной области и структура списка (см. п. 1.2. табл. 1);

2. собственно список Excel (см. п. 1.2. рис. 1);

3. описание реализованного с помощью автофильтра запроса:

а) постановка задачи (запрос) на поиск информации (см. п. 1.4);

б) результаты фильтрации (см. рис. 4, 6);

4. описание реализованного с помощью расширенного фильтра запроса:

а) постановка задачи (запрос) на поиск информации (см. п. 1.4 – 1.6);

б) критерии отбора информации (см. рис. 8, 10, 12, 14, 16, 18);

в) результаты фильтрации (см. рис. 9, 11, 13, 15, 17, 19);

5. описание задачи, решенной с использованием функции баз данных:

а) постановка задачи (см. п. 1.7);

б) интервал критериев и формула для решения задачи (см. рис. 20, 22);

в) результат вычислений (см. рис. 21, 23);

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

а) постановка задачи (см. п. 1.8);

б) таблица с итоговыми данными (см. рис. 25, 26).

Контрольная работа оформляется и представляется на заочное отделение ГОУ СПО ЛКГТК в виде отчета. Отчет должен быть представлен в напечатанной форме на стандартных листах формата А4. Работа должна быть скреплена. Титульный лист оформляется в соответствии со стандартом ЛКГТК. Все страницы должны быть пронумерованы. Работа подписывается автором. Обязательно указание номера Вашего варианта, который определяется порядковым номером в журнале учебных занятий. К отчету в обязательном порядке прилагается дискета с файлами базы данных в MS Excel. Файлы должны называться так: Фамилия_Вариант.xls (например, Ivanov_03. xls).

5 Перечень вопросов для проведения промежуточной аттестации

1. Документ имеет верхний колонтитул. Что произойдет, если в данной ситуации выбрать верхний стандартный колонтитул?

2. Можно ли напечатать одно слово в абзаце с увеличенным расстоянием между буквами?

3. В какой вкладке находятся инструменты для вставки в документ автоматически обновляемой даты?

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

5. Для каких целей можно использовать указанные кнопки?

6. Что устанавливается в указанном раскрывающемся списке?

7. Как влияет изменение масштаба отображения документа на печать документа?

8. Почему в заголовке открытого документа отображается указанное сообщение («режим ограниченной функциональности»)?

9. Что можно маркировать с использованием маркированного списка?

10. Каким элементом горизонтальной линейки следует воспользоваться для установки отступа первой строки (красной строки)?

11. Какой командой следует воспользоваться для изменения начального номера списка?

12. Необходимо с помощью мыши увеличить ширину всех столбцов и высоту всех строк таблицы. Каким маркером следует для этого воспользоваться?

13. Что нужно сделать для перехода к редактированию и оформлению ранее созданного колонтитула?

14. В документе имеется нижний колонтитул. Что произойдет, если выбрать вид нумерации внизу страницы?

15. Как с помощью мыши изменить размер рисунка, соблюдая его пропорции?

16. Что произойдет после перетаскивания левой кнопкой мыши маркера в правом нижнем углу ячейки А3 до ячейки А7?

17. Как сортировать таблицу по данным столбца «Объем партии»?

18. Какие ячейки следует выделить на листе, чтобы сортировать таблицу по данным столбца С?

19. Какой диапазон следует выделить с целью установки фильтров для всех столбцов таблицы?

20. Как сортировать таблицу по данным столбца «Объем партии»?

21. При вводе числа после подтверждения ввода в ней оказались символы «решётка». Что это означает?

22. Каким символом при вводе времени в ячейку следует разделять часы, минуты и секунды?

23. Как подобрать высоту строки 2 по содержимому наибольшей ячейки в строке?

24. Как подобрать ширину столбца «В» по содержимому наибольшей ячейки в столбце?

25. Могут ли совпадать пароли, используемые для защиты листов и структуры книги?

26. Можно ли переместить лист, защищенный от изменений?

27. Почему в указанных ячейках границы расположены под углом?

28. Что настраивается в указанном счетчике?

29. Какой символ следует использовать в формулах в качестве знака деления?

30. Какая формула будет указана в ячейке D5 при копировании в нее формулы из ячейки D2?

31. Что означает указанная запись в строке формул?

32. Какой символ следует использовать в формулах в качестве знака умножения?

33. Как часто обновляется значение в ячейке В1 при использовании функции ТДАТА()?

34. Каким документом формируются проводки по начислению налогов с ФОТ?

35. Что означает красная «галочка», которой отмечена пиктограмма в начале строки счета (субсчета)?

36. Что можно сказать о счете 00?

37. Можно ли в многоуровневом справочнике осуществлять быстрый поиск по всем элементам справочника независимо от их расположения в группах?

38. Какая информация отображается в отчете «Анализ счета по датам»?

39. В каком случае при вводе проводки операции требуется заполнить поле «Количество»?

40. Может ли быть операция сформирована непроведенным документом?

41. Каким документом оформляется в программе выдача денег под авансовый отчет?

42. Можно ли одновременно просматривать операции и принадлежащие им проводки?

43. В проведенном документе «Авансовый отчет №12» выбран не тот сотрудник. Как исправить ошибку?

44. За какой период будет выводиться информация в открываемых журналах для ситуации, представленной на рисунке, если рабочая дата установлена 05.05.2005?

45. В каком журнале сохраняются документы, фиксирующие различные выплаты сотруднику?

46. Каким образом отражается в программе изменение оклада сотрудника?

47. Каким образом можно очистить поле ввода, заполняемое выбором из списка («серого цвета»)?

48. В соответствии со ст. 218 НК РФ для граждан, не относящихся к льготной категории, право на ежемесячный вычет составляет

49. Что такое Power Point?

50. Что такое презентация PowerPoint?

51. Power Point нужен для создания ….

52. Составная часть презентации, содержащая различные объекты, называется…

53. Совокупность слайдов, собранных в одном файле, образуют…

54. Запуск программы Power Point осуществляется с помощью команд …

55. В каком разделе меню окна программы Power Point находится команда Создать (Новый) слайд?

56. Выбор макета слайда в программе Power Point осуществляется с помощью команд

57. Конструктор и шаблоны в программе Power Point предназначены для…

58. Какая кнопка панели Рисование в программе Power Point меняет цвет контура фигуры?

59. Какая кнопка панели Рисование в программе Power Point меняет цвет внутренней области фигуры?

60. Команды вставки картинки в презентацию программы Power Point…

61. Команды добавления диаграммы в презентацию программы Power Point

62. Какая кнопка окна программы Power Point предназначена непосредственно для вставки текстового блока на слайд?

63. В каком разделе меню окна программы Power Point находится команда Настройка анимации?

64. Эффекты анимации отдельных объектов слайда презентации программы Power Point задаются командой

65. Команды настройки смены слайдов презентации программы Power Point по щелчку

66. Какая команда контекстного меню программы Power Point превращает любой объект в управляющую кнопку?

67. Какая клавиша прерывает показ слайдов презентации программы Power Point?

68. Что такое электронная почта?

69. Из чего состоит электронное письмо?

70. Из каких частей состоит электронный адрес?

71. Что такое телеконференция?

72. Электронная почта (e-mail) позволяет передавать:

73. Какие вирусы заражают загрузочный сектор гибкого диска или винчестера?

74. Какие вирусы могут поражать исполняемые файлы различных типов?

75. Какие вирусы получили наибольшее распространение для Microsoft office?

76. Что используется для защиты вирусов и лечения зараженного компьютера?

77. Информационная культура общества предполагает

78. Областями применения информационных технологий являются

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

80. Информационно – справочными системами являются

81. Заражение компьютерными вирусами может произойти в процессе...

82. Какая программа не является антивирусной?

83. Как обнаруживает вирус программа-ревизор?

84. Компьютерным вирусом является...

85. Найдите правильные слова: компьютерные вирусы...

86. Модем – это...

87. Какой протокол является базовым в Интернет?

88. Компьютер, подключенный к Интернет, обязательно имеет...

89. Гиперссылки на web-странице могут обеспечить переход...

90. Задан адрес электронной почты в сети Internet: user-name@int.glasnet.ru. Каково имя владельца электронного адреса?

91. Браузеры (например, Microsoft Internet Explorer) являются...

92. Web-страницы имеют формат (расширение)...

93. Mодем – это устройство, предназначенное для...

94. В качестве гипертекстовых ссылок можно использовать ...

95. Компьютерные телекоммуникации – это...

96. Домен – это...

97. Можно ли на странице расположить часть текста в одну колонку, а часть - в три колонки?

Приложение 1

Варианты контрольных заданий

Вариант 1

Создать базу данных о работниках предприятия, содержащую следующие поля:

- отдел;

- ФИО

- должность;

- оклад;

- категория;

- стаж работы;

- заработная плата.

Поле «заработная плата» рассчитывается по формуле:

Оклад + Надбавка

Надбавка зависит от категории.

1-я категория -15% от оклада;

2-я категория–10% от оклада;

3-я категория–5% от оклада.

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

Вариант 2

Создать базу данных для торгового предприятия, содержащую следующие поля:

- организация;

- код товара;

- № прейскуранта;

- количество;

- розничная цена;

- сумма по розничной цене;

- % торговой скидки;

- сумма торговой скидки;

- сумма за вычетом скидки.

Поле «сумма по розничной цене» рассчитывается по формуле: Розничная цена*Количество

Поле «сумма торговой скидки» рассчитывается по формуле:

Сумма по розничной цене * % торговой скидки

Поле «сумма за вычетом скидки» рассчитывается по формуле:

Сумма по розничной цене–Сумма торговой скидки

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

Вариант 3

Создать базу данных для торговых организаций, содержащую следующие поля:

- организация;

- товарные запасы на начало года;

- продано товаров:

- план,

- факт;

- поступление товаров:

- план,

- факт;

- товарные запасы на конец года:

- план,

- факт;

Поле «товарные запасы на конец года» (по факту и по плану) определяется по формуле:

Товарные запасы на начало года + Поступление товаров–Продано товаров

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

Вариант 4

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

- номер автомашины;

- табельный номер водителя;

- пройдено, км;

- остаток горючего на начало месяца, кг;

- получено, кг;

- израсходовано, кг:

- по норме,

- фактически;

- остаток на конец месяца;

- результат:

- экономия;

- перерасход.

Поле «остаток на конец месяца» рассчитывается по формуле:

Остаток горючего на начало месяца + Получено–Израсходовано фактически

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

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

Определить средний расход горючего по автохозяйству.

Вариант 5

Создать базу данных для торгового предприятия по анализу розничного товарооборота, содержащую следующие поля:

- код торгового предприятия;

- наименование торгового предприятия;

- оборот предшествующего года;

- товарооборот отчётного года:

- план,

- факт,

- отклонение:

- больше,

- меньше;

- отчетный год в % к плану;

- отчетный год в % к предыдущему году.

Поле «отклонение» рассчитывается по формуле: Факт – План

Если полученное отклонение положительное, то результат заносится в поле «больше», в противном случае – в поле «меньше».

Поле «отчетный год в % к плану» рассчитывается по формуле: Факт / План*100

Поле «отчетный год в % к предыдущему году» рассчитывается по формуле:

Факт / Оборот предшествующего года*100

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

Вариант 6

Создать базу данных по складу продовольственных товаров, содержащую следующие поля:

- код товара;

- наименование товара;

- ед. изм.;

- цена;

- количество по инвентаризационным данным;

- сумма по инвентаризационным данным;

- количество по учетным данным;

- сумма по учетным данным;

- результат инвентаризации:

- количество излишек,

- количество недостача,

- сумма излишек,

- сумма недостача.

Поле «сумма по инвентаризационным данным» рассчитывается по формуле:

Цена* Количество по инвентаризационным данным

Поле «сумма по учетным данным» рассчитывается по формуле:

Цена* Количество по учетным данным

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

Определить суммарную величину излишков и недостачи по складу продовольственных товаров.

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

Вариант 7

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

- код видов основных средств;

- балансовая стоимость основных средств:

- на начало месяца,

- поступило,

- выбыло,

- на конец месяца;

- амортизационные отчисления, %

- на полное восстановление:

- %,

- сумма,

- на капитальный ремонт:

- %,

- сумма;

- общая сумма амортизационных отчислений.

Поле «на конец месяца» рассчитывается по формуле: На начало месяца + Поступило – Выбыло

Поле «сумма на полное восстановление» рассчитывается по формуле:

Балансовая стоимость на конец месяца * % на полное восстановление.

Поле «сумма на капитальный ремонт» рассчитывается по формуле:

Балансовая стоимость на конец месяца* % на капитальный ремонт

Поле «общая сумма амортизационных отчислений» рассчитывается

по формуле:

Сумма на капитальный ремонт + Сумма на полное восстановление

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

Вариант 8

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

- код продукции;

- наименование продукции;

- ед. изм.;

- план закупок:

- количество,

- цена,

- сумма;

- фактически закуплено:

- количество,

- цена,

- сумма;

- отклонения:

- количество,

- больше,

- меньше;

- сумма:

- больше,

- меньше.

Поля «сумма» (по плану и по факту) рассчитываются по формуле:

Количество * Цена

Поле «отклонение» (количество и сумма) вычисляется по формуле:

Фактически закуплено – План закупок

Если полученное отклонение положительное, то результат заносится в поле «больше», в противном случае – в поле «меньше».

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

Вариант 9

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

- код продовольственного магазина;

- наименование продовольственного магазина;

- оборот предшествующего года;

- товарооборот отчётного года:

- план,

- факт,

- отклонение:

- больше,

- меньше;

- отчетный год в % к плану;

- отчетный год в % к предыдущему году.

Поле «отклонение» рассчитывается по формуле: Факт – План

Если полученное отклонение положительное, то результат заносится в поле «больше», в противном случае – в поле «меньше».

Поле «отчетный год в % к плану» рассчитывается по формуле: Факт / План*100

Поле «отчетный год в % к предыдущему году» рассчитывается по формуле:

Факт / Оборот предшествующего года*100

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

Вариант 10

Создать базу данных для торгового объединения по анализу издержек обращения, содержащую следующие поля:

- код потребительского общества;

- наименование потребительского общества;

- уровень издержек предшествующего года;

- сумма издержек:

- план,

- факт;

- товарооборот:

- план,

- факт;

- уровень издержек:

- план,

- факт;

- отклонение уровня издержек:

- больше,

- меньше.

Поле «уровень издержек» (по плану и факту) рассчитывается по формуле:

Сумма издержек / Товарооборот*100

Поле «отклонение уровня издержек» рассчитывается по формуле:

Уровень издержек (факт) – Уровень издержек (план)

Если полученное отклонение положительное, то результат заносится в поле «больше», в противном случае – в поле «меньше».

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

Вариант 11

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

- табельный номер;

- ФИО;

- дни болезни;

- ноябрь:

- дни,

- сумма;

- декабрь:

- дни,

- сумма;

- всего:

- дни,

- сумма;

- средний дневной заработок;

- размер пособия, %;

- сумма пособия.

Поле «дни (всего)» рассчитывается по формуле: Дни (Ноябрь) + Дни (Декабрь)

Поле «сумма (всего)» рассчитывается по формуле:

Сумма (Ноябрь) + Сумма (Декабрь)

Поле «средний дневной заработок» рассчитывается по формуле:

Сумма (Всего) / Дни (Всего)

Поле «сумма пособия» рассчитывается по формуле:

Средний дневной заработок * Дни болезни * Размер пособия в % / 100

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

Задача 12

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

- наименование товара;

- единицы измерения;

- цена;

- остаток на начало месяца:

- количество,

- сумма;

- оборот за месяц:

- количество,

- сумма;

- остаток на конец месяца:

- количество,

- сумма.

Поле «остаток на конец месяца» рассчитывается по формуле:

Остаток на начало месяца + Оборот за месяц

Определить общую сумму по следующим полям базы данных: «остаток на начало месяца», «оборот за месяц», «остаток на конец месяца».

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

Вариант 13

Создать базу данных для печати расчетно-платежной ведомости по оплате труда, содержащую следующие поля:

- табельный номер;

- начислено:

- сдельно,

- повременно,

- премия,

- прочее;

- итого начислено;

- удержано:

- аванс,

- подоходный налог:

- процент,

- сумма;

- ФИО;

- сумма к выдаче.

Поле «итого начислено» рассчитывается по формуле:

Начислено сдельно + Начислено повременно + Премия + Прочее

Поле «удержано» рассчитывается по формуле:

Аванс + Подоходный налог (сумма)

Поле «сумма к выдаче» рассчитывается по формуле: Итого начислено – Удержано

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

Определить общую сумму начислений по предприятию.

Вариант 14

Создать базу данных для учета поступления сельскохозяйственной продукции, содержащую следующие поля:

- номер товарной накладной;

- общий вес;

- стандартная:

- цена,

- вес,

- % от общего веса;

- нестандартная:

- цена,

- вес,

- % от общего веса;

- отходы:

- цена,

- вес,

- % от общего веса,

- сумма.

Поле «% от общего веса» рассчитывается по формуле:

Вес стандартной продукции * 100 / Общий вес

Поле «сумма отходов» рассчитывается по формуле: Вес отходов * Цена отходов

Определить общую сумму по полю «сумма отходов».

Вариант 15

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

- номер по порядку;

- наименование товара;

- единицы измерения;

- цена;

- фактическое наличие:

- количество,

- сумма;

- учетные данные:

- количество,

- сумма;

- недостача:

- количество,

- сумма;

- излишки:

- количество,

- сумма.

Для заполнения полей «недостача» и «излишки» следует сравнить поля «фактическое наличие» и «учетные данные». Если «фактическое наличие» больше, чем «учетные данные», заполняются поля «излишки (количество)» и «излишки (сумма)», в противном случае – поля «недостача (количество)» и «недостача (сумма)».

Вариант 16

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

- показатели;

- единицы измерения;

- базисный период;

- отчетный период:

- план,

- факт,

- отклонение,

- % выполнения плана;

- % выполнения к базисному периоду.

Поле «отчетный период (отклонение)» рассчитывается по формуле:

Отчетный период (факт)–Отчетный период (план)

Поле «% выполнения плана» рассчитывается по формуле:

Отчетный период (факт) * 100 / Отчетный период (план)

Поле «% выполнения к базисному периоду» рассчитывается по

формуле:

Отчетный период (факт) * 100 / Базисный период

Вариант 17

Создать базу данных для строительного объединения по анализу издержек обращения, содержащую следующие поля:

- код строительного объединения;

- наименование строительного объединения;

- уровень издержек предшествующего года;

- сумма издержек:

- план,

- факт;

- капитальные вложения:

- план,

- факт;

- уровень издержек:

- план,

- факт;

- отклонение уровня издержек:

- больше,

- меньше.

Поле «уровень издержек» (по плану и факту) рассчитывается по формуле:

Сумма издержек / капитальные вложения*100

Поле «отклонение уровня издержек» рассчитывается по формуле:

Уровень издержек (факт) – Уровень издержек (план)

Вариант 18

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

- код продукции;

- наименование продукции;

- ед. изм.;

- план закупок:

- количество,

- цена,

- сумма;

- фактически закуплено:

- количество,

- цена,

- сумма;

- отклонения:

- количество,

- больше,

- меньше;

- сумма:

- больше,

- меньше.

Поля «сумма» (по плану и по факту) рассчитываются по формуле:

Количество * Цена

Поле «отклонение» (количество и сумма) вычисляется по формуле:

Фактически закуплено – План закупок

Вариант 19

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

- табельный номер;

- начислено:

- сдельно,

- повременно,

- премия,

- прочее;

- итого начислено;

- удержано:

- аванс,

- подоходный налог:

- процент,

- сумма;

- ФИО;

- сумма к выдаче.

Поле «итого начислено» рассчитывается по формуле:

Начислено сдельно + Начислено повременно + Премия + Прочее

Поле «удержано» рассчитывается по формуле:

Аванс + Подоходный налог (сумма)

Поле «сумма к выдаче» рассчитывается по формуле:

Итого начислено – Удержано

Вариант 20

Создать базу данных оптово-торгового предприятия, содержащую следующие поля:

- код товара;

- наименование товара;

- ед. изм.;

- цена;

- количество по инвентаризационным данным;

- сумма по инвентаризационным данным;

- количество по учетным данным;

- сумма по учетным данным;

- результат инвентаризации:

- количество излишек,

- количество недостача,

- сумма излишек,

- сумма недостача.

Поле «сумма по инвентаризационным данным» рассчитывается по формуле:

Цена* Количество по инвентаризационным данным

Поле «сумма по учетным данным» рассчитывается по формуле:

Цена* Количество по учетным данным

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

Рекомендуемая литература

Литература основная

1. Михеева, Е. В. Информационные технологии в профессиональной деятельности [Текст]: Учеб. пособие для сред. проф. образования/Е. В. Михеева. – 3-е изд., стер. –М.: Издательский центр «Академия», 2007.-384с.

2. Михеева, Е. В. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера [Текст]: Учеб. пособие для сред. проф. образования/Е. В. Михеева, О. И. Титова. –М.: Издательский центр «Академия», 2005.-224с.

3. Скобара, В. В., Скобара, А. В. Возможности Excel 7.0 для аудитора и бухгалтера [Текст]: монография/В. В. Скобара, А. В. Скобара. – СПб.: Петро-Балт-Аудит, 1998.

Литература дополнительная

1. Документация к пакету бухгалтерской программы " 1С: Бухгалтерия."

2. План счетов бухгалтерского учета с изменениями и дополнениями. – СПб.: ООО " Издательство ДНК", 2001 г.

3. Годин В.В., Корнеев И.К. Информационное обеспечение управленческой деятельности: Учебник. – М.: Мастерство; Высшая школа, 2001. – 240с.

Интернет – ресурсы