Поиск

Полнотекстовый поиск:
Где искать:
везде
только в названии
только в тексте
Выводить:
описание
слова в тексте
только заголовок

Рекомендуем ознакомиться

Информатика, программирование->Лабораторная работа
Задайте ім'я вузла комутатора CustomerSwitch, використовуючи наступні команди: >enable #configure terminal (config)#hostname CustomerSwitch Крок . Нал...полностью>>
Информатика, программирование->Лабораторная работа
На заре компьютеризации все управление пользователями сводилось к администрированию одного единственного сервера. Со временем ситуация стала меняться,...полностью>>
Информатика, программирование->Лабораторная работа
Вас попросили, щоб ви виправити помилки конфігурації в корпоративній мережі. Для цієї лабораторної, не використовуйте Логін або захист паролем на будь...полностью>>
Информатика, программирование->Лабораторная работа
По сути своей маршрутизатор – это специализированный компьютер, предназначенный для передачи данных между несколькими интерфейсами к которым могут быт...полностью>>

Главная > Лабораторная работа >Информатика, программирование

Сохрани ссылку в одной из сетей:

Работа со списками в MS EXCEL

Цель: Приобрести навыки поиска и агрегирования данных в списке.

Краткая теория

Компьютерные информационные технологии широко используются для анализа данных и подготовку управленческих решений на основе экономико – математических моделей. Удобным средством обработки больших объемов информации и решения широкого круга экономических, финансовых и управленческих задач является среда моделирования MS Excel. Экономико – математические модели, реализуемые в среде MS Excel, основываются на встроенных функциях и программах написанных на языке программирования VB for Application.

К стандартным технологиям анализа данных MS Excel относятся, например,

  • Представление исходных данных в виде списков (баз данных);

  • Упорядочивание и фильтрация список;

  • Отбор данных из списка по определенному критерию;

  • Формирование итоговых отчетов и компактное представление данных в виде сводных таблиц;

  • Использование встроенных функций MS Excel для решения задач финансового менеджмента, статистических задач, задач прогнозирования и т.д.;

  • Технология подбора параметра;

  • Многовариантные расчеты с применением сценариев для принятия решения;

  • Технология подстановки табличных значений параметров в функциональной модели;

  • Методы математического программирования для решения оптимизационных задач;

  • Консолидация (агрегирование данных);

  • Графические методы решения экономических задач и представления результатов и т.д.

Понятие о списке

Список MS EXCEL представляет собой БД для хранения и извлечения информации. Данные в такой БД хранятся в виде записей. Записи можно просматривать, редактировать, добавлять или удалять.

MS Excel позволяет работать с реляционными базами данных, которые называются списками. Список представляет собой БД для хранения и извлечения информации и к нему применимы те же требования, что и для реляционных БД. Данные в такой БД хранятся в виде записей. Записи можно просматривать, редактировать, добавлять или удалять. В работе со списками используются такие операции обработки данных как сортировка, группировка и фильтрация (отбор данных по критерию), агрегирование (подведение промежуточных итогов), структурирование и консолидация данных.

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

Адресация ячеек на рабочем листе

В MS EXCEL существует два способа адресации ячеек рабочего листа: относительный и абсолютный. Абсолютный адрес ячейки не изменяется при копировании формулы. Знак $ перед буквой в имени ячейки означает абсолютную ссылку на столбец с данным именем, а $ перед цифрой дает абсолютную ссылку на строку с этим номером. Для ввода в формулу абсолютного адреса достаточно после ввода ее относительного адреса нажать клавишу <F4>.

Другой способ абсолютной адресации возможен с помощью команды Вставка/ Имя/ Присвоить. Такое назначение имен позволяет значительно сократить время ввода и сделать понятной запись формул.

ОТБОР ДАННЫХ

Фильтрация списка с помощью расширенного фильтра

В MS EXCEL существует два способа отбора нужных данных из списка: с помощью автофильтра и расширенного фильтра.

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

Для отображения данных, соответствующих заданному условию, необходимо

  • создать исходный список с заголовками столбцов;

  • установить курсор в список;

  • выполнить команду меню Данные/Автофильтр;

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

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

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

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

Алгоритм выполнения фильтрации с помощью расширенного фильтра:

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

  2. Скопируйте заголовки критериев отбора в смежные клетки. Предусмотрите хотя бы одну свободную клетку под заголовком критерия.

  3. Введите в строки под заголовками условий требуемые критерии отбора.

  4. Установите курсор в список.

  5. Выберите пункт Фильтр в меню Данные/ Расширенный фильтр.

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

  7. В поле Исходный диапазон введите диапазон ячеек, содержащий исходный список вместе с заголовками.

  8. Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов.

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

Примеры условий отбора расширенного фильтра

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

На ячейки одного столбца накладываются три или более условий отбора

Заказчик

Иванов

Петров

Сомов

Чтобы задать для отдельного столбца три или более условий отбора, введите условия в ячейки, расположенные в смежных строках (т.е. в столбик). Например, для следующего диапазона условий будут отобраны строки, содержащие либо «Иванов», «Петров» или «Сомов» в столбце «Заказчик».

Условие отбора накладывается на ячейки двух или более столбцов

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

Дата

Сумма

22.03.09

>6000

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

Дата

Сумма

22.03.09

>6000

Чтобы наложить сложное условие отбора, введите его составные части в отдельные строки диапазона условий. В нашем примере отобразятся записи о покупках на дату 22.03.99 с суммой свыше 6000 руб. и на дату 6.04.99 с суммой менее 5000 руб.

Дата

Сумма

22.03.09

>6000

6.04.09

<5000

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

В условии фильтрации можно использовать возвращаемое формулой значение. При задании формул в условиях не используйте в качестве заголовка условия заголовки столбцов списка. Введите заголовок, который не является заголовком столбца списка или оставьте ячейку заголовка условия незаполненной. Для приведенного ниже примера критерия отбора в результате фильтрации будут отображены строки, в которых значение столбца E превышает среднее значение в ячейках F2: F15. Таблица критериев содержит две ячейки, одна из которых пустая (поле заголовка условия не заполнено).

=E2>СрЗнач($f$2:$f15)

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

Можно также рассчитать среднее значение по формуле СЗЗНАЧ в любой из ячеек рабочего листа (например, H3), и затем использовать ссылку на эту ячейку в таблице критериев.

Больше среднего

=E2>$H$2

Замечания:

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

  • Используемая в условии формула должна ссылаться либо на заголовок столбца (например, «Сумма»), либо на соответствующее поле в первой записи. В приведенном примере E2 ссылается на соответствующее поле (столбец E) первой записи (строка 2) списка.

  • При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона, в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Пример 1: Вывести сведения о сотрудниках, имеющих стаж больше среднего по предприятию.

Список сотрудников

Номер сотрудника

ФИО

Пол

Стаж

Должность

Оклад

Для решения задачи воспользуемся расширенным фильтром.

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

  1. Создать исходный список в диапазоне A3: F23.

  2. Рассчитать среднее значение стажа сотрудников в ячейке, например, D25.

  3. В ячейках H3:H3 создать таблицу критериев отбора записей.

Стаж > среднего

=D4>$D$25

  1. Установить курсор в список.

  2. Выбрать пункт меню Данные /Фильтр / Расширенный фильтр.

  3. Установить переключатель Обработка в положение Скопировать результаты в другое место.

  4. В поле Исходный диапазон введите диапазон ячеек, содержащий исходный список вместе с заголовками (A3:F23).

  5. Ввести в поле Диапазон критериев ссылку на диапазон таблицы критериев (H3:H3).

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

  7. Ознакомьтесь с полученными результатами.

Задание 1. На основании описанных выше примеров условий отбора выполните несколько вариантов отбора записей для произвольных критериев.

СОЗДАНИЕ ПРОМЕЖУТОЧНЫХ ОТЧЕТОВ

В ЭИС часто используют агрегирование данных, т.е. группировку по каким-либо показателям и расчет итогов. На основе полученных значений формируются отчеты, которые могут быть использованы для анализа и принятия решений и передачи во внешние ИС.

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

Ниже приведен алгоритм подготовки данных и создания на их основе промежуточных отчетов:

Подготовка данных для создания промежуточных отчетов

  1. Ввести данные в список

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

Создание промежуточного отчета

  1. Укажите ячейку (домен) в этом списке (установите курсор в список) и выполните команду Данные/ Итоги.

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

  3. Выберите функцию, необходимую для подведения итогов, из списка Операция.

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

Рассмотрим пример создания БД «Учет продаж товаров».

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



Загрузить файл

Похожие страницы:

  1. Работа со списками в MS EXCEL. Консолидация данных

    Лабораторная работа >> Информатика, программирование
    Работа со списками в MS EXCEL. Консолидация данных Цель: Приобрести навыки консолидации данных в среде MS Excel. Краткая теория Цель ... работы: Получить навыки вычислений и подведения итогов в MS Excel для ...
  2. Работа с базами данных в MS EXCEL (2)

    Лабораторная работа >> Информатика, программирование
    ... в MS EXCEL Цель: Приобрести навыки использования встроенных функций МS Ехсеl для работы со списками. Краткая ... . Для достижения наибольшей эффективности при работе со спи­сками необходимо следовать некоторым правилам. Правила ...
  3. Создание информационной системы средствами MS Excel и VBA

    Курсовая работа >> Информатика
    ... данных 3. Список. Составляющие списка В MS Excel можно работать со списками любого объема. В Excel БД-это просто список ... , Год издания, Издательство 8.Фильтрация данных в списке В MS Excel списком называется снабженная метками последовательность строк ...
  4. Обработка данных с помощью средств MS Excel

    Контрольная работа >> Информатика
    ... . 2. СТРУКТУРА КОНТРОЛЬНОЙ РАБОТЫ Структура контрольной работы должна быть следующей: титульный ... Обработка данных с помощью средств MS Excel» Вариант 1 Используя соответствующие финансовые ... строку). 9. Вставьте перед диапазоном со списком 11 пустых строк. 10. ...
  5. Решение задач с использованием возможностей MS Excel

    Практическая работа >> Информатика, программирование
    ... . Задание 4. Описать технологию работы со списками в электронной таблице Excel (создания списка (из не менее 10 ... в текстовом редакторе MS Word. Подготовить книгу Excel с примерами, демонстрирующими технологию работы со списками (таблица 4). Литература ...

Хочу больше похожих работ...

Generated in 0.0014650821685791