Интегрированный урок по приложения EXCEL, ACCEESS

Интегрированный урок по информатике  в 11 классе.

Тема: Использование табличного процессора Excel и СУБД Access при   

            решении экономических задач.

Цели и задачи: Продолжить изучение темы СУБД Access, повторить работу с формулами и построение диаграмм  в табличном процессоре Excel, установить   связь между этими двумя приложениями к ОS Windows.

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

б) воспитательные: Развитие познавательной активности учащихся.

Тип урока: Интегрированный урок повторения и сообщения знаний.

Методы проведения урока: Опрос, самостоятельная работа, рассказ, беседа, работа на компьютере.

Техническое оборудование: Компьютеры.

Раздаточный материал: Тесты, карточки, опорные конспекты с заданиями.

Ход урока:

1. Организационный момент.                                                           (1мин.)
2. Знакомство с планом урока.                                                         (2 мин.)
3. Проверка домашнего задания  (тест).                                          (5-7 мин.)
4. Разбор заданий для работы на компьютере.                               (10 мин.)
5. Практическая работа на компьютере (3 этапа).                          (20+30 мин.)
6. Обобщение и подведение итогов урока.                                      (10 мин.)             

План урока.

1. Проверка домашнего задания в виде теста (тест прилагается).

2.     Устный разбор заданий для выполнения на компьютере по каждому этапу:

                                I.      Рассказать каким образом будет происходить экспорт файла «Продукты» из текстового  процессора MS WORD в табличный процессор  MS Excel .

                             II.      Разобрать порядок выполнения заданий в табличном процессоре  MS Excel и записать необходимые формулы.

                          III.      Повторить, как происходит экспорт данных   из Excel в Access.

3.   Проделать все задания на компьютере и проанализировать результаты.

Вопросы и полный разбор заданий урока.

ЧАСТЬ 1.

Задание 1. Экспортировать файл «Продукты» из текстового  процессора MS WORD в табличный процессор  MS Excel.

Вопрос: Назвать порядок действий пользователя при выполнении задания?

Выполнение:

1 этап.

1. Запустить текстовый процессор Microsoft Word (Пуск ->  Программы->Microsoft Word).

2.Открыть файл «Продукты» (Файл -> Открыть  или комбинацией Ctrl+O ).  

3. Выделить имеющуюся  в файле таблицу с данными (Таблица ->  Выделить таблицу или комбинация клавиш Ctrl+F).

4. Скопировать выделенную таблицу в буфер обмена (Правка  -> Копировать  или комбинация клавиш Ctrl+С).          

2 этап.

1. Запустить табличный процессор Microsoft Excel (Пуск -> Программы->Microsoft Excel).

2. Установить курсор в ячейку F1 и вставить данные из буфера обмена(Правка ->    Вставить или комбинация клавиш Ctrl+V).

3. Изменить название рабочего листа «Лист1» на название «Исходная таблица». Порядок действий:

• Навести курсор мыши на название листа и щелчком правой кнопки мыши открыть контекстное меню. Выбрать пункт Переименовать и щелкнуть левой кнопкой мыши.
• Набрать новое имя рабочего листа (Исходная таблица) и подтвердить переименование нажатием клавиши Enter или щелчком левой клавиши мыши по любой ячейке рабочего листа.            

4.      Сохранить файл под именем «Таблица продукты».

Порядок действий:

·         Выполнить команду Файл  -> Сохранить как … (команда применяется в  случае сохранения нового файла или переименования существующего. Если необходимо просто сохранить изменения в файле, то применяется команда Файл -> Сохранить или комбинация клавиш Ctrl+S. Чтобы избежать случайной потери информации, необходимо применять команду Файл  -> Сохранить как можно часто).

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

·         Присвоить файлу новое имя «Таблица продукты».

·         Сохранить файл.

ЧАСТЬ 2.

Задание 1. Вычислить себестоимость, реализацию и прибыль по каждому факту реализации товара.

Вопрос:  Запишите на доске формулы необходимые для выполнения задания?

=Е2*G2;

=F2*G2;

=I2-H2.

    Выполнение:

  1 этап.

      В ячейках H1, I1, J1 необходимо соответственно напечатать Себестоимость, Реализация, Прибыль.    

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

o   Себестоимость (ячейка Н2)  ……….=Е2*G2;

o   Реализация (ячейка I2) .…………… =F2*G2;

o   Прибыль (ячейка J2)………………...=I2-H2;

2 этап.

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

Скопируем формулу, находящуюся в ячейке H2 на  диапазон H2:H200. Для этого воспользуемся маркером автозаполнения:

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

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

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

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

Для вычисления реализации, прибыли и торговой наценки по всем строкам таблицы необходимо заполнить соответственно диапазоны I2:I200, J2:J200.

Задание 2. Вычислить общую себестоимость, общую сумму реализации, общую прибыль.

Вопрос: Какая вычислительная операция необходима для выполнения задания?

Для выполнения этого задания необходимо применить функцию суммы. Он заключается в применении автосуммы.  Для применения автосуммы необходимо выделить ячейку под суммируемым диапазоном данных  Н201и нажать кнопку автосуммы ∑, которая расположена на панели инструментов «Стандартная».

Задание 3. Определить  минимальную, максимальную и среднюю сумму продаж.

Вопрос: Запишите на доске формулы необходимые для выполнения задания?

1.      = МИН (I2:I200);

2.      =МАКС (I2:I200);

3.      =СРЗНАЧ (I2:I200);

Выполнение:

Воспользуемся функциями МИН, МАКС и СРЗНАЧ из категории «Статистические».

1.       Функция МИН возвращает минимальное значение в указанном диапазоне, логические значения и текст игнорируется. Синтаксис функции := МИН (диапазон ячеек).

2.       Функция МАКС возвращает максимальное значение в указанном диапазоне, логические значения и текст игнорируется. Синтаксис функции := МАКС (диапазон ячеек).

3.       Функция СРЗНАЧ возвращает среднее арифметическое значение в указанном диапазоне. Синтаксис функции := СРЗНАЧ (диапазон ячеек).

Задание 5. Определить количество факторов реализации сахара.

Вопрос: Записать на доске формулу для выполнения задания?

= СЧЁТЕСЛИ (D2:D200; “сахар”)

Выполнение:

Воспользуемся функцией СЧЁТЕСЛИ из категории «Статистические», которая подсчитывает количество непустых ячеек, удовлетворяющих заданному условию. Синтаксис функции = СЧЁТЕСЛИ( диапазон; условие), где диапазон – это диапазон ячеек, в котором подсчитывается количество непустых ячеек, а условие – это условие в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Для корректной работы  функции в этом задании необходимо применять знак «*», который означает любой символ в любом количестве.

При выполнении задания в условии необходимо вводить текстовое значение «*Кр сахар*» или «* сахар*».

Задание 6. Определить сумму реализации сахара.

Вопрос: Какой функцией,  из какой категории будет записана формула для вычисления суммы реализации?

=СУММЕСЛИ(D2:D200; “сахар”;I2:I200)

Выполнение:

Воспользуемся  функцией СУММЕСЛИ из категории «Математические». Данная функция суммирует ячейки по данному условию.

Синтаксис функции :=СУММЕСЛИ(интервал; критерий; сумм _интервал):

где:

интервал – это интервал вычисляемых ячеек;

критерий – это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. (Например: 32, «23.10.00», «>32», «яблоки»).

  сумм _интервал – это фактические ячейки для суммирования.

Поэтому для выполнения данного задания  в ячейку Е212 необходимо ввести функцию

=СУММЕСЛИ(D2:D200;”сахар”;I2:I200),

где:

D2:D200 – диапазон ячеек, проверяемых условием;

“сахар” - условие отбора;

I2:I200 – диапазон ячеек , суммируемых при выполнении задания.

Примечание: текстовые условия и условия в формате даты необходимо вводить в кавычках.

Задание 7. Определить:

 а) сумму реализации в отделах «Гастрономия», «Бакалея», «Напитки»;

б) прибыль за 24.10.00;

в) сумму продаж за 24.10.00, 25.10.00, 26.10.00, 27.10.00;

г) сумму продаж товаров больше 12 рублей.

Запишите формулы, которые будут использоваться в расчетах?

а) =СУММЕСЛИ(C2:C200;”гастрономия”;I2:I200);

    = СУММЕСЛИ(C2:C200;”бакалея”;I2:I200);

    = СУММЕСЛИ(C2:C200;”напитки”;I2:I200);

б) = СУММЕСЛИ(B2:B200;”24.10.00”;J2:J200);

в) = СУММЕСЛИ(B2:B200;”24.10.00”;I2:I200);

    = СУММЕСЛИ(B2:B200;”25.10.00”;I2:I200);

    = СУММЕСЛИ(B2:B200;”26.10.00”;I2:I200);

    = СУММЕСЛИ(B2:B200;”27.10.00”;I2:I200);

г) = СУММЕСЛИ(B2:B200;”>12”;I2:I200);

После окончания расчетов необходимо перевести необходимые данные расчетной таблицы в денежный формат( Формат – ячейки…: закладка Число; Числовой форма – Денежный; Число десятичных знаков -2; Обозначение – р.).

Задание 8. Построить графики по результатам заданий 6 а) и 6 в)

Порядок построения графиков:

1)          выбрать диапазон данных ( в том числе заголовки строк), которые должны быть использованы в диаграмме ( например, диапазон В216:Е218);

2)          щелкнуть на кнопке Мастер диаграмм на  панели инструментов «Стандартная». Откроется первый этап Мастера диаграмм, в котором выбирается тип и вид диаграммы.

3)          Щелкнуть на кнопке Далее для перехода ко второму этапу Мастера диаграмм. На этом этапе необходимо выбрать данные для представления в диаграмме. В окне просмотра показано, как будет выглядеть диаграмма. Так как в п.1 уже был выбран диапазон данных с заголовками, то в окне выбора данных по умолчанию будут находиться необходимые для построения графика данные.

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

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

6)          щелкнуть на кнопке Далее для перехода к четвертому этапу Мастера диаграмм, в котором можно определить размещение диаграммы на отдельном листе либо на имеющемся листе;

7)          последний шаг щелкнуть на кнопке Готово для вставки диаграммы в рабочий лист.

ЧАСТЬ 3.

Задание 1. Экспортировать файл «Таблица продукты» из Excel в Access.

Выполнение:

1.      выделить исходную таблицу (диапазон данных А1:J200);

2.      скопировать выделенные данные в буфер обмена;

3.      перейти на другой рабочий лист «Лист2»;

4.      вставить данные из буфера обмена в ячейку А1;

5.      присвоить рабочему листу «Лист2» новое имя «Импорт»;

6.      сохранить и закрыть файл;

7.      запустить систему управления базами данных Microsoft Access (Пуск->        Программы -> Microsoft Access);

8.      в открывшемся окне указать создание новой базы данных (Новая база данных);

9.       сохранить базу данных под именем «База продукты».

         Порядок действий:

o   В появившемся окне сохранения документа необходимо выбрать место расположения файла. При необходимости создать новую папку;

o   Присвоить файлу новое имя «База продукты».

o   Сохранить файл( кнопка Создать).

10.  для экспорта данных из Excel в Access необходимо применить команду Файл  ->

      Внешние данные  -> Импорт;

в окне импорта необходимо:

o   определить тип файла (Microsoft Excel);

o   найти файл «Импорт» с выполненным в Excel заданием;

o   завершить операцию нажатием кнопки Импорт;

1)      после выполнения команды импорт начинает работать мастер импорта данных, в котором выбирается лист, содержащий данные, подготовленные на предыдущем этапе (пункт листы помечается «·»). Для перехода к следующему этапу используется кнопка Далее;

2)      на втором этапе работы мастера импорта данных происходит преобразование строки, содержащей заголовки в поля таблицы (ставится флажок (٧) около надписи «Первая строка заголовка»). Для перехода к следующему этапу используется кнопка Далее;

3)      третий этап работы мастера импорта данных позволяет пользователю выбрать место сохранения данных: в новой таблице или в существующей. Выбираем новую таблицу. Для перехода к следующему этапу используется кнопка Далее;

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

5)      пятый этап работы мастера импорта данных позволяет создать или определить ключевое поле (поставить «·» в поле автоматически создать ключ, выбрав поле «Код» в качестве ключевого поля), которое будет необходимо при выполнении следующего задания. Для перехода к следующему этапу используется кнопка Далее;

6)      на шестом этапе работы мастера импорта данных присвоить новое имя таблице и произвести анализ таблицы на наличие ошибок импорта. Щелкнуть на кнопке Готово.

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

Какие действия необходимо произвести, для того чтобы выполнить это задание?

Выполнение:

o   Щелкнуть на вкладке Запросы в окне база данных.

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

o   Добавить таблицу «Данные».

o   В строке Поле: выбрать – Наименование товаров.

o   В строке Условие отбора: ввести «сахар».

o   Сохранить структуру запроса (команда Файл  ->  сохранить).

o   Присвоить новое имя запросу и закрыть окно конструктора запросов.

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

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

Какие действия необходимо произвести, для того чтобы выполнить это задание?

      Выполнение:

o   Щелкнуть на вкладке Запросы в окне база данных.

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

o   Добавить таблицу «Данные».

o   В строке Поле: выбрать – Тип товара и Реализация.

o   В строке Условие отбора: ввести «сахар».

o   В строке Групповая операция выбрать Sum.

o   Сохранить структуру запроса (команда Файл  ->  сохранить).

o   Присвоить новое имя запросу и закрыть окно конструктора запросов.

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

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

Вопросы подведения и обобщения итогов урока.

1.      Что нового вы узнали на уроке?

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

3.      Какое свойство OS Windows было использовано при выполнении практической работы?