Получение значения из электронной таблицы и заполнения массива

1. Откройте новую книгу и введите значения в ячейки a1: a10 на листе Sheet1.

2. Клавиши Alt + F11, чтобы запустить редактор Visual Basic.

3. В меню Вставка выберите пункт Модуль.

4. Введите в окне модуля следующий код:

5. Sub From_sheet_make_array()6. Dim myarray As Variant7. myarray = Range("a1:a10").Value8. 9. 'Looping structure to look at array.10. For i = 1 To UBound(myarray)11. MsgBox myarray(i, 1)12. Next13. End Sub14.

15. В меню файл выберите команду Закрыть и вернуться в Microsoft Excel.

16. Выберите Лист1.

17. В меню Сервис выберите пункт Макрос и выберите команду Макросы.

18. В диалоговом окне макроса выберите From_sheet_make_array и нажмите кнопку запустить.

Для передачи и получения массива

1. Откройте новую книгу.

2. Клавиши Alt + F11, чтобы запустить редактор Visual Basic.

3. В меню Вставка выберите пункт Модуль.

4. Введите в окне модуля следующий код:

5. Sub Pass_array()6. Dim myarray As Variant7. myarray = Range("a1:a10").Value8. receive_array myarray9. End Sub10. 11. Sub receive_array(thisarray)12. For i = 1 To UBound(myarray)13. MsgBox myarray(i, 1)14. Next End Sub

15. В меню файл выберите команду Закрыть и вернуться в Microsoft Excel.

16. Выберите Лист1 и выделите диапазон a1: a10.

17. В меню Сервис выберите пункт Макрос и выберите команду Макросы.

18. В диалоговом окне макроса выберите Pass_array и нажмите кнопку запустить.

Анализ деловых данных в Microsoft Excel. Решение оптимизационных задач с помощью Подбора параметра и Поиска решения. Создание сценариев.

Решение уравнений и задач оптимизации

Для решения задач оптимизации широкое променение находят различные средства Excel.

В этом разделе рассмотрим команды:

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

 Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям;

 Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных.

Подбор параметров

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

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка

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

 формула для расчета;

 пустая ячейка для искомого значения;

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

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления)

Оптимизация с помощью команды Подбор параметров выполняется так:

1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.

 

2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра..

 

3. Введите в текстовое поле Значение число, соответствующее объему продаж - 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.

 

После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.

Команда Поиск решения

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

После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.
1. Выделите на листе целевую ячейку, в которую введена формула.
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».

 

3. Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

31.Обработка информации в списках в Microsoft Excel. Сортировка данных в списках. Использование фильтра. Упорядочивание списков с помощью команды Итоги. Использование консолидации. Работа со сводными таблицами.

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

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

 

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

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

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

Диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка. Этот диапазон должен быть расположен на том же листе, что и список. Если извлеченную информацию необходимо поместить на другой лист, ее копируют в буфер, а затем вставляют в нужное место. Следует отметить, что для извлечения информации из баз данных, находящихся во внешнем источнике, можно использовать программу Microsoft Query или же следует воспользоваться импортированными списками из других приложений, обрабатывающих данные (например, СУРБД MS Access).

Рекомендации по организации списка в MS Еxсel.

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

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

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

4. Исключать поля, без которых можно обойтись.

5. Строку заголовка всегда располагать сверху над всем списком.

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

7. Следует отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой. Это помогает MS Excel легко выделять наименованный список при выполнении фильтрации или при сортировке данных.

8. Список может занимать весь рабочий лист.

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

10. Имена полей нельзя отделять от данных пустыми строками или пунктирными линиями.

11. Не располагать слева или справа от списка формулы или важные данные. В режиме фильтрации диапазон ячеек слева или справа может оказаться скрытым (т.е. скрываются и данные).

12. Не располагать информацию под неограниченным диапазоном для извлечения. Извлекаемые данные могут наложиться на нее.

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

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

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

Ввод данных в список. Существуют следующие способы ввода данных в список:

 использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды линейки меню Данные – Форма;

 ввод данных во вставляемые в список пустые строки; в этом случае имя диапазона списка переопределяется автоматически (непосредственно ввод данных);

 использование средства Автоввода и команды Выбрать из списка для ускорения работы;

 использование форм Access и дальнейший перенос данных на лист MS Excel;

 использование Мастера шаблонов для преобразования рабочего листа MS Excel в форму;

 применение VBA. Соответствующая программа будет предоставлять форму или окно диалога для ввода данных и их последующего помещения в определенные ячейки рабочего листа MS Excel.

Работа со списками в MS Excel. Работа с подготовленным списком в MS Excel может осуществляться по трем направлениям:

 сортировка – выстраивание данных в нужном порядке;

 отбор данных – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями);

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

 

Основные понятия баз данных и их классификация. БД и системы управления реляционными базами данных. Структура простейшей базы данных.Объекты базы данных.Проектирование баз данных

База данных (БД) представляет собой совокупность структуриро­ванных данных, хранимых в памяти вычислительной системы и ото­бражающих состояние объектов и их взаимосвязей в рассматриваемой предметной области.

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

Система управления базами данных (СУБД) — это комплекс языко­вых и программных средств, предназначенный для создания, ведения и совместного использования БД многими пользователями. Обычно СУБД различают по используемой модели данных. Так, СУБД, осно­ванные на использовании реляционной модели данных, называют ре­ляционными СУБД.

Для работы с базой данных зачастую достаточно средств СУБД. Однако если требуется обеспечить удобство работы с БД неквалифи­цированным пользователям или интерфейс СУБД не устраивает пользо­вателей, то могут быть разработаны приложения. Их создание требует программирования. Приложение представляет собой программу или комплекс программ, обеспечивающих автоматизацию решения какой-либо прикладной задачи. Приложения могут создаваться в среде или вне среды СУБД — с помощью системы программирования, исполь­зующей средства доступа к БД, к примеру, Delphi или С++ Вuildег. Приложения, разработанные в среде СУБД, часто называют приложе­ниями СУБД, а приложения, разработанные вне СУБД, — внешними приложениями.

ередаваемых данных.

Выделяют следующие виды СУБД:

* полнофункциональные СУБД;

* серверы БД;

* средства разработки программ работы с БД.

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

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

2. Значения каждого атрибута должны принадлежать к одному и то­му же типу.

3. Каждая запись в таблице уникальна.

4. Каждое поле имеет уникальное имя.

5. Последовательность полей и записей в таблице не существенна.