Применение мастера функций в MS Excel

Содержание

 

1. Виды запросов и способы их создания в MS ACCESS……………………. 3

2. Применение мастера функций в  MS Excel…………………………………. 8

3. Практическое задание……………………………………………………..... 11

4. Список используемой  литературы………………………………………….14

 

 

1. Виды запросов и способы их создания в MS ACCESS.

 

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

Запрос на выборку является наиболее часто используемым типом  запроса. Запросы этого типа возвращают данные из одной или нескольких таблиц и отображают их в виде таблицы, записи в которой можно обновлять (с  некоторыми ограничениями). Запросы  на выборку можно также использовать для группировки записей и  вычисления сумм, средних значений, подсчета записей и нахождения других типов итоговых значений.  
Запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами. 

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

Перекрестные  запросы 

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

 

Запросы на изменение 

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

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

• На обновление записи. Запрос на обновление вносит общие изменения  в группу записей одной или  нескольких таблиц. Например, на 10 процентов  поднимаются цены на все молочные продукты или на 5 процентов увеличивается  зарплата сотрудников определенной категории. Запрос на обновление записей  позволяет изменять данные в существующих таблицах.  
• На добавление записей. Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты». 

• На создание таблицы. Запрос на создание таблицы создает новую  таблицу на основе всех или части  данных из одной или нескольких таблиц. Запрос на создание таблицы полезен  при создании таблицы для экспорта в другие базы данных Microsoft Access или  при создания архивной таблицы, содержащей старые записи. 

 

Запросы SQL 

Запрос SQL — это запрос, создаваемый при помощи инструкций SQL. Язык SQL используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access. 

Когда пользователь создает  запрос в режиме конструктора запроса, Microsoft Access автоматически создает  эквивалентную инструкцию SQL. Фактически, для большинства свойств запроса, доступных в окне свойств в  режиме конструктора, имеются эквивалентные  предложения или параметры языка SQL, доступные в режиме SQL.

MS ACCESS. Способы создания запросов (мастер, конструктор). 

Создание запроса в  режиме Конструктора: 

1. В окне базы данных выберите Запросы и нажмите кнопку Создать. 
2. В диалоговом окне Новый запрос щелкните строку Конструктор, а затем нажмите кнопку OK. 
3. В диалоговом окне Добавление таблицы выберите вкладку, содержащую объекты, данные из которых будут использованы в запросе. 
4. Дважды щелкните объекты, которые нужно добавить в запрос, а затем нажмите кнопку Закрыть. 

Для создания запроса на основе открытой таблицы: 

1. Вставка => Запрос 

2. В окне Новый запрос  выберите режим Конструктора. 

Создание  простого запроса с помощью мастера 

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

1. В окне базы данных выберите Запросы и нажмите кнопку Создать. 
2. В диалоговом окне Новый запрос выберите в списке строку Простой запрос и нажмите OK..
3. Следуйте инструкциям в диалоговых окнах мастера. Последнее диалоговое окно позволяет либо запустить запрос, либо открыть его в режиме конструктора. 

Итак, чтобы создать простой запрос с помощью Мастера запросов, необходимо:

1. В окне базы данных на панели объектов выбрать ярлык Запросы (Queries).
2. В списке запросов дважды щелкнуть левой кнопкой мыши на ярлыке Создание запроса с помощью мастера (Create query by using wizard) или нажать на кнопку Создать (New) в окне базы данных и в появившемся диалоговом окне Новый запрос (New Query) выбрать Простой запрос (Simple Query Wizard) и нажать на кнопку ОК.
3. В появившемся окне Создание простых запросов (Simple Query Wizard) в поле со списком Таблицы и запросы (Tables/Queries) выбрать таблицу или запрос, которые будут служить источником данных для создаваемого запроса.
4. С помощью стрелок вправо и влево переместить из списка Доступные поля (Available Fields) в список Выбранные поля (Selected Fields) те поля, которые необходимы в конструируемом запросе. При этом порядок полей в запросе будет соответствовать порядку полей в списке Выбранные поля (Selected Fields). Если нужно включить в запрос все поля, можно воспользоваться кнопкой с двумя стрелками вправо.
5. Нажать кнопку Далее (Next).
6. Следующее диалоговое окно будет последним. В нем нужно ввести имя создаваемого запроса в поле Задайте имя запроса (What title do you want to your query?) и выбрать дальнейшие действия: Открыть запрос для просмотра данных (Open the query to view information) или Изменить макет запроса (Modify the query design).
7. При необходимости можно установить флажок Вывести справку по работе с запросом? (Display Help on working with the query) для вывода справочной информации по работе с запросами.
8. Нажать на кнопку Готово (Finish).

По окончании работы Мастера  простых запросов в зависимости  от выбора способа дальнейшей работы с запросом откроется или окно запроса в режиме просмотра, или  окно Конструктора запросов, в котором  можно модифицировать запрос. В качестве примера построения простого запроса  с помощью Мастера простых  запросов рассматривается создание запроса, содержащего имена, фамилии, адреса и телефоны сотрудников фирмы  «Борей» (Northwind) (в качестве исходных данных взяты таблицы демонстрационной базы данных «Борей»). Этот запрос строится на основе таблицы «Сотрудники» (Employees). На первом шаге Мастера простых запросов была выбрана исходная таблица «Сотрудники» (Employees) в поле со списком Таблицы и запросы (Tables/Queries) и в список Выбранные поля (Selected Fields) были перенесены следующие поля: «Имя» (First Name), «Фамилия» (Last Name), «Адрес» (Address), «Домашний телефон» (Home Phone). На втором шаге Мастера простых запросов в поле названия запроса было введено имя «Сотрудники Запрос» и выбран способ дальнейшего отображения запроса: просмотр информации.

  

2. Применение мастера функций в MS Excel

 

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

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

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

-   10 недавно использовавшихся,

-   полный алфавитный перечень,

-   финансовые;

-   дата и время;

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

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

-   ссылки и массивы;

-   работа с базой данных;

-   текстовые;

-   логические;

-   проверка свойств и значений.

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

Происходящее далее рассмотрим на конкретном примере. Из списка функций  мы выберем СУММ и как только мы это сделаем, программа внесет в  ячейку =СУММ(), а в диалоговом окне Аргументы функции появятся поля, куда необходимо вести ее аргументы.

Чтобы выбрать аргументы, поместим точку вставки в поле Число1, и щелкнуть на ячейке электронной  таблицы (или перетащить мышь, выделив  нужный диапазон).  После этого в текстовом поле Число1 появится адрес ячейки (либо адрес диапазона) и одновременно в окне справа – числовое значение, введенное в эту ячейку, а также внизу диалогового окна после слова Значение отразится итоговое значение функции.

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

Если необходимо просуммировать содержимое нескольких ячеек, либо диапазонов, то нужно нажать клавишу <Tab> или  щелкнуть в поле Число2 чтобы переместить  в него курсор. В поле Число2 указываем  вторую ячейку (или диапазон).

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

Редактирование формул:

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

После щелчка на кнопке Изменить формулу Excel открывает диалоговое окно Аргументы функции, в котором  можно отредактировать аргументы, указав новые ссылки на ячейки или  диапазоны ячеек в текстовых  полях Число1, Число2 и т.д. если необходимо заменить текущий аргумент, то его  необходимо выделить, а затем удалить, нажав клавишу <Del>, и лишь после  этого вводить новое значение аргумента.

Когда редактирование формулы  будет завершено, щелкните на кнопке ОК или нажмите <Enter>, чтобы закрыть  диалоговое окно редактирования аргументов. Преобразовать формулу и обновить значение функции в ячейке.

 

3. Практическое задание

Произвести анализ объёма розничного товарооборота по РТП  «Россинка» по кварталам отчётного  года на основании следующих данных:

Динамика розничного товарооборота по РТП «Россинка»

         (сумма в тыс. руб.)

Квар- талы	Пред- шеств. год	Отчётный год			Индекс цен, %	т/о в сопост. ценах	Динамика, %
		план	факт	% вып. плана			в действ. ценах	в сопост. ценах
1	55467,4	105218	104462		191
2	113462,6	121000	121484		105
3	133844,1	157000	158884		145
4	138957,2	202000	203210		136
ИТОГО	441731,3	585218	588047		150