РОБОТА З МАСИВАМИ ЗНАЧЕНЬ В РЕДАКТОРІ ЕЛЕКТРОНИХ ТАБЛИЦЬ MICROSOFT OFFICE EXCEL 2007

ЗМІСТ

Вступ    3

  1. Висновок    25

    Список використаної літератури:    26

 

 

Вступ

Табличний процесор Excel фірми Microsoft призначений для введення, зберігання, обчислення і виведення великих обсягів даних у вигляді, зручному для аналізу і сприйняття інформації. Усі дані зберігаються й обробляються у виглядіокремих або зв’язаних таблиць. Одна або кілька таблиць складають «робочу книгу». У цьому випадку таблиці називаються робочими аркушами цієї книги, аркуші можна видаляти, доповнювати або переміщати з однієї робочої книги в іншу. Фізично на диску зберігається вся книга у вигляді окремого файлу з розширенням «xlsx» (рис. 1).


Рис. 1. Редактор електронних таблиць

Можливості Excel дуже широкі (обробка тексту, управління базами даних). Програма настільки потужна, що в багатьох випадках перевершує спеціалізовані програми-редактори або програми баз даних.

За майже двадцятилітню історію табличних розрахунків із застосуванням персональних комп’ютерів вимоги користувачів до подібних програм істотно змінилися. Спочатку основний акцент у такій програмі, як, наприклад, VisiCalc, робився на обчислювальні функції. Сьогодні положення змінилося. Поряд з інженерними й бухгалтерськими розрахунками організація і графічне зображення даних набуває все більшого значення. Крім того, різноманіття функцій, пропоноване такою обчислювальною й графічною програмою, не повинне ускладнювати роботу користувача. Програми для Windows створюють для цього ідеальні передумови.

 

Поняття масиву значень в редакторі електронних таблиць. Microsoft Office Excel

Клітинка може бути порожньою або містити один з таких типів даних:

  1. необхідно виділити діапазон комірок, який буде містити результати, перш ніж вводити формулу;
  2. вміст окремої клітинки у формулі масиву змінити не можна. Щоб перевірити це правило, виділіть комірку діапазону у зразку книги та натисніть клавішу DELETE;
  3. формулу масиву можна перемістити або видалити тільки цілком, іншими словами – щоб зменшити формулу масиву, спочатку потрібно видалити існуючу формулу, а потім створити нову формулу з самого початку;
  4. у формулу масиву з кількома рядками не можна вставити пусті рядки або видалити рядки з неї.

Константи масиву

Константи масиву є компонентами формул масиву. Константи масиву створюються шляхом введення списку елементів з наступною вставкою фігурних дужок «(())» навколо списку, таким чином:

= {(1,2,3,4,5)}

Раніше наголошувалося на необхідності натискати клавіші «CTRL + SHIFT + ENTER» при створенні формул масиву. Оскільки константи масиву є складовою формул масиву, необхідно вручну вставити дужки навколо цих констант.Потім необхідно натиснути клавіші «CTRL + SHIFT + ENTER», щоб ввести формулу цілком.

Якщо елементи списку розділяються комами, то буде створений горизонтальний масив (рядок). Якщо елементи розділяються крапками з комою— вертикальний масив (стовпчик).Щоб створити двовимірний масив, слід розділити елементи рядків комами, а рядки — крапкою з комою.

Як і формули масиву, константи масиву можна використовувати з будь-якими вбудованими функціями Excel.

Розглянемо приклад простої операції над масивами, а саме множення масиву на число. Множення довільного масиву А1:В2 на число 5 реалізовується наступним чином: виділяється на робочому листі область такого ж розміру, що й масив-множник.

Тепер вводиться формула =А1:В2*5 (рис. 1.3.2)

Рис. 1.3.2. Множення масиву на число

Натискаються клавіші «CTRL» + «SHIFT» + «ENTER». Таким чином програма знає, що необхідно виконати операцію над масивом. При цьому Excel виведе заключну формулу в рядку формул в фігурні дужки:

При роботі з масивами формула діє в всіх комірках діапазону. Неможна змінювати окремі комірки в формулі. Аналогічно можна визначити:

  • Ділення масиву на число (рис. 1.3.3.);

    Рис. 1.3.3. Ділення масиву на число

  • Суму масивів (рис. 1.3.4.);

Рис. 1.3.4. Сума масивів

  • Різницю масивів (рис. 1.3.5.);

Рис. 1.3.5. Різниця масивів

  • Поелементне множення масивів (рис. 1.3.6.);

Рис 1.3.6. Поелементне множення масивів

  • Поелементне ділення масивів (рис. 1.3.7.);

    Рис.1.3.7. Поелементне ділення масивів

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

Рис 1.3.8. Масив, елементи якого залежать від деякої функції.

Вбудовані функції для роботи з матрицями

Для виконання табличних обчислень потрібні формули. Оскільки деякі формули і їхні комбінації зустрічаються дуже часто, то Excel пропонує більш 400 вбудованих формул, що називаються функціями.

Всі функції розділені по категоріях, щоб у них було простіше орієнтуватися. Умонтований «Конструктор функций» допомагає на всіх етапах роботи правильно застосовувати функції. Він дозволяє побудувати й обчислити більшість функцій за два кроки.

У програмі є упорядкований за алфавітом повний список усіх функцій, у котрому можна легко знайти функцію, якщо відомо її ім’я; у протилежному випадку варто робити пошук по категоріях.

Багато функцій відрізняються незначно, тому при пошуку по категоріях корисно скористатися стислими описами функцій, що пропонує «Конструктор функций». Функція оперує деякими даними, що називаються її аргументами. Аргумент функції може займати один осередок або розміщатися в цілій групі комірок. «Конструктор функций»допомогає у завданні будь-яких типів аргументів.

Робота з матрицями та їх перетворення

Матриця являє собою таблицю з числовими значеннями, яка складається з N–рядків і M– стовпців.

Матриці можна між собою додавати, перемножувати, множити на вектор, транспонувати, створювати обернені матриці.

Спочатку створюють матрицю А у діапазоні комірок А2:С4, наприклад

Після цього з елементами матриці можна виконувати такі дії:

Множення матриці на число

Якщо необхідно помножити дану матрицю на число 3, необхідно виділити область вільних комірок за розміром, що відповідає заданій матриці (наприклад, D2:F4). Далі у рядку формул вводять формулу й активізують клавіші «CTRL» + «SHIFT» + «ENTER». У виділеному діапазоні комірок з’являються значення початкових даних, помножені на 3 (рис. 3.1.).

Множення матриці на вектор

Для цього використовується функціяМУМНОЖ(Массив1; Масив2), де Массив1, Массив2 — це масиви, які перемножуються. Кількість стовпців аргументу Массив1має дорівнювати кількості рядків аргументу Массив2. Внаслідок множення матриць утворюється масив з такою самою кількістю рядків, що й масив 1, і з такою самою кількістю стовпців, що й масив 2. Множення матриць здійснюють за формулою:

де – елемент нового масиву;

і – номер рядка;

– номер стовпця;

– елемент масиву 2;

к – номер рядка масиву 1 або номер стовпця масиву 2;

п– розмір матриці.

При множенні матриці на вектор розрахована матриця має розмірність вектора.

Спочатку створюється вектор у діапазоні G2:G4, який повинен мати стільки рядків, скільки стовпців у матриці. Далі виділяється діапазон комірок, де буде створюватися нова матриця (А7:А9), активізуючи кнопку «Вставка функции», вибирається функція МУМНОЖ і вносяться відповідні значення діапазонів матриці А та вектора С (рис. 3.1.).

Після цього встановлюється курсор на рядок формул й активізуються клавіші «CTRL» + «SHIFT» + «ENTER» й одержується:

МУМНОЖ(А2:С4;G2:G4).

Створення обернених матриць

Такі матриці застосовуються для розв’язання систем рівнянь з кількома невідомими. Для цього активізують функцію МОБР, що виконує обчислення з точністю до 16-значних чисел (для округлення цих значень слід користуватися функцією ОКРУГЛ).

Наприклад, треба створити обернену матрицю з елементів матриці А. Для цього виділяють діапазон комірок (D7:F9), де буде створюватися нова матриця, активізують кнопку «Вставка функции», вибирають функцію МОБР і заносять відповідні значення елементів матриці А:

МОБР(А2:С4)

Після цього встановлюють курсор на рядок формул і натискають на клавіші «CTRL» + «SHIFT» + «ENTER».

Додавання матриць

Для додавання двох матриць вибираємопорожню комірку, і у пункті меню — «Вставка» →«Функція» → «СУММ»і натискаємо кнопку «ОK».

З’являється вікно з шаблоном для заповнення. В пустих полях вказуємо перший осередку матриць.Натискаємо кнопку «ОK» і отримуємо у вихідній клітинці результат. Потім за правий нижній кут розтягуємо вихідну комірку в розмір даних матриць і отримуємо суму двох матриць.

Транспонування матриць

Для транспонування матриці необхідно виділити масив і скопіювати його в буфер обміну. Потім увійти в пункт меню – «Правка» → «Специальная вставка»→ «Транспонировать», попередньо вказавши вільну комірку. Після натиснути кнопку «ОK». В результаті чого ми отримуємо транспоновану матрицю.

Аналогічно функції «МУМНОЖ» використовуються функції «МОПРЕД» (знаходження визначника матриці) і«МОБР» (знаходження зворотної матриці).

В Excel існують наступні спеціальні функції для роботи з матрицями:

МОБР (MINVERSE)    Обернена матриця

МОПРЕД (MDETERM)    Визначник матриці

МУМНОЖ (ММULT)    Матричне множення двох матриць

ТРАНСП (TRANSPOSE)    Транспонована матриця

 

Рис. 3.1. Розраховані значення функцій та функції, що застосовують для роботи з матрицями

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

Обрахуємо в якості прикладу систему лінійних рівнянь з двома невідомими, матриця коефіцієнтів якої записана в комірки F1:G2, а вільні члени – в комірки І1:І2

Рис. 3.2. Розв’язок системи лінійних рівнянь

Для розв’язання цієї задачі згадаємо, що розв’язок лінійної системи АХ=В,де А – матриця коефіцієнтів, В – стовпець вільних членів, Х – стовпець невідомих, має вигляд Х= В, де – матриця, зворотня по відношенню до А. Тому для вирішення нашої системи рівнянь виділимо під вектор розв’язку діапазон К1:К2 і введемо в нього формулу, як показано на малюнку. Розв’яжемо також систему лінійних рівнянь Х=В, де

,    .

Для розв’язку цієї системи введемо в діапазон комірок А1:В2 елементи матриці А, а в діапазон комірки D1:D2 – елементи стовпця вільних членів В. Виберемо діапазон F1:F2, куди помістимо елементи вектора розв’язку, і введемо наступну формулу:

Розглянемо приклад розв’язання квадратичної формули Z =АХ, де А – квадратна матриця, введена в діапазон D1:D2, а символ (т) означає операцію транспонування. Для розв’язання Zвведемо в комірку F1 формулу

 

 

 

 

 

Рис 3.3. Знаходження квадратичної форми

Хоч результатом цієї формули є число, потрібно незабути для її вводу нажати клавіші «CTRL» + « SHIFT» + « ENTER». Якщо цього не зробити, то в комірці F1 з’явиться повідомлення #ЗНАЧ!.

Розв’язаннясистемирівнянь методом Гаусса

Теоретичні відомості

В процесі вивчення багатьох питань економіки, природознавства, техніки тощо доводиться розв’язувати системи лінійних алгебраїчних рівнянь. Зокрема, до таких систем зводиться чисельне розв’язування лінійних диференціальних та інтегральних рівнянь. Важливе практичне значення таких систем зумовило розробку чималого арсеналу різних методів розв’язування систем лінійних рівнянь. В своїй абсолютній більшості вони передбачають існування і єдиність розв’язку і поділяються на точні та наближені методи. До точних належать метод Гаусса та його модифікації, правило Крамера, метод квадратних коренів тощо. До наближених – метод ітерації, метод Зейделя. Точні методи можна назвати точними лише умовно, бо початкові дані можуть бути наближеними числами і, крім цього, заокруглення в процесі проміжних обчислень приводяться до втрати точності кінцевого результату.

Перевагою методуГаусса є простота алгоритму і невелике число в порівнянні з правилом Крамера проміжних обчислень. Метод квадратного кореня годиться лише для симетричної головної матриці системи рівнянь і практично не використовується.

Метод Гаусса, або метод послідовного виключення невідомих, має декілька модифікацій. Розглянемо схему єдиного ділення, за якою систему розв’язують в два етапи. На першому етапі вихідну систему рівнянь зводять до рівносильної їй системи трикутної форми. Цей процес перетворень називають прямим ходом. На другому етапі, який називають зворотнім ходом, знаходять розв’язок системи лінійних рівнянь трикутної форми за допомогою підстановки відомих розв’язків. Оскільки перетворення системи є фактично перетвореннями коефіцієнтів при невідомих і вільних членів, то перетворення проводять над розширеною матрицею системи.

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

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

Спочатку цим сумам присвоюють одні і ті ж значення – суми елементів рядків розширеної матриці системи рівнянь. В ході перетворень над елементами контрольної суми виконують такі самі дії, що й над відповідними елементами рядка матриці, а в стовпець рядкових сум записують суми елементів новоутворених рядків розширеної матриці. Якщо відповідні рядкові і контрольні суми практично збігаються (можливі розбіжності із-за похибок арифметичних операцій), то обчислення проведено правильно. В протилежному випадку, потрібно шукати помилку в обчисленні того рядка, де суми не співпадають. Зауважимо, що похибки арифметичних операцій можуть бути великими, якщо ділення проводилось над числами близькими до нуля. В цьому випадку говорять, що має місце нестійкість алгоритму і таку систему лінійних рівнянь потрібно розв’язувати іншим методом.

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

Практичне завдання

Використовуючи метод Гаусса, розв’язати систему рівнянь:

 

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

Спочатку заповнимо таблицю елементами розширеної матриці. Оскільки вона має розмірність 4х5, то використаємо блок А1:Е4(рис. 4.2.1). Клітинку F1 заповнимо формулою СУММ(А1:Е1). Після введення цієї формули процесор автоматично запише в цю клітинку суму елементів першого рядка. Скопіюємо цю формулу в клітинки F2, F3, F4, G1, G2, G3, G4.В клітинки F2, F3, F4 запишеться сума відповідно другого, третього та четвертого рядків, а формули в стопчику G доведеться редагувати, бо наприклад, в клітинці G1 замість бажаної формули =СУММ(В1:Е1) стоятиме формула =СУММ(В1:F1). Можна відредагувати формулу лише в клітинці G1 і скопіювати після цього її в клітинки G2, G3, G4. При цьому числові значення у відповідних клітинках стовпців F та G співпадатимуть.

Запишемо тепер у клітинку А5 формулу =А1/А1 і скопіюємо її у блок В5:F5. В кожній клітинці цього блоку відредагуємо формулу так, щоб у знаменнику стояло А1. В клітинку G5 скопіюємо формулу із G1. Взагалі кажучи цю формулу бажано скопіювати у блок G5:G14, щоб більше не повертатися до формування стовпця рядкових сум. Числові значення в клітинках F1 та G1 повинні практично співпадати. Цим самим завершено процес по діленню першого рядка розширенної матриці на елемент (перший крок методу Гаусса).

Тепер нам потрібно зробити перетворення другого, третього та четвертого рядків матриці так, щоб одержати в першому стовпці під одиницею нуль. Тому в шостий рядок запишемо формули (тут Х приймає відповідні значення від А по F). В сьомий рядок (стовпці від А по F) запишемо формули (Х приймає значення від А по F). У восьмий рядок (стовпці від А по F) запишемо формули (Х приймає значення від А по F). Запишемо тепер у клітинку В9 формулу =В6/В6 і скопіюємо її у блок С9:F9. В кожній клітинці цього блоку відредагуємо формулу так, щоб у знаменнику стояло В6. Цим самим ми завершили ділення другого рядка розширенної матриці на елемент . Тут нічого не говориться про клітинку А9. Там повинен стояти нуль, а, оскільки при запуску електронної таблиці всі клітинки обнуляться, то немає потреби змінювати значення в клітинці А9.

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

Аналогічно у дванадцятий рядок запишемо формулу. Тепер нам залишилося зробити перетворення четвертого рядка матриці так, щоб одержати в третьому стовпці під одиницею нуль. Тому в тринадцятий рядок запишемо формулу. У чотирнадцятий рядок запишемо формулу =Х13/В13. Цим самим завершено прямий хід методу Гаусса.

Для знаходження коренів запишемо відповідно в клітинки від В15 по В18 текст «х1=», «х2=», «х3=», «х4=», а в клітинки від В15 по В18 – формули =Е14, , . Корені другої системи позначимо у1, у2, у3, у4 і запишемо в клітинки від D15 по D18, а клітинки від Е15 по Е18 – формули =F14,

В підсумку таблиця матиме вигляд – рис. 4.2.1.

Також можна організувати кінцеву перевірку, підставивши знайденні значення в систему рівнянь – рис. 4.2.2 і рис.4.2.3.

Рис. 4.2.1. Розв’язання системи лінійних рівнянь методом Гаусса


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис.4.2.2. Перевірка розв’язку системи лінійних рівнянь методом Гаусса

 


Рис. 4.2.3. Підстановка значень в лінійне рівняння

 

Висновки

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

Отже, у даній роботі викладені основні можливості та операціїтабличного процесора Excel з масивами значень;вбудованими функціями для роботи з матрицями; розв’язання системи рівнянь методом Гаусса. Застосування цього табличного процесора дозволяє легко оптимізувати і прискорити роботу з обробки інформації.Адже не дарма доведено, що подання інформації в табличному вигляді, а також у вигляді графіків, діаграм дозволяє більш наочно і краще представити інформацію.

Перспективні напрямки в розробці електронних таблиць основними фірмами-розробниками визначені по-різному. Фірма Microsoft приділяє особливу увагу вдосконаленню набору функціональних засобів Excel, і в цьому її пакет явно лідирує серед всіх електронних таблиць. Фірма Lotus основні зусилля сконцентрувала на розробці інструментів групової роботи.ПакетQuattroPro в результаті тестування отримав досить високі оцінки, але ні одна з особливостей пакета не викликала до себе підвищеної уваги. Найбільш привабливими виявилися лише можливості сортування даних.

 

Список використаної літератури

  1. Глушаков С.В. ,Персональний комп’ютер. Учебний курс.–Харків: 2000.–499с.
  2. Дибкова Л. М. Інформатика і комп’ютерна техніка: Навчальний посібник. — Київ, 2005р. 153-217с.
  3. Згуровський М.З., Коваленко І.І., Міхайленко В.М. Вступ до комп’ютерних інформаційних технологій: Навч.посіб. – К.: Вид-во Європ. університету (фінанси, інформ. системи, менеджм. і бізнес), 2000.- 265 с.
  4. Лук’янова В.В. Комп’ютерний аналіз даних: Посібник. – К.: Видавничий центр “Академія”, 2003. – 344с. (Альма-матер).
  5. Рудненко В.Д.,Макарчук О.М., Патланжоглу М.О.Практичний курс інформатики / За ред. Мадзігона В.М. – К.:Фенікс, 1997. –304 с.
  6. Бухвалов А.В. и др. Финансовыевычисления для профессионалов. –.СПб.: БХВ-Петербург, 2001.-320с.
  7. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах.-СПб.: БХВ–Петербург, 2001.– 816с.
  8. Евдокимов В.В. и др. Экономическаяинформатика. Учебник для вузов. Под ред. Д.э.н., проф. В.В.Евдокимова. – СПБ.: Питер, 1997. – 592с.
  9. Карлберг, Конрад. Бизнес-анализ с помощью Excel.: Пер с англ.– К.: Диалектика, 1997.- 448с.
  10. Линвингстон Б.,Секрети Windows 95.–К.:Комиздат,Диалектика,1996.–560 с.

Симонович С.В. и др. Информатика. Базовый курс/–СПБ.: Питер, 2000. – 640с.

ЗАВАНТАЖИТИ

Для скачування файлів необхідно або Зареєструватись

Курсова (580.3 KiB, Завантажень: 4)

завантаження...
WordPress: 23.1MB | MySQL:26 | 0,712sec