4. Функція трансп

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

Мал. 4.1

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

Це можна зробити за допомогою формули ТРАНСП. Синтаксис цієї функції дуже простий ТРАНСП(масив), де масив це діапазон даних, які необхідно перетворити у вертикальний або горизонтальний вигляд (транспонувати).

Для реалізації даного способу нам необхідно вибрати діапазон в якому автоматично будуть транспонуватися дані. У нашому випадку це C8: C13.

Мал. 4.2

Після цього на клавіатурі відразу набираєте формулу = ТРАНСП (С3: H3)

В результаті у вас має отримати наступне:

Мал. 4.3

Далі, набравши формулу необхідно натиснути одночасно (Ctrl + Shift + Enter) для застосування формули масиву. У результаті формула з двох сторін буде оточена фігурними дужками.

Важливо розуміти, що якщо ви просто вкажіть фігурні дужки, то функція ТРАНСП працювати не буде. Крім того масиви (діапазони) повинні бути однакової величини.

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

5. Розв’язання систем линійних алгебраічних рівнянь

Табличні формули або формули масиву - дуже потужний обчислювальний засіб Excel, що дозволяє працювати з блоками робочого листа як з окремими осередками. Табличні формули в якості результату повертають масив значень. Тому перед введенням такої формули необхідно:

• виділити діапазон осередків, куди будуть поміщені результати;

• набрати формулу;

• після закінчення введення натиснути комбінацію клавіш Ctrl+Shift+Enter

Формула вводиться в усі осередки виділеного інтервалу. При активізації будь-якого осередку з інтервалу, що містить формулу масиву, в рядку формул відображається введена формула, поміщена у фігурні дужки. Саме фігурні дужки є ознакою табличної формули. Для виділення усього блоку, що містить табличну формулу, необхідно виділити один з його осередків, після чого натиснути комбінацію клавіш Ctrl+/. Неможливо редагувати вміст тільки одного осередку з інтервалу з табличною формулою. Змінити можна тільки увесь блок цілком, для чого він і має бути заздалегідь виділений.

До найпростіших операцій з матрицями прийнято відносити наступні:

• додавання і віднімання матриць,

• множення і ділення матриці на число,

• перемножування матриць,

• транспонування,

• обчислення оберненої матриці.

Множення (поділ) матриці на число, додавання (віднімання) матриць в Excel реалізуються досить просто: за допомогою звичайних формул (поелементне додавання чи віднімання, множення або ділення на число), або з використанням табличних формул, як це описано нижче.

Складання матриць. Наприклад, нехай необхідно скласти дві матриці розміру 3х3.

Елементи першої матриці(9 елементів) розмістимо в інтервалі A1 : C3, другий, - в діапазоні E1 : G3. Під результат виділимо інтервал A5 : C7. Після чого, не знімаючи виділення, введемо формулу =A1 : C3+E1 : G3, натиснувши комбінацію клавіш Ctrl+Shift+Enter. У осередках інтервалу A5 : C7 відобразиться результат - сума відповідних елементів матриць, а в рядку формул ми побачимо {=A1: C3+E1 : G3}.

Множення матриці на число. Нехай замість складання нам потрібно помножити першу матрицю на число 2. Для цього переміщаємося всередину інтервалу A5 : C7, виділяємо його, натиснувши комбінацію Ctrl+/, вносимо у формулу виправлення =A1 : C3*2 і натискаємо Ctrl+Shift+Enter. У інтервалі A5 : C7 побачимо результат множення, а в рядку формул - табличну формулу {=A1: C3*2}.

Для інших матричних операцій в Excel передбачені функції:

МОПРЕД(матриця)	обчислення визначника матриці
МОБР(матриця)	обчислення зворотної матриці
МУМНОЖ(матрица1;матрица2)	добуток матриць
ТРАНСП(матриця)	транспонування матриці

Приклад. Длявирішеннясистеми:

представимоцюсистему в матричномувигляді:AX=B,деА – матрицякоэффіцієнтів системы рівнянь, Х – вектор невідомихтаВ – вектор правых частей.

У цьому випадку невідоміx₁,x₂, x₃иx₄рахуються за формулою:

,i=1, …, 4

Де ∆ -визначникматриціA, ∆_i-визначникматриці,що виходитьз матриціАшляхом заміниi-го стовпцявекторомb.

Решение СЛАУ можно найти по формулам Крамера:

Де det А = |А| -визначникматрицісистеми(гголовнийвизначник), det А_i= |A_i|

(i=1, 2, ...,n) -визначникиматриці, (допоміжнівизначники),котрі виходятьзАзаміноюi-го стовпцяна стовпецьвільнихчленівВ.Лінійнаалгебраічнасистема несумісна(немає розв’язків),якщоdet А = 0.

Мал. 5.1

Для реалізаціїцьогометодувMSExcel:

1. введемоматрицюАтавектор b наробочийлист.

2. Зформируємочотиридопоміжніматриці, замінюючипослідовностовпці матриціA настовпецьвектора b (мал.. 5.1).

3. ЩобвичислитивизначникматриціA.Встановимокурсор в ячейкуH8тазверннемосядомайстрафункцій.УкатегориїМатематичніоберемо функціюМОПРЕД,що призначенудляобчисленнявизначникаматриці,таперейдемододругогокроку майстра функцій. Діалоговевікно,що з’являєтьсянадругомукроціміститьполе ввода Масив. Вцьомуполіуказуютьдиапазон матриці,визначникякоїобчислюють.У нашому випадку цеячейки B2:E5. (Мал.5.2)

Мал. 5.2

4. Для обчисленнядопоміжнихвизначниківвведем формулы:

H9=МОПРЕД(B7:E10),

H10=МОПРЕД(B12:E15),

H11=МОПРЕД(B17:E20),

H12=МОПРЕД(B22:E25).

У результаті в комірці H8 зберігається головний визначник, а в осередках H9: H12 - допоміжні.

5. Скористаймосяформулами Крамера і розділимо послідовно допоміжні визначники на головний. У осередок J9 введемо формулу = H11 / $ H $ 8. Потім скопіюємо її вміст в осередку J10, J11 і J12.

6. Зробимо перевірку рішення, для цього підставимо в нашу систему отримані значення:

7. У осередок L9 вводимо формулу = B2 * $ J $ 9 + C2 * $ J $ 10 + D2 * $ J $ 11 + E2 * $ J $ 12. Потім копіюємо її вміст в осередку L10, L11 і L12, отримані в результаті обчислень відповіді співпали з відповідями у вихідному прикладі - Система вирішена вірно

Матричний спосіб вирішення СЛАР. Цей спосіб досить простий. Обидві частини матричного рівності АХ = В помножимо зліва на обернену матрицю А-1:

A^-1AX = A^-1B.

Так якA^-1A = Е , где Е -одинична матриця (діагональна матриця, у якої по головній діагоналі розташовані одиниці), то рішення системи

X = A^-1В.

Тобто для вирішення системи необхідно знайти для матриці А зворотний A-1 і помножити її праворуч на вектор-стовпець В вільних членів.

Розглянемо рішення системи матричним способом.

1. ВведемоматрицюАуячейкиB28:E3.

2. Осередкидіапазона G28: G31 запвнюємозначеннямиправых частинрівняньсистемы:

Мал. 5.3

3. В осередку B33 щоб обчислити визначник матриці А, викликаємо Майстер функцій і в категорії Математичні клацнемо на імені функції МОПРЕД, яка повертає величину визначника матриці. Відкриється діалогове вікно Аргументи функції для функції МОПРЕД. У полі Масив вказуємо діапазон комірок G28: G31..

4. Виділяємо діапазон осередківE33: E36, призначений для відображення знайденого рішення.

5. Помістимо курсор в рядок формул і викличемо Майстер функцій. Вибираємо функцію МУМНОЖ, яка повертає результат множення матриць і заповнюємо діалогове вікно Аргументи функції наступним чином:

Мал. 5.4

6. Завершіть введення формули не традиційним клацанням на кнопці OK, а комбінацією клавіш Ctrl Shift Enter. Натискати їх слід послідовно і не відпускати 1-2 секунди, поки не зафіксується одночасне натискання всіх трьох клавіш. Фрагмент електронної таблиці, що реалізує рішення, наведений на малюнку.

Мал. 5.5