Основные понятия Microsoft Excel

Обработка списков

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

Базы данных

В комплект поставки Excel входит программа обработки внешних  баз данных MS Query. Программа Query (Запрос) может работать с текстовыми файлами и с данными, которые были подготовлены следующими программами управления базами данных: Ms Access, dBASE, FoxPro, Oracle, Paradox, SQL Server. Если в процессе работы с таблицей в программе Excel возникает необходимость обратиться к внешней базе данных, нужно запустить программу Query. Это позволит импортировать данные из базы и обрабатывать их как обычные табличные данные в программе Excel. В некоторых случаях внешние данные можно обработать непосредственно программой Query. После загрузки набора данных из него можно выбрать часть записей путем включения критериев отбора данных.   

Конфигурирование  программы

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

     §2 Понятие макроса

Работая с приложениями Microsoft Office, например, с Word или Excel, можно заметить, что часто одни и те же последовательности действий повторяются, и, естественно, возникает желание автоматизировать этот процесс, сделав так, чтобы часто  выполняемую последовательность действий заменит каким-либо одним действием. В приложениях Microsoft Office (Word, Excel и  др.) это реализовано под названием  макрос – возможность воспроизведения  последовательности нажатий клавиш, выбора команд и выполнения других элементарных действий. После записи эта последовательность может быть многократно повторена. Макросредства  упрощают работу с соответствующими приложениями Microsoft Office и позволяют  настраивать его согласно нуждам конкретного пользователя.

Некоторые задачи достаточно легко автоматизировать с помощью  макросов, например:

- открытие, выделение и  обновление документа, содержащего  коды полей;

- сохранение параметров  настройки и быстрое переключение  между ними;

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

- добавление дополнительных  кнопок масштабирования и редактирования  на панели инструментов.

     Но главное  достоинство макросов в том,  что они совершенно равноправны  с другими командами. Это значит, что когда мы создаем макрос, то мы его можем конструировать  как из набора команд, так и  включать в него другие макросы.  Сфера применения макросов достаточно  широка, но обычно макросы используют  для редактирования; объединения  сложных команд; упрощения доступа  к параметрам (данным) в диалоговых  окнах; автоматизации обработки  больших последовательностей сложных  действий; ускорения часто выполняемых  операций форматирования и взаимодействия  между приложениями Windows и пользовательскими  программами. Созданные макросы  сохраняются либо как глобальные, либо как макросы шаблонов  или документов. Глобальные макросы  хранятся в шаблоне Normal.dot и  доступны из любого документа  или шаблона. Макросы, сохраненные  в шаблоне, доступны только  в документах,основанных в этом  шаблоне. А, макросы, сохраненные  в документе, доступны только  в самом документе.

§ 3  Применение макросов в процессе автоматизации                

  работы    Microsoft Excel

MS Excel – мощная программа  для хранения и автоматизации  расчета данных, представленных  в табличной форме, обладает  всеми необходимыми возможностями  для обработки электронных таблиц: от простых арифметических действий  с несколькими ячейками таблицы  до статистической обработки  огромных массивов данных. Если, работая в Excel, встречаются повторяющиеся  задачи, то необходимо создать  макрос. Макрос представляет собой  последовательность команд, сохраненных  в виде модуля Microsoft Visual Basic. При  запуске макроса все эти команды автоматически выполняются, что обеспечивает быстрое выполнение задач [12]. Создание макросов в MS Excel производится аналогично созданию макросов MS Word.

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

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

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

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

1.         Щёлкните по ярлыку листа Февраль.

2.         Выполнить Формат -> Лист -> Скрыть.

3.         Щёлкните по ярлыку листа Апрель.

4.         Выполнить Формат -> Лист -> Скрыть.

5.         Щёлкните по ярлыку листа Июнь.

6.         Выполнить Формат -> Лист -> Скрыть.

Остановите запись макроса. Запустите макрос СкрытьЛисты.

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

1.         Выполнить Формат -> Лист -> Отобразить.

2.         Из списка скрытых листов выбрать  Февраль и щёлкнуть по кнопке  ОК.

3.         Выполнить Формат -> Лист -> Отобразить.

4.         Из списка скрытых листов выбрать  Апрель и щёлкнуть по кнопке  ОК.

5.         Выполнить Формат -> Лист -> Отобразить.

6.         Из списка скрытых листов выбрать  Июнь и щёлкнуть по ОК.

Остановите запись макроса. Запустите макрос ОтобразитьЛисты.

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

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

Теперь нужно выполнить  последовательность действий, которая  будет выполняться при выполнении макроса.

1.         Создать новый рабочий лист.

2.         Выделить ячейки А1:Е7. Выполнить  Правка -> Очистить -> Все.

3.         В ячейке А1 набрать текст  «Отчет о продажах» (без кавычек).

4.         В ячейке А3 набрать «Регион».

5.         В ячейке А4 набрать «Север».

Рис. 8

6.         В ячейке А5 набрать «Юг».

7.         В ячейке А6 набрать «Запад». 

8.         В ячейке А7 набрать «Восток».

9.         В ячейке В3 набрать «1 квартал».

10.      В ячейке  С3 набрать «2 квартал».

11.      В ячейке D3 набрать «3 квартал».

12.      В ячейке E3 набрать «4 квартал».

13.      Выделить  диапазон A3:E7 и установить границы  ячеек.

14.      Выделить  диапазон B3:E7 и применить форматирование  «По центру».

15.      Выделить  диапазон А1:E1 и применить форматирование  «Объединить и поместить в  центре».

16.      Установить  курсор в ячейку B4

Остановите запись макроса. Измените имя созданного рабочего листа  на 2005 год. Сохраните рабочую книгу  под именем Отчёты.

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

Откройте рабочую книгу  Отчёты. Запустите макрос НовыйОтчёт два раза. Измените имена созданных  рабочих листов на 2006 год и Консолидация. Заполните диапазон В4:Е7 листа 2005 год  и диапазон В4:Е7 листа 2006 год числовыми  данными. Запустите запись макроса, назвав его КонсолидацияОтчётов, и  выполните следующую последовательность действий:

1. Установите курсор в  ячейку В4 листа Консолидация.

2. В меню Данные выберите  пункт Консолидация.

3. В окне Консолидация  из раскрывающегося списка функций  выбрать Сумма.

Рис. 9 Окно Консолидация.

4. В Список диапазонов  добавить ссылки диапазон В4:Е7  листа 2005 год и диапазон В4:Е7  листа 2006 год.

5. Выбрать пункт Создавать  связи с исходными данными.

6. Закрыть окно Консолидации, щёлкнув по кнопке ОК.

Остановите запись макроса.

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

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

1.         Щёлкните по ярлыку листа 2005 год.

2.         Выполнить Файл -> Печать и щёлкнуть  по кнопке ОК.

3.         Щёлкните по ярлыку листа 2006 год.

4.         Выполнить Файл -> Печать и щёлкнуть  по кнопке ОК.

5.         Щёлкните по ярлыку листа Консолидация.

6.         Выполнить Файл -> Печать и щёлкнуть  по кнопке ОК.

§4  Пример автоматизации работы Microsoft Excel с                 помощью макросов

 

Пример 1

Для начало запмшем макрос для вывода автоматизации одной ячейки, для  этого нажмем Макрос/запись макроса  и назовем его «ВыводИсполнителя» и обозначим Ctrl+Q и жмем ОК:

 

Потом нажимаем Ctrl+Q и вот результат автоматизации :

пример2

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

 

 

 

Результат выполненной работы при  выполнении комбинации Ctrl+ц :

 

Можно также автоматизировать сортировку по горячим клавишам ,

Например по Фамилии и по Сумме:

сортируя По Фамилии используя  клавиши Ctrl+у.

 

 

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

  

сортировка по Сумме используя  клавиши Ctrl+к.

 

 

 

 

 

 

 

 

Результат :

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Заключение

        Характерной чертой современности является стремительный научно-технический прогресс, что требует от менеджеров и бизнесменов значительного повышения ответственности за качество принятия решений. Это основная причина, которая обусловливает необходимость научного принятия управленческих решений. При помощи этого продукта можно анализировать большие массивы данных. В Excel можно использовать более 400 математических, статистических, финансовых и других специализированных функций, связывать различные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры. Программа MS Excel, являясь лидером на рынке программ обработки электронных таблиц, определяет тенденции развития в этой области. Вплоть до версии 4.0 программа Excel представляла собой фактический стандарт с точки зрения функциональных возможностей и удобства работы. На современном рынке завоевала популярность версия 7.0, которая содержит много улучшений и приятных неожиданностей. Основной программой является вычислительный модуль, с помощью которого выполняется обработка текстовых и числовых данных. В данной версии программы у этого модуля появилось множество интересных функций: контроль допустимости вводимых значений, обнаружение и исправление наиболее распространенных ошибок ,вычисление полей в свободных таблицах и другие.