2. Редагування в електронній таблиці

У процесі створення ЕТ і введення даних постійно виникає потреба вносити в ЕТ ті чи інші виправлення і зміни. Програма Ехсеlмає зручні засоби для виконання цих операцій.

Редагування активної клітинки. Для внесення змін у вміст одної клітинки треба на цю клітинку встановити табличний курсор (зробити її активною). Вміст активної клітинки автоматично переноситься урядок формули, де його можна редагувати аналогічно тексту у редакторіMSWord. Клацаємо мишею на потрібній позиції у рядку формули, з’являється курсор вставки і проводимо необхідні виправлення. Щоб виправлений текст перемістився у активну клітинку треба натиснути клавішу введенняEnter.

Для очищення активної клітинки достатньо натиснути клавішу Delete.

Очищення, вставка, вилучення елементів ЕТ. Для виконання таких дій над елементами ЕТ спочатку треба їх виділити.

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

Очистити виділений діапазон клітинок можна командою Правка - Очистить. При цьому на екрані з’являється підменю, яке включає такі пункти:Все– вилучає формати та значення клітинок; Формати– вилучає тільки формати клітинок; Содержимое – вилучає значення клітинок. При очищенні клітинки діапазону залишаються на своєму місці в таблиці, міняється тільки їх вміст.

Для вставки у таблицю рядка треба поставити табличний курсор на будь-яку клітинку рядка, перед яким треба вставити новий рядок, і подати команду Вставка - Строки. Аналогічно виконується вставка стовпця по командіВставка - Столбцы.

Команди Правка - Удалить листі Вставка - Листдозволяють вилучати або вставляти листи у книгу. По замовченню Excel створює книгу, яка має 3 листа. Якщо потрібно більше, то листи можна додати у книгу за допомогою командиВставка/Лист.

3. Обчислення в електронній таблиці

Формули. Обчислення в ЕТ здійснюється за допомогою формул. Формула – це сукупність операндів, з’єднаних між собою знаками операцій і круглих дужок. Операндом може бути число, текст, логічне значення, адреса клітинки, функція. У формулах розрізняються арифметичні операції і операції відношень.Excelдопускає: арифметичні операції+- додавання,-- віднімання,*- множення,/- ділення,^- піднесення до степеня; операції відношень > - більше, < - менше,=- дорівнює,<=- менше або дорівнює,>=- більше або дорівнює,<>- не дорівнює.

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

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

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

Більшість помилок у формулах з математичними операторами відбувається із-за порушення порядку арифметичних дій. В програмі ЕХСЕL встановленій такий порядок виконання арифметичних операцій:

1. піднесення до степеня і дії які виконуються в дужках,

2. множення, ділення,

3. додавання, віднімання.

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

Формули масивів. Такий тип формул дозволяють обчислити не одне число, а масив чисел, які записуються у діапазон (масив) клітинок. Спочатку треба виділити діапазон клітинок, в який буде записуватися результат обчислень. Далі пишемо потрібну формулу. Введення формули закінчується натискуванням одночасно трьох клавіш Ctrl, Shift i Enter. Введена формула береться при цьому у фігурні дужки. Введення звичайної формули (не формули масиву) закінчується лише натискуванням клавішіEnter.

У наведеній на рис. 3.2.2електронній таблиці для обчислення вартості можна використати формулу масиву. Треба виділити діапазон клітинок F6:F11, далі записати формулу =D6:D11*E6:E11 і натиснути клавішіCtrl + Shift + Enter.

Є багато функцій (див. таблицю 4.1), які обробляють масиви, і формули з їх використанням треба вводити як формули масиву. Наприклад, всі функції для обчислення з матрицями потребують використання формули масиву.

Формула масиву вводиться не в одну клітинку, а у виділений діапазон (масив) клітинок. Окрема клітинка із цього масиву не має формули, по якою обчислюється її вміст, тому не можна змінити вміст окремої клітинки. При спробі змінити вміст однієї клітинки Excelвидає повідомлення“Нельзя изменять часть массива”. Треба заново виділити весь діапазон, а потім внести зміни у формулу масиву.

Функції.Excelмістить більше ніж 400 вбудованих функцій. Функція маєім’яісписокаргументів. Список аргументів записується безпосередньо за іменем функції і обов’язково береться у круглі дужки. Якщо аргументів декілька, то між ними ставиться (;). Аргументами можуть бути числові та текстові константи, клітинки, діапазони клітинок.

Ввести функцію у формулу можна вручну або з використанням майстра функцій. Краще використовувати майстер функцій. Для роботи з майстром функцій слід натиснути кнопку Мастер функцийна панелі інструментівСтандартнаяабо виконати командуВставка - Функция. При цьому відкривається діалогове вікноМастер функций – шаг 1 из 2 (рис. 4.4), в якому можна вибрати категорію функцій. При виборі категорії в полеВыберите функциювиводиться список функцій даної категорії. У цьому полі можна вибрати потрібну функцію.

Рис. 4.2.4 - Вибір функції

Після вибору функції слід натиснути кнопку ОК, у результаті чого відкривається вікно діалогуМастер функций – шаг 2 из 2 (рис. 4.2.5), в якому треба вказати аргументи функції.

Рис. 4.2.5 - Вікно для введення аргументів функції

Аргументи функції у відповідні поля можна вводити вручну. Але доцільніше це робити шляхом виділення. Для цього треба клацнути по правому краю поля потрібного аргумента: на екрані від вікна діалогу залишається тільки це поле і стає доступною електронна таблиця. Далі виділяємо діапазон клітинок, які містять аргумент функції (виділення здійснюється пунктирною лінією). Виділений діапазон автоматично вставляється у поле аргумента. Після цього знову клацаємо по правому краю поля аргумента, на екрані відновлюється вікно діалогуМастер функций– шаг 2 из 2, але вже із введеним аргументом. Аналогічні операції треба повторити для кожного аргументу. Після натискування кнопки ОК функція вставляється у формулу.

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

Таблиця 4.2.1 - Перелік основних функцій

Опис функції	Категорія
1	2
ЕСЛИ(лог_выраж; знач_если_истина; знач_если_ложь) Результат: перше значення, якщо аргумент лог_выраж при обчисленні дає значення ИСТИНА, і друге значення, якщо ЛОЖЬ	Логические
КОРЕНЬ(число) Результат:квадратний корінь числа	Математические
МАКС(число1; число2; ...) Результат:найбільше значення в списку аргументів	Статистические
МИН(число1; число2; ...) Результат: найменше значення в списку аргументів	Статистические
МОБР(массив) Результат:обернена матриця квадратної матриці; треба вводити як формулу масиву	Математические
МОПРЕД(массив) Результат: визначник матриці	Математические
МУМНОЖ(массив1; массив2) Результат:добуток матриць; треба вводити як формулу масиву	Математические
ОКРУГЛВВЕРХ(число; количество_цифр) Результат:округлення числа до вказаної кількості десяткових розрядів	Математические

Продовження таблиці 4.2.1

1	2
ПРОИЗВЕД(число1; число2; ...) Результат:добуток аргументів	Математические
СРЗНАЧ(число1; число2; ...) Результат:середнє арифметичне значення аргументів	Статистические
СУММ(число1; число2; ...) Результат:сума аргументів	Математические
СУММЕСЛИ (диапазон; “критерий”; диапазон_суммирования) Результат:сумаклітинок, що задовольняють заданому критерію	Математические
СУММПРОИЗВ(массив1; массив2) Результат:сума добутків відповідних елементів масивів	Математические
СЧЕТ(значение1; значение2; ...) Результат:кількість чисел у списку аргументів	Статистические
СЧЕТЕСЛИ(диапазон; “критерий”) Результат:кількість клітинок діапазону, що задовольняють заданому критерію	Статистические
ТРАНСП(массив) Результат:транспонована матриця; треба вводити як формулу масиву	Ссылки имассивы

Далі наведені приклади використання функцій електронної таблиці.

Приклад 1. Функції СУММЕСЛИ, СЧЕТЕСЛИ. Особливості використання цих функцій розглянемо на прикладі електронної таблиці, представленої на рис. 4.2.6.

Рис. 4.6 – Розрахунки за функціями СУММЕСЛИ, СЧЕТЕСЛИ

Нехай, наприклад, треба розрахувати сумарну потребу в деталях 20Д305. Для цього спочатку активізують кнопку Вставка функции, у переліку знаходять функцію СУММЕСЛИ. Її аргумент Диапазон містить значення діапазону комірок з назвами деталей, серед яких відшукуються ті, що задовольняють умову, поставлену в аргументі Критерий. Цей аргумент може містити число, умову, текст, адресу комірки, що містить потрібну назву (наприклад, 45, >1, "20Д305", A3).

Аргумент Диапазон_суммирования містить діапазон тих клітинок, в якому відбувається підсумовування; при цьому обробляються тільки ті записи, значення яких задовольняють поставлену умову. Якщо Диапазон_суммирования пропущений, то обробляються комірки, адреси яких задано в аргументі Диапазон.

З урахуванням наведених пояснень для обчислення сумарної потреби в деталях типу 20Д305 у клітинку Е16 вводимо таку формулу

=СУММЕСЛИ(D3;D14;”20Д305”;E3:E14)

і отримуємо в результаті 265.

Функція СЧЕТЕСЛИ розраховує кількість заповнених комірок у діапазоні, які задовольняють поставлену умову. Якщо, наприклад, треба розрахувати, у скількох виробах використовується деталь 20Д305, то у клітинку E18 вводимо таку формулу

=СЧЕТЕСЛИ(D3:D14;"20Д305")

і отримуємо в результаті 3.

Приклад 2. Функція ЕСЛИ. Функція ЕСЛИ використовується для перевірки умови стосовно значень та формул і повертає одне розраховане значення, якщо задана умова після розрахунку дає значення ІСТИНА, й інші розраховані значення, якщо значення умови після розрахунку буде ХИБНІСТЬ. Аргументами функції можуть бути до семи вбудованих функцій в разі складних перевірок.

Нехай треба розрахувати значення функції такого вигляду:

для, наприклад, двох значень аргументу х = 2,1 і х = -1,3.

Спочатку в діапазон клітинок А2:А3 (рис.4.2.7) заносять значення х й активізують клітинку для розрахунку першого значення у (клітинка В2).

Далі активізують кнопку Вставка функции, знаходять функцію ЕСЛИ й активізують кнопку ОК.

Рис. 4.2.7 - Введення аргументів функції ЕСЛИ

В аргументі Лог_выражение (рис. 4.2.7) створюють умову для першого рівняння (х≤0,5), причому х в Excel — це адреса клітинки, що містить значення х. Умова матиме вигляд А2<=0,5.

Аргумент Значение_если_истина містить розрахунок першого рівняння, якщо умова аргументу Логическое выражение має значення ІСТИНА. Аргумент Значение_если_ложь містить розрахунок другого рівняння, якщо умова аргументу Логическое выражение має значення ХИБНІСТЬ. Після натискування кнопки ОК створена формула заноситься у клітинку В2 і за цією формулою здійснюється обчислення для першого значення х.

Рис. 4.2.8 - Розрахунок за функцією ЕСЛИ

Далі її копіюють для другого значення у, і в результаті електронна таблиця набуває вигляду, показаного на рис. 4.2.8.

Приклад 3. Обчислення з матрицами. Для роботи з матрицями використовують різні функції (табл. 4.2.1). Матриці можна перемножувати між собою, множити на вектор, транспонувати, створювати обернені матриці тощо.

Розрахунки з матрицями розглянемо на прикладі розв’язування системи лінійних рівнянь, заданої в матричної формі А×Х=С, деА – матриця системи,С– вектор вільних членів,Х– вектор розв’язку (корені) системи. Нехай

Якщо визначник матриці Ане дорівнює нулю, то розв’язок системи лінійних рівнянь А×Х=Сзнаходиться за формулоюХ=А^-1×С, деА^-1– обернена матрицяА.

Спочатку створюють матрицю, А, наприклад, у діапазоні клітинок А2:С4, а вектора С – у діапазоні клітинок Е2:Е4 (рис. 4.2.9).Після цього над елементами матриці А і вектора С можна виконувати дії, що потрібні для розв’язування системи рівнянь.

Обчислення визначника матриці. Для обчислення визначника матриці застосовується функція МОПРЕД(массив), де массив – діапазон клітинок, в який записана матриця. У прикладі, що розглядається, для обчислення визначника матриці Ау клітинку Е6 записана формула =МОПРЕД(А2:С4). Визначник не дорівнює нулю (рис. 4.2.9), таким чином, система має розв’язок.

Створення оберненої матриці. Треба створити обернену матрицю А^-1 з елементів матриці А. Такі обчислення виконуються за формулою масиву. Для цього виділяють діапазон комірок (А9:С11), де буде створюватися нова матриця, активізують кнопку Вставка функции, вибирають функцію МОБР, і заносять відповідні значення елементів матриці А, в результаті отримують формулу =МОБР(А2:С4). Після цього встановлюють курсор на рядок формул та натискують на клавіші Ctrl+Shift+Enter.

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

Для розв’язування системи рівнянь матрицю А^-1 треба помножити на вектор С. Результатом буде вектор розв’язку Х, для розміщення якого потрібні три клітинки, наприклад, Е9:Е11. Їх треба виділити, активізувати кнопку Вставка функции, вибрати функцію МУМНОЖ і занести відповідні значення діапазонів матриці А^-1 та вектора С (рис. 4.2.9).

Після цього встановлюють курсор на рядок формул, натискують на клавіші Ctrl+Shift+Enter й одержують формулу МУМНОЖ(А9:С11;Е2:Е4).

Для перевірки розв’язку треба матрицю системи А помножимо на знайдений векторХ. Отриманий вектор повинен збігатися з векторомС.

Функції, що застосовувалися при розв’язуванні системи лінійних рівнянь, та отриманий розв’язок представлені відповідно на рис. 4.2.9 і 4.2.10.

Рис. 4.2.9 - Функції, що застосовуються для розв’язання системи лінійних рівнянь

Рис. 4.10 - Розраховані значення коренів системи лінійних рівнянь

Імена клітинок і діапазонів.Формула стає більш зрозумілою, якщо клітинкам і діапазонам, які записані у формулі, надати змістовні імена. Так, наприклад, якщо діапазонамD6:D1,E6:E11,F6:F11 (див. рис. 4.2.2) надати відповідно іменаЦіна, Кількість, Вартість, то для обчисленняВартостіможна записати формулу =Ціна * Кількість.

Для надання клітинкам або діапазонам певних імен слід спочатку їх виділити, а потім виконати команду Вставка - Имя - Присвоить. На екрані з’явиться діалогове вікно Присвоение имени, в поле Имяякого можна набрати ім’я. Ім’я повинно починатися з літери, може включати будь-які літери і цифри, а також знак підкреслення , знак питання, крапку. Адреса виділеної клітинки або діапазону з’явиться в поліФормулацього вікна, За замовчуванням створене ім’я всій книзі. Якщо треба обмежити дію імені певним листом, то імені, яке набирається в поліИмя, повинно передувати ім’я листа і знак оклику, наприклад, лист1!Ім’я_1. Для вилучення імені слід розкрити списокИмядіалогового вікнаПрисвоить имя, виділити ім’я і натиснути кнопкуУдалить.

Імена клітинок і діапазонів містяться у списку, який розкривається у лівій частині рядка формул Поле имени, Для вставки імені у формулу слід вибрати його з цього списку.