Назовите способы моделирования таблицы excel

Создание модели данных в Excel

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.

Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.

Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.

Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,

Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.

Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.

Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.

Начало работы

Во-первых, вам нужно получить некоторые данные.

В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.

В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.

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

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

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

Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).

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

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

Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.

Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.

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

Советы по уменьшению размера модели данных см. в Excel и Power Pivot.

Дополнительные сведения см. в учебнике Импорт данных в Excel и Создание модели данных.

Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.

Создание связей между таблицами

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

Перейдите на > Power Pivot .

На вкладке Главная выберите представление диаграммы.

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

Затем перетащите поле первичного ключа из одной таблицы в следующую. Ниже приведен пример представления диаграммы таблиц наших учащихся:

Мы создали следующие ссылки:

tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося

Другими словами, перетащите поле «ИД учащегося» из таблицы «Учащиеся» в поле «ИД учащегося» в таблице «Оценки».

tbl_Semesters | ИД семестра > tbl_Grades | Семестр

tbl_Classes | Номер > tbl_Grades | Номер класса

Имена полей не должны быть одинаковыми для создания связи, но они должны быть одного типа данных.

Соединители в представлении диаграммы имеют 1 с одной стороны, а «*» — на другой. Это означает, что между таблицами существует связь «один-к-многим», которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.

Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).

Создание и создание с помощью модели данных для создания сводная диаграмма

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

В Power Pivot, перейдите вуправление .

На вкладке Главная выберите вкладку «PivotTable».

Выберите место для размещения таблицы: новый или текущий.

Нажмите кнопкуОК, Excel и справа отобразит пустую с помощью области Список полей пустую.

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

Добавление имеющихся несвязанных данных в модель данных

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

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

Добавьте данные одним из следующих способов.

Щелкните Power Pivot > Добавить в модель данных.

Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне «Создание сводной таблицы».

Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в Power Pivot таблицу

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Моделирование в табличном процессоре MS Excel

Применение табличного процессора MS Excel в моделировании. Построение поверхностей в трехмерной системе координат, форматирование и копирование созданных таблиц. Построение диаграмм.

Содержимое разработки

Тема: Моделирование в табличном процессоре MS Excel

Цель занятия: применение табличного процессора MS Excel в моделировании. Построение поверхностей в трехмерной системе координат, форматирование и копирование созданных таблиц. Построение гистограмм.

ТСО: Компьютерный класс. Программное обеспечение (Windows XP), MS Office (Word, Excel).

Задание 1. Построить график поверхности функции , заданной в промежутке с шагом 0,5.

1. Запустить Excel.

2. Переименовать Лист7 → Поверхность.

3. Ввести данные и заполнить столбец и строку, как указано на рисунке.

4. Составить расчетную формулу для заполнения столбца (диапазона) В8:В24 (это =A8*$B$7).

5. Заполнить столбец методом автокопирования.

6. Скопировать исходную формулу и вставить в ячейку С8 и соответственно нужно изменить адрес ячейки $B$7 на $C$7 и получить расчетную формулу =A8*$C$7.

7. Повторить пункт 5.

8. Эти операции повторить до тех пор, пока не заполним таблицу.

9. Выделить диапазон В8:R24 и построить диаграмму «Поверхность».

Задание 2. Построить поверхность в изометрической проекции, которая задается формулой в промежутке с шагом 0,5.

Формулы расчета значений таблицы в Excel:

=$A$2+B3 – для вычисления значений в интервале, столбец В.

=$A$2+C2– для вычисления значений в интервале, строка 2.

=КОРЕНЬ (16—В3^2-$С$2^2) – для вычисления значений в интервале, столбец С.

Ход выполнения задания

1. В ячейку A2 ввести значение шага 0,5 (абсолютный адрес).

2. В ячейку B3 ввести начальное значение заданного интервала, число – 4.

3. В ячейку B4 ввести первую форму = $A$2+B3, для расчета относительных адресов и нажать Enter.

4. Щелкнуть по ячейке В4. Подвести курсор к нижнему правому углу выделенной ячейки, как только курсор примет вид крестика «+», нажать левую клавишу мыши, и, не отпуская, протянуть маркер заполнения вниз, пока не увидите число 4 в ячейке В19.

5. В ячейку С2 ввести начальное значение из заданного интервала, число – 4.

6. В ячейку D2, ввести расчетную формулу =$A$2+C2, нажать Enter.

7. Щелкнуть по ячейке D2, и протянуть маркер заполнения курсором мыши по диапазону D2:S2, выделив все ячейки.

8. Установить курсор в ячейку С3, ввести формулу =КОРЕНЬ (16—В3^2–$С$2^2), и нажать Enter.

9. Щелкнуть повторно по ячейке С3.

10. Подвести курсор мыши к нижнему правому углу выделенной ячейки и, как только курсор примет вид крестика «+», нажать левую клавишу мыши и, не отпуская, протянуть маркер заполнения вниз, до ячейки С19.

11. Аналогичными действиями, нужно вводить формулы для ячеек D3, E3, … , S3, заменив адреса относительных ячеек на абсолютные адреса.

12. Щелкнуть повторно по ячейке D3.

13. Подвести курсор мыши к нижнему правому углу выделенной ячейки и, как только курсор примет вид крестика «+», нажать левую клавишу мыши, и, не отпуская, протянуть маркер заполнения вниз до ячейки D19.

15. Щелкнуть повторно по ячейке E3.

16. Повторить пункт 10.

И соответственно ввести расчетные формулы в ячейках

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

Для более компактного размещения данных в Excel, нужно выполнить команду ФОРМАТ → Ячейки → Число → Числовой.

Задать число « Десятичных знаков» – 2.

И последняя операция – уменьшить размер столбцов.

Источник

Моделирование в электронной таблице Excel

Тема урока: «Моделирование в электронной таблице Excel»

закрепить знания, умения и навыки решения задач с помощью ЭТ,

расширить представление о практическом применении ЭТ для решения прикладных задач,

учиться делать выводы по результатам исследования;

развивать творческие способности при решении прикладных задач;

осознание ценности совместной работы,

формирование навыков индивидуальной и групповой работы,

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

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

Формы работы учащихся :

фронтальная работа при повторении материала,

самостоятельная работа на карточках,

работа в парах на персональном компьютере при решении задач.

Организация начала урока

Актуализация опорных знаний

Мы изучили тему «Информационное моделирование». Что такое модель?

Действительно ли модель отражает все реальные свойства реального объекта?

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

Как запустить программу?

Основным элементом ЭТ является?

Что такое “принцип относительной адресации”?

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

Как отменить действие относительной адресации?

Перечислите основные типы данных для работы в ЭТ?

(Текст, число, формула)

Каким символом начинается ввод формулы в ячейку? (=)

Как правильно ввести формулу? (адреса ячеек вводятся на английской раскладке клавиатуры)

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

Как осуществить перенос строк в ячейке? (формат – выравнивание – переносить по словам)

Как правильно сохранить выполненную работу?

Как создать диаграмму или график по исходным данным?

Как осуществить функцию автозаполнения ячеек? (протаскивание сверху вниз)

Как переключить на русский язык раскладку клавиатура и обратно? ( alt+shift)

III . Формирование умений и навыков

Сегодня вам предстоит моделировать некоторые процессы на компьютерах, для каждой группы участников будет выдан свой лист с заданиями, вам необходимо будет внимательно ознакомиться с текстом задания и в дальнейшем, выполнив его сформировать выводы по итогам выполненной работы. Задания будут носить дифференцированный характер, т.е. они распределены по трём уровням сложностей: на 1 уровне вам необходимо будет в готовую таблицу вставить данные и построить диаграмму и график, на втором уровне … на третьем … Вы не сможете выполнить задания 2 –го уровня не выполнив первого, поэтому вы должны двигаться постепенно, выполняя пошагово задания. Соответственно задания первого уровня — оценка «3» второго уровня оценка «4», третьего уровня -«5». Кроме того будет оцениваться ваша самостоятельность при выполнении данной работы. Не забывайте, что после каждого уровня вам необходимо сделать выводы и записать их в тетради.

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

Если вам понятно задание, можно приступать к его выполнению. Помните о правилах ТБ при работе с компьютером.

В течение всего урока вы будите выполнять эту работу.

Источник