5.4.1. Заполнение таблиц модельными данными
В соответствии с проектом у нас должны быть следующие таблицы:
-
Справочник по товарам
-
Справочник по поставщикам
-
Данные о продажах
-
Данные о поставках
-
Сотрудники
Первая, вторая и пятая таблицы относительно небольшие и содержат условно-постоянную информацию. В самом деле – не так уж и часто на рынке появляются новые товары и не так уж и часто меняются налаженные схемы поставок. Поэтому заполнять эти таблицы придется вручную (напрягая фантазию).
Например, данные о товарах:
| A | B | C | D | E | F | G |
1 |
|
|
|
|
|
|
|
2 |
| Код товара | Наименование | Сорт | Оптовая цена | Наценка | Код поставщика |
3 |
| 1 | Лапти липовые | 2 | 100 | 10% | 1 |
4 |
| 2 | Лапти дубовые | 1 | 200 | 20% | 1 |
5 |
| 3 | Галоши | 2 | 150 | 10% | 2 |
6 |
| 4 | Валенки | 2 | 300 | 10% | 3 |
7 |
| 5 | Кепка a ’la Lenin | 2 | 400 | 10% | 4 |
8 |
| 6 | Кепка a 'la Luzkov | 1 | 500 | 20% | 4 |
Таблицы «Продажи» и «Поставки» являются самыми большими, поскольку в нормально работающем магазине идет непрерывный процесс продаж и за счет поставок проданные товары постоянно обновляются. Поэтому и записи в этих таблицы добавляются ежедневно и в больших количествах.
Все описания ориентированы на размещение данных согласно приводимым рисункам.
Если вы разместите данные в других ячейках, то соответственно должны быть откорректированы адреса во всех формулах.
Для студентов экономических специальностей самым простым методом получения больших объемов модельных данных является использование встроенных функций.
Рассмотрим эту технологию на примере заполнения таблицы продаж.
Пусть шапка этой таблицы имеет следующий вид:
|
| С | D | E | F | G | H |
10 |
|
|
|
|
|
|
|
11 |
| Дата продажи | Код товара | Наименование | Количество | Цена | Сумма |
12 |
|
|
|
|
|
|
|
13 |
|
|
|
|
|
|
|
И пусть в ней должны храниться сведения о продажах за прошедший месяц. Предположим также, что в день у нас производится по десять продаж. Тогда общее количество записей будет равно 300.
Очевидно, что
– колонки С, D и F должны заполняться случайно;
– колонки E, и G будут заполняться исходя из данных справочника по товарам;
– колонка H должна рассчитываться по данные колонок F и G.
Для заполнения колонок случайными данными можно использовать имеющуюся в Excel функцию генерации случайных чисел – СЛЧИС(). Она генерирует случайные числа из диапазона 0..1. Для генерации целых чисел из произвольного диапазона используется формула:
=А + ЦЕЛОЕ((В – А+1)*СЛЧИС()) (5.1)
где А – нижняя граница необходимого диапазона;
В – верхняя граница диапазона;
ЦЕЛОЕ – имеющаяся в Excel функция округления дробных чисел.
Начнем с колонки «Дата продажи».
Для этой колонки нам необходимо определить параметры A и B в формуле (5.1).
Для определения параметра A:
– в отдельную ячейку (например, в A1) вводим начальную дату продаж – пусть это будет 01.10.09. Задаем для этой ячейки формат «общий». В ней получится число 40087. Это будет число дней, прошедших с сначала прошлого века (с 1900 года).
Поэтому в ячейку С6 вводим формулу:
= 40087+ ЦЕЛОЕ(30 * СЛЧИС())
и копируем ее на 300 строк данного столбца.
Вы должны были обратить внимание на то, что после каждой манипуляции с данными их значения меняются. Это свойство функции СЛЧИС.
Чтобы избавиться от этого эффекта:
– выделяем столбец C и копируем его в буфер;
– не снимая выделения произведем перекопирование данных командой Правка > Специальная ставка > Значения;
– не снимая выделения, преобразуем данные столбца C в формат «Дата» (Формат > Ячейки > Дата).
Не забудьте удалить из А1 ненужную теперь дату.
По аналогичной схеме заполняется колонка D – «Код товара»:
– в ячейку D12 вводится формула
= 1+ ЦЕЛОЕ(6 *СЛЧИС())
(здесь 6 – количество товаров);
– формула копируется на 300 строк;
– путем перекопированния столбца D избавляемся от формулы.
Аналогично заполняется колонка F – «Количество»:
– в ячейку F12 вводится формула
= 1+ ЦЕЛОЕ(10 *СЛЧИС())
(здесь 10 – количество товаров, т.е. больше 10–и кепок в одни руки не даем!);
– формула копируется на 300 строк;
– путем перекопированния столбца F избавляемся от формулы.
Для заполнения столбца E в ячейку E12 вводим формулу:
=ВПР(D12;Справочник_товары;2)
и копируем ее на 300 строк.
Формула содержит функцию ВПР, которая ищет значение поля D12 в первой колонке справочной таблицы товаров и в качестве результата берет значения из второй колонки таблицы товаров.
В ячейку G12 должна быть введена формула расчета розничной цены исходя из данных справочника по товарам. В общем виде она выглядит следующим образом:
РозничнаяЦена = ОптоваяЦена*(1+Наценка) (5.2)
При реализации в Excel эта формула должна «сама» по коду товара из столбца D брать с листа Товары» значения оптовой цены и наценки. Для этого также используется функция ВПР. Т.е. в G12 вводится формула:
= ВПР(D12; Справочник_товары;4) * (1 + ВПР(D12; Справочник_товары;5))
Обратите внимание, в первой ВПР оптовая цена берется из четвертой колонки справочной таблицы, а во второй – наценка берется из пятой колонки справочной таблицы. Данная формула копируется на весь столбец G.
И, наконец, в столбец H вводится формула расчета суммы покупки (с последующим копированием): = F12 * G12.
Таблица заполнена.
Осталось только отсортировать ее по полю «Дата продажи» и присвоить имя – «Данные_продаж».
По аналогичной схеме можно создать таблицу «Поставки».
- Содержание
- VII. Статистические методы 167
- Введение
- I. Общие методы работы
- 1.1. Работа с формулами
- 1.1.1. Общие сведения
- Вычисления сложных выражений
- 1.1.2. Задание
- 1.2. Математические функции
- 1.2.1. Общие сведения
- 1.2.2. Пример
- 1.2.3. Варианты заданий
- 1.3. Вычисления с условиями
- 1.3.1. Общие сведения
- 1.4. Работа со справочниками
- 1.4.1. Общие сведения
- 1.4.2. Варианты заданий
- «Разносортица»
- «Маршрутное такси»
- «Гостиница»
- «Автовокзал»
- «Книжное издательство»
- «Продукты»
- «Коттеджи»
- «Гастроли»
- «Туристическое агентство»
- «Комплектующие»
- «Авиаперевозки»
- «Винный погребок»
- «Сберкасса»
- «Мебельная фабрика»
- 16. «Сага о таре»
- 1.5. Работа с диаграммами
- 1.5.1. Общие сведения
- 1.5.2. Задание на построение диаграммы
- 1. Изменение настроек параметров диаграммы:
- 3. Форматирование рядов данных и их элементов:
- 4. Форматирование осей диаграммы:
- 5. Форматирование сетки, стен и основания:
- 6. Форматирование легенды:
- 1.5.3. Варианты заданий
- 1.6. Собственные функции
- 1.6.1. Общие сведения
- 1.6.2. Общие сведения о Visual Basic for Excel
- Математические операции
- Математические функции
- 1.6.3. Варианты заданий
- II. Численные методы
- 2.1. Решение алгебраических уравнений Средство «Подбор параметра»
- 2.1.1. Общие сведения
- 2.1.2. Пример
- 2.1.3. Варианты заданий
- 2.2. Решение систем уравнений
- 2.2.1. Общие сведения
- 2.2.2. Реализация расчетов в Excel
- 2.2.3. Варианты заданий
- 2.3. Задачи оптимизации
- 2.3.1. Общие сведения
- 2.3.2. Пример
- 2.3.3. Варианты заданий
- III. Базы данных в ms Excel
- Каждое из последующих заданий необходимо выполнять на отдельном листе!!!
- Сортировка
- 3.1.1. Общие сведения
- 3.1.2. Варианты заданий
- Фильтрация данных
- 3.2.1. Общие сведения
- Варианты заданий
- Средство «Итоги»
- 3.3.1. Общие сведения
- Сводные таблицы
- 3.5. Функции для работы с базами данных
- 3.6. Консолидация данных
- 3.6.2. Варианты заданий
- 3.7. Контрольная работа по теме «Базы данных в Excel»
- 3.7.1. Указания
- 2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.
- 3.7.2. Варианты заданий
- Вариант 12
- Вариант 13
- Вариант 14
- Вариант 15
- IV. Макросы в ms Excel
- 4.1. Макросы для автоматизации работ
- 4.1.1. Пример
- 4.2. Вычислительные макросы
- 4.2.1. Пример 1. Расчет точки безубыточности
- 4.2.2. Пример 2. Моделирование процесса налогообложения [8]
- 4.3. Использование макросов для создания интерфейса
- V. Технология создания информационной системы средствами ms Excel
- 5.1. Постановка задачи
- 5.2. Требования к системе
- 5.3. Общая архитектура ис
- 5.3.1. Проектирование общей архитектуры
- 5.3.2. Создание общей архитектуры
- 5.3.2.1. Создание объектов ис
- 5.3.2.2. Организация переходов между объектами
- 5.3.2.3. Этапы создания интерфейса
- 5.4. Организация работы с базой данных
- 5.4.1. Заполнение таблиц модельными данными
- 5.4.2. Работа с данными
- 5.4.3. Сортировка
- 5.4.4. Поиск данных
- 5.4.5. Отчеты
- 5.4.5.1. Использование функций
- 5.4.5.2. Использование сводных таблиц
- 5.4.5.3. Использование элементов управления
- 5.4.5.5. Использование встроенных функций
- Функция должна быть в англоязычном варианте.
- 5.4.5.6. Варианты заданий
- 5.4.6. Расчет заработной платы
- 5.4.6.1. Постановка задачи
- 5.4.6.2. Интерфейс расчета заработной платы
- 5.4.6.3. Реализация расчетов
- VI. Экономические расчеты
- 6.1. Задачи на проценты
- 6.1.1. Общие сведения
- 6.1.2. Пример.
- 6.1.3. Варианты заданий
- 6.2. Финансовые функции
- 6.2.1. Общие сведения
- Бс(Ставка, Кпер, Плт, Пс, Тип).
- 6.2.3. Варианты заданий
- 6.3. Анализ межотраслевого баланса (модель Леонтьева)
- Основные понятия
- Математическая модель межотраслевого баланса
- 6.3.4. Варианты заданий
- 6.4. Задача об эквивалентности ставок [1]
- 6.4.1. Основные формулы
- 6.4.2. Постановка задачи
- 6.4.3. Варианты заданий
- 6.5. Методы анализа проектов (использование средства «Подбор параметра»)
- 6.5.1. Термины и определения
- 6.5.2. Примеры
- Варианты заданий
- 6.6. Выбор оптимального портфеля инвестиций
- 6.6.1. Основные определения
- 6.6.2. Пример
- 6.6.3. Варианты заданий
- 6.7. Вычисление налогов
- 6.7.1. Предварительные замечания
- 6.7.2. Пример.
- 6.7.3. Варианты заданий
- 6.8. Моделирование динамических процессов
- 6.8.1. Общие сведения
- 6.8.2. Порядок выполнения работы
- 6.8.3. Пример
- Результаты должны отражать основные закономерности процесса
- 6.8.4. Варианты заданий
- Производство в условиях постоянного спроса
- Конкуренция
- Сезонное производство
- Рыночные отношения
- Взаимопоставки
- Цены в условиях ограниченного объема выпуска
- Северный завоз
- Два пароходства
- Последовательные перевозки
- Антимонопольная система
- Конъюнктура
- Количество информации в Интернет
- Валютная интервенция
- Реклама
- VII. Статистические методы
- 7.1. Определение характеристик случайных величин
- 7.1.1. Содержание работы
- 7.1.2. Варианты заданий
- 7.2. Дисперсионный анализ
- 7.2.1. Общие сведения
- 7.2.1. Пример
- 7.2.3. Методы, применяемые после дисперсионного анализа
- 7.2.4. Варианты заданий
- 7.3. Регрессионный анализ
- 7.3.1.Общие сведения
- 7.3.2. Порядок выполнения работы
- 7.3.3. Проверка уравнения регрессии на адекватность
- 7.3.4. Использование уравнения для прогноза
- 7.4. Кластерный анализ
- 7.4.1. Общие положения.
- 7.4.2. Примеры
- 7.4.3. Формализация процесса кластеризации
- 7.4.4. Порядок выполнения работы
- 7.4.5. Задания
- 7.5. Анализ временных рядов
- 7.5.1. Общие сведения
- 7.5.2. Пример
- Литература
- Приложения
- Технология генерации модельных данных
- Приложение 2 Районы и города Чувашии в цифрах [9]
- Тексты макросов Текст макроса для кластерного анализа
- Текст макроса для решения систем дифференциальных уравнений
- Текст макроса для генерации временного ряда
- Приложение 4 Транспорт и связь
- Статистические данные по регионам рф [5]