Арифметичні операції над масивами
|
|
Трохи теорії
Miсrosoft Excel володіє широким набором засобів та функцій, які дозволяють виконувати практичні роботи з економетрії. В даній роботі розглядаються приклади використання цих засобів та функцій.
Як в більшості табличних розрахунків в Excel, в ході виконання практичних робіт ми будемо використовувати такий засіб як автозаповнення.
Автозаповнення – це підхід, який дозволяє отримати ряд даних при введенні одного чи двох його перших членів. При цьому використовується маркер автозаповнення, який і дозволяє отримати інші члени ряду. Для цього потрібно:
зафіксувати ліву клавішу миші на маркері в правому нижньому куті рамки виділення та провести маркер до кінцевої комірки. Автозаповнення дозволяє прискорити введення серійних даних та виведення результатів, а також знизити рівень помилок введення.
Види автозаповнення:
1. Ряд числових даних з кроком 1 створюється заповненням першого члена ряду при утримуванні клавіші Ctrl.
2. Ряд числових даних з довільним кроком створюється заповненням діапазону, який містить перші два члени ряду.
3. Ряди днів тижня та місяців року створюються шляхом заповнення будь – якого елемента зі вказаних рядів з врахуванням національних настроювань Панелі управління Windows.
4. Ряди дат та часу за замовчуванням створюються з кроком відповідно 1 день, 1 година, але по аналогії до п. 2 можна заповнити з довільним кроком.
5. Ряд формул створюється заповненням формули з врахуванням типу посилок, які в ній присутні ( абсолютні, відновні, змішані ).
6. Користувальницький ряд – це текстова послідовність, яку користувач вводить в список: Сервис → Параметри ... → Спискиі потім використовує аналогічно певним рядам (див. п. 3).
Також, використовуючи команду меню Правка → Заполнить → Прогрессия користувач може виконувати розрахунки прогресії та деякі інші операції.
При вивченні взаємозв’язків між економічними показниками для їх характеристики використовують статистичні показники. Зокрема, характеристики варіації ознаки: середнє лінійне відхилення значення ознаки від середнього значення, дисперсію, середньоквадратичне відхилення. Обчислити їх значення можна за відомими формулами або використовуючи відповідні вбудовані функції.
Формули для розрахунку цих показників:
; 
; 
Функції, які дозволяють обчислити значення цих показників: СРОТКЛ, ДИСПР, СТАНДОТКЛОНП. Використання функцій можна розбити на такі етапи:
- виділити комірку, яка буде містити результат функції. В комірці, яка міститься зліва, попередньо можна записати заголовок, що представляє собою позначення показника 
; 
; 
;
- викликати Мастер функций, який знаходиться на панелі інструментів (див. рис. 1);
- в категорії Статистические ( Математические, Полный алфавытний перечень ) виберіть відповідну функцію;
- введіть масив аргументів;
- натисніть клавішу Enter або у вікні Мастера функций - OK.
Рис. 1
Щільність лінійного зв'язку оцінюється за допомогою лінійного коефіцієнта кореляціі(Пірсона) r:
,
який набуває значень у межах ±1, тому характеризує не лише щільність, а й напрямок зв'язку. Додатне значення свідчить про прямий зв'язок, а від'ємне — про зворотній. Відповідна функція Excel КОРРЕЛ або ПИРСОН. Її використання аналогічне до використання функцій для розрахунку середнього лінійного відхилення значення ознаки від середнього значення, дисперсії, середньоквадратичного відхилення. Відмінність полягає лише в тому, що аргументом функції є два масиви значень, зв’язок між якими оцінюється. Порядок введення масивів значення не має.
Найважливішими операціями, з якими прийдеться працювати в даному практикумі, є матричні.
Матриця – прямокутна таблиця елементів, розміщених в стовпчиках та стрічках. Для Excel матриця – будь – який прямокутний діапазон числових даних. Для виконання будь – якої операції з матрицями введення формули та її редагування необхідно закінчувати натискуванням комбінації клавіш:
Ctrl – Shift – Enter.
Знайти суму ти різницю двох масивів можна в тому випадку, якщо вини мають однакову розмірність, тобто кількість стовпчиків та стрічок цих масивів співпадають.
Хід роботи
1. Введіть в комірки А1:С11 значення, як це показано на схемі. Для заповнення комірок А2:А11 використати команду Автозаполнение: в комірці А1 ввести число 1, виділивши комірку А1, натиснути клавішу Ctrl і, використовуючи маркер автозаповнення, отримати інші члени ряду, тобто зафіксувати ліву клавішу миші на цьому маркері в правому нижньому куту рамки виділення та провести маркер до комірки А11. Значення змінних x та y в комірках В2:В11 та С2:С11 введіть з клавіатури. Використайте для цього клавіші клавіатури, розміщені справа. Командою Формат ячеек → Число встановіть формат Числовой з одним десятковим знаком.
2. В стрічці № 1 введіть відповідні заголовки. По мірі виконання роботи в даній стрічці вводьте потрібні заголовки стовпчиків розрахункових даних.
3. В комірці А12 запишіть заголовок Сума. В комірках В12 та С12 за допомогою автосуми підрахуйте суми чисел, розміщених в відповідних стовпчиках. Для цього виділіть комірку, яка міститиме суму – В12 або С12, на панелі інструментів виділіть знак суми та натисніть клавішу Enter, або виділіть масив чисел, суму яких потрібно знайти, та на панелі інструментів виділіть знак суми.
4. В комірках D2:D11 розрахуйте добутки ху. Для цього в комірці D2 введіть формулу = В2 * С2, натисніть клавішу Enter, виконайте автозаповнення до комірки D11. Для цього виділіть комірку D2, зафіксуйте ліву клавішу миші на маркері в правому нижньому куту рамки виділення , проведіть маркер до комірки D11.В комірці D12 знайдіть суму добутків ху.
5. Розрахуйте різницю х – у в стовпчику Е. В комірці Е1 введіть відповідний заголовок. Комірка Е2 міститиме різницю значень змінних, які містяться в комірках В2 та С2. В цій комірці введіть формулу = В2 – С2 і виконайте автозаповнення до комірки Е11. Для цього виділіть комірку Е2, зафіксуйте ліву клавішу миші на маркері в правому нижньому куту рамки виділення , проведіть маркер до комірки E11.
5. Виконайте ті ж розрахунки, розглядаючи дані стовпчики чисел як масиви і використовуючи операції над масивами. Для цього виділіть область F2:F11, введіть формулу = В2:В11 – С2:С11 і натисніть комбінацію клавіш Ctrl – Shift – Enter. Щоб ввести в формулі значення масивів В2:В11 та С2:С11 потрібно виділити ці масиви. = та знаки операцій вводяться з клавіатури. Порівняйте отримані значення.
6. В комірці А13 запишіть заголовок Середнє. Щоб знайти середні значення змінних х та у, використайте функцію СРЗНАЧ. Для цього виділіть комірку, яка буде містити результат функції ( В13 ), активізуйте Мастер функций, який знаходиться на панелі інструментів, в категорії Статистические ( Математические, Полный алфавытний перечень ) виберіть функцію СРЗНАЧ і введіть масив аргументів. Операцію повторіть для змінної у.
8. В комірках G2:G11 розрахуйте квадрати різниць ( х – у )2. Для того, щоб отримати заголовок ( х – у )2 , потрібно ввести з клавіатури вираз ( х – у ), натиснути праву клавішу миші, вибрати команду Формат ячеек → Вершний индекс і ввести показник степеня 2. При цьому курсор має знаходитись на відповідній комірці. В противному випадку, потрібно підвести курсор до відповідної комірки, двічі натиснути ліву клавішу мишу і повторити вище вказані команди. Для обчислення квадратів різниць введіть наступну формулу - = ( В2- С2 )^2. Команда “ ^” дозволяє підносити число до степеня з будь – яким показником. В нашому випадку це – 2. Вводиться дана команда комбінацією клавіш Shift – 6. В комірках G13 розрахувати суму квадратів різниць ( х – у )2.
9. Виберіть довільно комірки і розрахуйте суму добутків ху та суму квадратів різниць ( х – у )2 , використовуючи функції СУММПРОИЗВ та СУММКВРАЗН. Розрахуйте з допомогою функції СУММКВ суму квадратів змінної х – Σх2 . Результати розрахунків представте, як показано на схемі.
| A | B | C | D | E | F | G | H | I | J | K | L | |
| № | х | у | ху | (х-у) | (х-у) | (х-у)2 | (х-хсер.)2 | (y-усер.)2 | х2 | I x-xсер. I | I у-усер. I | |
| 30,1 | 52,1 | 1568,2 | -22,0 | -22,0 | 484,0 | 66,6 | 9,6 | 906,0 | 8,16 | 3,1 | ||
| 32,6 | 53,5 | 1744,1 | -20,9 | -20,9 | 436,8 | 32,0 | 2,9 | 1062,8 | 5,66 | 1,7 | ||
| 33,7 | 53,1 | 1789,5 | -19,4 | -19,4 | 376,4 | 20,8 | 4,4 | 1135,7 | 4,56 | 2,1 | ||
| 35,1 | 56,5 | 1983,2 | -21,4 | -21,4 | 458,0 | 10,0 | 1,7 | 1232,0 | 3,16 | 1,3 | ||
| 36,4 | 54,1 | 1969,2 | -17,7 | -17,7 | 313,3 | 3,5 | 1,2 | 1325,0 | 1,86 | 1,1 | ||
| 39,4 | 58,2 | 2293,1 | -18,8 | -18,8 | 353,4 | 1,3 | 9,0 | 1552,4 | 1,14 | |||
| 41,8 | 55,1 | 2303,2 | -13,3 | -13,3 | 176,9 | 12,5 | 0,0 | 1747,2 | 3,54 | 0,1 | ||
| 43,3 | 57,2 | 2476,8 | -13,9 | -13,9 | 193,2 | 25,4 | 4,0 | 1874,9 | 5,04 | |||
| 44,2 | 56,1 | 2479,6 | -11,9 | -11,9 | 141,6 | 35,3 | 0,8 | 1953,6 | 5,94 | 0,9 | ||
| 46,0 | 56,1 | 2580,6 | -10,1 | -10,1 | 102,0 | 59,9 | 0,8 | 2116,0 | 7,74 | 0,9 | ||
| Сума | 382,6 | 552,0 | 21187,4 | 3035,6 | 267,3 | 34,4 | 14905,6 | 46,8 | 16,2 | |||
| Середнє | 38,3 | 55,2 | ||||||||||
| Σ ху = | 21187,4 | lсер.x= | 4,7 | lсер.x= | 4,7 | lсер.у= | 1,6 | lсер.у= | 1,6 | |||
| Σ (х-у)2 = | 3035,6 | σ2x= | 26,7 | σ2x= | 26,7 | σ2у= | 3,4 | σ2у= | 3,4 | |||
| Σ х2 = | 14905,6 | σx= | 5,2 | σx= | 5,2 | σу= | 1,9 | σу= | 1,9 | |||
| r = | 0,7 | r = | 0,7 |
Схема 1. Розрахункова таблиця
10. В комірках Н2:Н11 розрахуйте квадрати різниць ( х – хсер. )2. Для цього в комірці Н2 введіть формулу = ( В2- $В$13 )^2 і проведіть автозаповнення. Оскільки дану формулу ви копіюватимете в комірки зверху вниз, то знак $ достатньо ввести між буквою та числом, які позначають посилання на комірку, яка містить середнє значення змінною х – В13. Якщо формула містить константи, вказуючи значення яких ми робимо посилання на ті чи інші комірки, то перед позначенням комірки та між буквою і числом, які її позначають, потрібно ввести знак $. Зробити це можна натисканням клавіші F4.
11. Використовуючи здобуті навички використання засобів Excel, обчислити дисперсії, середні лінійні відхилення, стандартні відхилення значень змінних х та у, коефіцієнт кореляції . Формули для розрахунку цих показників наводяться:
; 
; 
; 
.
Для обчислення абсолютного значення різниць та кореня квадратного використовуйте функції АBS та КОРЕНЬ. Функції можна використовувати таким чином: в комірці або в стрічці формул, виділивши попередньо комірку, яка міститиме результат, ввести знак =, назву функції та ввести аргументи, заключивши їх в дужки.
12. Перевірте проведені розрахунки, використовуючи функції СРОТКЛ, ДИСПР, СТАНДОТКЛОНП, КОРРЕЛ або ПИРСОН. При виклику функцій з використанням Мастер функций,вибравши потрібну, уточніть, який результат дозволяє отримати дана функція.
| A | B | C | D | E | F | G | H | I |
| А = | 4,0 | 12,0 | 5,0 | В = | 8,0 | 9,0 | 12,0 | ||
| 5,0 | 14,0 | 2,0 | 5,0 | 4,0 | 15,0 | ||||
| 2,0 | 13,0 | 8,0 | 2,0 | 3,0 | 9,0 | ||||
| А + В = | 12,0 | 21,0 | 17,0 | А - В = | -4,0 | 3,0 | -7,0 | ||
| 10,0 | 18,0 | 17,0 | 0,0 | 10,0 | -13,0 | ||||
| 4,0 | 16,0 | 17,0 | 0,0 | 10,0 | -1,0 | ||||
| А * В = | -48,0 | 416,0 | -374,0 | В-1 = | 0,1 | 0,4 | -0,7 | ||
| -40,0 | 380,0 | -321,0 | 0,1 | -0,4 | 0,5 | ||||
| -16,0 | 342,0 | -253,0 | -0,1 | 0,0 | 0,1 | ||||
| АТ = | d = | -123 | |||||||
| А * 5 = | |||||||||
Схема 2. Розрахункова таблиця
13. Введіть масив А та масив В, розмістивши їх в областях В18:D20 та G18:I20. Знайдіть суму та різницю даних матриць. Для цього виділіть область В22:D24, введіть формулу = В18:D20 + G18:I20, натисніть комбінацію клавіш Ctrl – Shift – Enter і область G22:I24, введіть формулу = В18:D20 - G18:I20, натисніть комбінацію клавіш Ctrl – Shift – Enter . Якщо Ви допустилися помилки при виконанні команд і отримали невірні результати не змінюйте масивів, а виконайте необхідні операції повторно.
14. Знайдіть добуток двох масивів, використавши функцію МУМНОЖ. Для цього виділіть область В26:D28, активізуйте вікно Мастер функций, виберіть в категорії Математичніфункцію МУМНОЖ, введіть аргументи, зберігаючи заданий порядок множників, та натисніть комбінацію клавіш Ctrl – Shift – Enter .
15. В області G26:I28 знайдіть масив обернений до масиву, розміщеного в області G18:I20, використовуючи функцію МОБР.
16. В області B30:D32 знайдіть масив транспонований до масиву, розміщеного в області G18:I20, використовуючи функцію ТРАНСП. Дану функцію можна знайти в категорії Ссылки и масивы.
17. В комірці G30 обчисліть визначник масиву чисел, розміщеного в області G18:I20, використовуючи функцію МОПРЕД.
18. В області G35:I37 знайдіть результат множення масиву А та числа k = 5. Для цього виділіть область і запишіть формулу = В18:D20 * 5. Формулу закінчіть комбінацією клавіш Ctrl – Shift – Enter .
19. Розв’яжіть систему рівнянь в матричній формі:
| A = | A -1= | -0,33 | 0,44 | -0,22 | |||
| 0,67 | -0,22 | 0,11 | |||||
| -0,33 | 0,11 | 0,44 | |||||
| B = | X = | ||||||
20. Розв’яжіть систему рівнянь методом Крамера.
| Δ = | -9 | ||||||||||
| Δx1 = | Δx2 = | Δx3 = | |||||||||
| Δx1 = | -36 | Δx2 = | -27 | Δx3 = | -45 | ||||||
| x1= | x2= | x3= |
Завдання для самостійної роботи
1. Виконати запропонований в лабораторній роботі перелік завдань для заданих даних ( варіант 1 – 24 ).
2. Розв’язати систему рівнянь в матричній формі та методом Крамера ( варіант 1 – 24 ).
Контрольні питання
1. Поясніть, як використовується в розрахунках автозаповнення. Яка область його застосування?
2. Перерахуйте відомі Вам види рядів автозаповнення.
3. Поясніть принцип використання Мастера функций.
4. Перерахуйте відомі Вам функції роботи з масивами.
5. Поясніть, як створюється ряд формул.
6. Перерахуйте особливості ведення та редагування формул в Excel , які містять масиви.
7. Як розв’язати систему лінійних рівнянь в матричній формі?
8. Поясніть суть методу Крамера, який використовується при розв’язанні системи лінійних рівнянь.
Трохи теорії
Miсrosoft Excel володіє широким набором засобів та функцій, які дозволяють виконувати практичні роботи з економетрії. В даній роботі розглядаються приклади використання цих засобів та функцій.
Як в більшості табличних розрахунків в Excel, в ході виконання практичних робіт ми будемо використовувати такий засіб як автозаповнення.
Автозаповнення – це підхід, який дозволяє отримати ряд даних при введенні одного чи двох його перших членів. При цьому використовується маркер автозаповнення, який і дозволяє отримати інші члени ряду. Для цього потрібно:
зафіксувати ліву клавішу миші на маркері в правому нижньому куті рамки виділення та провести маркер до кінцевої комірки. Автозаповнення дозволяє прискорити введення серійних даних та виведення результатів, а також знизити рівень помилок введення.
Види автозаповнення:
1. Ряд числових даних з кроком 1 створюється заповненням першого члена ряду при утримуванні клавіші Ctrl.
2. Ряд числових даних з довільним кроком створюється заповненням діапазону, який містить перші два члени ряду.
3. Ряди днів тижня та місяців року створюються шляхом заповнення будь – якого елемента зі вказаних рядів з врахуванням національних настроювань Панелі управління Windows.
4. Ряди дат та часу за замовчуванням створюються з кроком відповідно 1 день, 1 година, але по аналогії до п. 2 можна заповнити з довільним кроком.
5. Ряд формул створюється заповненням формули з врахуванням типу посилок, які в ній присутні ( абсолютні, відновні, змішані ).
6. Користувальницький ряд – це текстова послідовність, яку користувач вводить в список: Сервис → Параметри ... → Спискиі потім використовує аналогічно певним рядам (див. п. 3).
Також, використовуючи команду меню Правка → Заполнить → Прогрессия користувач може виконувати розрахунки прогресії та деякі інші операції.
При вивченні взаємозв’язків між економічними показниками для їх характеристики використовують статистичні показники. Зокрема, характеристики варіації ознаки: середнє лінійне відхилення значення ознаки від середнього значення, дисперсію, середньоквадратичне відхилення. Обчислити їх значення можна за відомими формулами або використовуючи відповідні вбудовані функції.
Формули для розрахунку цих показників:
; ;
Функції, які дозволяють обчислити значення цих показників: СРОТКЛ, ДИСПР, СТАНДОТКЛОНП. Використання функцій можна розбити на такі етапи:
- виділити комірку, яка буде містити результат функції. В комірці, яка міститься зліва, попередньо можна записати заголовок, що представляє собою позначення показника ; ; ;
- викликати Мастер функций, який знаходиться на панелі інструментів (див. рис. 1);
- в категорії Статистические ( Математические, Полный алфавытний перечень ) виберіть відповідну функцію;
- введіть масив аргументів;
- натисніть клавішу Enter або у вікні Мастера функций - OK.
Рис. 1
Щільність лінійного зв'язку оцінюється за допомогою лінійного коефіцієнта кореляціі(Пірсона) r:
,
який набуває значень у межах ±1, тому характеризує не лише щільність, а й напрямок зв'язку. Додатне значення свідчить про прямий зв'язок, а від'ємне — про зворотній. Відповідна функція Excel КОРРЕЛ або ПИРСОН. Її використання аналогічне до використання функцій для розрахунку середнього лінійного відхилення значення ознаки від середнього значення, дисперсії, середньоквадратичного відхилення. Відмінність полягає лише в тому, що аргументом функції є два масиви значень, зв’язок між якими оцінюється. Порядок введення масивів значення не має.
Найважливішими операціями, з якими прийдеться працювати в даному практикумі, є матричні.
Матриця – прямокутна таблиця елементів, розміщених в стовпчиках та стрічках. Для Excel матриця – будь – який прямокутний діапазон числових даних. Для виконання будь – якої операції з матрицями введення формули та її редагування необхідно закінчувати натискуванням комбінації клавіш:
Ctrl – Shift – Enter.
Арифметичні операції над масивами
Масиви в Excel можна множити на число, додавати, віднімати, множити один на другий.
|
© 2013 wikipage.com.ua - Дякуємо за посилання на wikipage.com.ua | Контакти |