Разработка и типы запросов к базе данных в СУБД MS Access. 2

Оглавление

Введение 3

Часть 1. Теоретическая  часть 4

1.1 Понятие и возможности MS Excel 4

1.2 Организация вычислений в  среде MS Excel 4

1.3 Формулы MS Excel 6

1.4 Функции MS Excel 14

Часть 2. Практикум. Вариант 13 17

Решение практического  задания 18

Заключение 24

Список литературы 25

Введение 

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

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

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

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

Часть 1. Теоретическая  часть

1.1 Понятие и возможности MS Excel 

Microsoft Office — офисный пакет  приложений, созданных корпорацией  Microsoft для операционных систем Microsoft Windows и Apple Mac OS X. В состав этого  пакета входит программное обеспечение  для работы с различными типами  документов: текстами, электронными  таблицами, базами данных и  др. 

В состав Microsoft Office входит Microsoft Office Excel, который на сегодняшний день является одним из наиболее популярных приложений в мире. 

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

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

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

2. автоматизация итоговых  вычислений; 

3. решение задач путём  подбора значений параметров; 

4. обработка (статистический  анализ) результатов экспериментов; 

5. проведение поиска оптимальных  значений параметров (решение оптимизационных  задач); 

6. подготовка табличных  документов; 

7. построение диаграмм (в  том числе и сводных) по имеющимся  данным; 

8. создание и анализ  баз данных (списков). 

1.2 Организация вычислений в  среде MS Excel 

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

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

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

Текст - это информация, не являющаяся ни числом, ни формулой. Текст  обрабатывается как последовательность символов, даже если он представляет собой  набор цифр. 

Excel поддерживает следующие  форматы данных: 

• Общий (General) — текст  и числовые значения произвольного  типа; 

• Числовой (Number) — наиболее общий способ представления чисел; 

• Денежный (Currency) — денежные величины; 

• Финансовый (Accounting) — денежные величины с выравниванием по разделителю  целой и дробной частей; 

• Дата (Date) — дата или  дата и время; 

• Время (Time) — время или  дата и время; 

• Процентный (Percentage) — значение ячейки, умноженное на 100 с символом «%» в конце; 

• Дробный (Fraction) — рациональные дроби с числителем и знаменателем; 

• Экспоненциальный (Scientific) —десятичные дробные числа; 

• Текстовый (Text) — текстовые  данные отображаются точно так же, как вводятся и обрабатываются строки, вне зависимости от их содержимого; 

• Дополнительный (Special) —форматы для работы с базами данных и списками адресов; 

• Заказной (Custom) — формат, настраиваемый пользователем. 

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

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

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

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

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

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

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

1.3 Формулы MS Excel

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

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

В качества аргументов в формуле  обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие  символы: + (сложение) ; (вычитание) ; * (умножение) ; / (деление) .

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

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

Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.

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

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

В некоторых случаях может потребоваться  использование функции как одного из аргументов другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.

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

По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы  буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву  столбца, а следом номер строки.

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

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

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный  столбец. Абсолютная ссылка столбцов приобретает  вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При  изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль  строк и вдоль столбцов относительная  ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

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

Типы операторов:

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

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

Оператор ссылки.   Для описания ссылок на диапазоны ячеек используются следующие операторы.

Порядок выполнения действий в формулах

Формулы вычисляют значения в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о  том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула  вычисляется слева направо, в  соответствии с определенным порядком для каждого оператора в формуле.

Приоритет оператора

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

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

Создание формулы

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

Создание простой формулы: =128+345

1. Щелкните ячейку, в которую требуется ввести формулу.
2. Введите = (знак равенства).
3. Введите формулу.
4. Нажмите клавишу ENTER.

Создание формулы, содержащей ссылки или имена: =A1+23

1. Щелкните ячейку, в которую требуется ввести формулу.
2. В строку формул введите = (знак равенства).
3. Выполните одно из следующих действий.
• Чтобы создать ссылку, выделите ячейку, диапазон ячеек, место в другом листе или место в другой книге. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
• Чтобы создать ссылку на именованный диапазон, нажмите клавишу F3, выберите имя в поле Вставка имени и нажмите кнопку ОК.
4. Нажмите клавишу ENTER.

Создание формулы, содержащей функцию: =СРЕДНЕЕ(A1:B4)

1. Щелкните ячейку, в которую требуется ввести формулу.
2. Для того чтобы начать формулу с функции, нажмите кнопку Вставка функции на панели формул
3. Выберите функцию, которую нужно использовать. Можно ввести запрос с описанием операции, которую требуется выполнить, в поле Поиск функции (например, по словам «сложение чисел» будет найдена функция СУММ), или выбрать категорию в поле или категорию.
4. Введите аргументы. Чтобы ввести ссылки на ячейки в качестве аргументов, нажмите кнопку свертывания диалогового окна, чтобы временно скрыть его. Выделите ячейки на листе и нажмите кнопку развертывания диалогового окна.
5. По завершении ввода формулы нажмите клавишу ENTER.

Создание формулы с вложением функций: =ЕСЛИ(СРЕДНЕЕ(F2:F5)>50; СУММ(G2:G5);0)

1. Щелкните ячейку, в которую требуется ввести формулу.
2. Для того чтобы начать формулу с функции, нажмите кнопку Поместить функцию на панели формул.
3. Выберите функцию, которую нужно использовать. Можно ввести запрос с описанием операции, которую требуется выполнить (например, «сложение чисел» возвращение функции СУММ) в поле Поиск функции, или выбрать категорию в поле или категорию.
4. Введите аргументы.
• Чтобы ввести ссылки на ячейки в качестве аргументов, нажмите расположенную за требуемым аргументом кнопку свертывания диалогового окна , чтобы временно скрыть его. Выделите ячейки на листе и нажмите кнопку развертывания диалогового окна .
• Чтобы ввести другую функцию в качестве аргумента, введите функцию в соответствующее окно аргумента. Например, можно добавить СУММ(G2:G5) в текстовое поле значение_если_истина.
• Чтобы переключиться между частями формулы, отображаемыми в диалоговом окне Аргументы функции, нажмите кнопку мыши на имени формулы в списке формул. Например, при нажатии на функции ЕСЛИ появится аргумент для данной функции.

1.4 Функции MS Excel

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

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

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

Логические функции:

И - Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

ЛОЖЬ  - Возвращает логическое значение ЛОЖЬ.

ЕСЛИ  - Выполняет проверку условия.

НЕ  - Меняет на противоположное логическое значение своего аргумента.

ИЛИ  - Возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

ИСТИНА -  Возвращает логическое значение ИСТИНА.

«ЛОЖЬ»

Возвращает логическое значение ЛОЖЬ.

Синтаксис ЛОЖЬ( )

«НЕ»

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

Синтаксис НЕ(логическое_значение)

«ИСТИНА»

Возвращает логическое значение ИСТИНА.

Синтаксис ИСТИНА( )

«ЕСЛИ»

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис

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

«И»

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис И(логическое_значение1; логическое_значение2; ...)

«ИЛИ»

Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы  имеют значение ЛОЖЬ.

Синтаксис ИЛИ(логическое_значение1;логическое_значение2; ...)

Часть 2. Практикум. Вариант 13

Негосударственное образовательное  учреждение НОУ «Креатив» организует творческие курсы для населения  города. Проведение теоретических и практических занятий включает почасовую оплату преподавателя и накладные расходы. Дополнительно слушатели также оплачивают стоимость раздаточного материала для проведения практических занятий. Данные для выполнения расчетов представлены на рис. 13.1 и 13.2.

Для решения задачи необходимо следующее.

1. Построить таблицы по  данным, приведенным на рис. 13.1 и  13.2.

2. Рассчитать общую стоимость  каждого вида творческих курсов  без учета раздаточного материала  (рис. 13.1).

3. Рассчитать стоимость  раздаточного материала для проведения  практических занятий по каждому  курсу на одного человека (рис. 13.2).

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

5. Сформировать и заполнить  таблицу с данными по расчету  дохода от проведенных курсов  с учетом количества слушателей, стоимости курсов и стоимости  раздаточного материала по каждому  виду курсов и по всем курсам в целом (рис. 13.3).

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

Рис. 13.1. Данные о стоимости  курсов

Рис.13.2. Данные о стоимости  раздаточного материала для проведения практических занятий