Режим введення

Для введення інформації в задану комірку потрібно встановити на неї курсор або натиснути мишею на цій комірці. З введенням нового символу ви переходите в режим введення, при цьому в рядку стану відображується слово Ввод. При натисненні клавіші Enter з’явиться слово Готово. Для редагування інформації в комірці необхідно або натиснути клавішу F2 або двічі клацнути лівою кнопкою миші на цій комірці. Можна відновити вміст комірці не виходячи з режиму введення. Для цього потрібно натиснути Ctrl+Z. Копіювання даних здійснюється стандартним для середовища Windows способом. При введенні даних, при створенні таблиць часто буває необхідно вставити рядок або стовпець. Для цього необхідно: встановити маркер в будь яку комірку стовпця, перед яким потрібно вставити новий стовпець; виконати команду Вставка ► Столбец (аналогічно для рядка). Крім стовпців і рядків у таблицю можна вставити комірки.

Введення і редагування формул. Якщо в комірці записується формула, то вона починається зі знака дорівнює (=). У формулах адреси з відносними адресами модифікуються при копіюванні а з абсолютними ні. У формулах можна використовувати операції +,-, *, /. Крім того в будь-яке місце формули можна за допомогою кнопки Мастер функций вставити будь-яку з численних функцій Eхсel або за допомогою команди Вставка ►Функция.

Для внесення змін у формулу (редагування) необхідно натиснути мишею в рядку формул або натиснути клавішу F2. Якщо зміни вносяться безпосередньо у формулі, де вона записана, то потрібно двічі клацнути мишкою у тому місці. Формулу можна перенести, скопіювати або поширити на блок комірок. При копіюванні формули виникає необхідність керувати зміною адрес комірок або посилань. Для цього перед символами адреси комірки встановлюється символ $ Якщо в посиланні використовується символ $, то воно називається абсолютним, якщо немає $, то відносним.

Таблиця 5. Приклад використання відносних адрес у формулах

	А	В	С

Для того, щоб перемножити числа в комірках А і B необхідно в комірку С1 ввести формулу: =А1*В1 і натиснути клавішу Enter, потім встановити вказівник миші в правий нижній кут комірки С1 (щоб курсор перетворився в хрестик) і не відпускаючи ліву кнопку миші протягнути її до комірки С5.

Таблиця 6. Приклад використання абсолютних адрес у формулах

	А	В	С

Для того щоб перемножити всі числа в стовпці А на число в комірці В1 необхідно ввести формулу: = А1*$В$1 і натиснути клавішу Enter і потім виконати копіювання аналогічно попередньому прикладу.

Побудова та редагування графіків і діаграм

1. Виділяємо в таблиці дані, треба відкласти по осі Y.

2. Викликаємо майстра діаграм Вставка ►Диаграмма.

3. Обираємо тип діаграми та натискуємо кнопку Далее.

4. У вкладці Ряд у полі Подписи оси Х вводимо дані по осі Х з таблиці та натискуємо кнопку Далее.

5. У вкладці Ряд вводимо назву ряду даних та натискуємо кнопку Далее.

6. У вікні Мастер (шаг 3 из 4): параметры диаграммы вводимо назву діаграми, назви осей, якщо це необхідно, легенду, зазначити місце розташування діаграми у вікні майстра.

Побудувавши діаграму, можна редагувати її (добавляти і видаляти ряди даних, змінювати вид діаграми і т.д.)

4.2. Основні прийоми при роботі з таблицями

При роботі з таблицею можна використовувати різні прийоми:

· встановлювати або змінювати формат комірки (Формат ►Ячейки);

· створювати примітки (Вид ►Примечание);

· створювати списки і використовувати їх для автоматичного введення в комірку (Сервис ► Параметры ►Списки);

· використовувати автозаміну (Сервис ►Параметры автозамены, в діалоговому вікні Автозамена пишеться текст, йому привласнюється ім'я або абревіатура і при введенні абревіатури в комірку текст вставляється автоматично);

· виконувати сортування даних у таблиці використовуючи команду Данные ► Сортировка;

· виконувати автозаповнення використовуючи команду Правка ►Заполнить ► автозаполнение;

· виконувати фільтрацію даних як з використанням автофільтру, так і розширеного фільтру використовуючи команду Данные ► Фильтр;

· числову інформацію в комірці можна перетворити в текст за допомогою команди Формат ►Ячейки ►Текстовый.

Обчислення. При виконанні обчислень використовуються наступні можливості:

· виконання обчислень за допомогою простих операторів “+, -, *, / ", визначення кореня та ступеня;

· виконання обчислень за допомогою простих та складних формул з використанням вкладених функцій;

· автосумування чисел у рядку за допомогою відповідної піктограми S;

· копіювання формул в інші комірки як за допомогою команди “копіювати” так і з використанням маркера автозаповнення;

· обчислення з використанням в формулах абсолютних посилань ($);

· виконання статистичного регресивного аналізу даних, тобто одержання значення величин на основі відомих даних;

· оптимізація пошуку рішення (задача лінійного програмування);

· підбір параметра у функції для одержання необхідного результату.

Програмування. Програма Excel дає можливість вирішувати складні задачі з використанням мови програмування Visual Basic, а також створювати макроси.

Обмін даними. Числову, текстову інформацію, таблиці, діаграми можна копіювати як в Excel так і з табличного процесора в інші додатки. Обмін виконується як за допомогою команди Вставка так і Спеціальна вставка через меню Правка.

4.3. Спеціальні прийоми при роботі з електронними таблицями

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

· виділяється таблиця;

· виконується команда Правка ►Копировать;

· покажчик комірки установлюється в комірку, в якій буде лівий верхній кут транспонованої таблиці;

· виконується команда Правка ►Специальная вставка;

· в діалоговому вікні Специальная вставка ставиться прапорець Транспонировать, далі ОК.

Транспонування таблиці можна виконати також за допомогою функції ТРАНСП (функція «Ссылки и массивы») при цьому функцію вводять як масив даних одноразовим натисканням клавіш: Ctrl + Shift + Enter.

Обчислення питомої ваги. В електронних таблицях питома вага обчислюється з використанням абсолютного посилання (табл.7). Абсолютне посилання, це посилання на комірку, адреса якої не змінюється. Приклад обчислення питомої ваги

Таблица 7. Обчислення питомої ваги

	A	B	C	D
	Місяць	Кредити, млн. грн.	Питома вага, %	Формули у стовпчику С
	Січень		4,8	=B2/$B$8*100
	Лютий		9,5	=B3/$B$8*100
	Березень		14,3	=B4/$B$8*100
	Квітень		19,0	=B5/$B$8*100
	Травень		23,8	=B6/$B$8*100
	Червень		28,6	=B7/$B$8*100
	Усього			=СУММ(С2:С7)

Сортування даних. Щоб виконати сортування даних у таблиці необхідно виділити ці данні і активізувати команди Данные ► Сортировка. У діалоговому вікні Сортировка диапазона обрати заголовок стовпчика, по якому буде виконано сортування, далі встановити перемикач по возрастанию або по убыванию і натиснути ОК.

4.4. Функції табличного процесора Excel

У процесорі Excel більше 200 функцій. Якщо функція є аргументом іншої функції, то така функція має назву вкладена.

Є різні способи введення функцій:

· клацнути по кнопці піктографічного меню Вставка функції;

· активізувати команду Вставка►Функція;

· активізувати кнопку (f_x) в рядку формул;

· клавіші Shift+F3.

Внаслідок цих дій з’являється діалогове вікно Мастер функций, в якому функції зібрані по тематиці: 10 недавно использовавшихся; Полный алфавитный перечень; Финансовые; Дата и время; Математические; Статистические; Ссылки и массивы; Работа с базой данных; Текстовое; Логические; Проверка свойств и значений; Инженерные; Информационные; Мат. и тригонометрия. Якщо натиснути кнопку Справка, то з’явиться опис функції, синтаксис та приклад. Майстер функцій має два кроки і після вибору функції треба клацнути далі. В другому вікні майстра треба ввести необхідні значення та клацнути Готово. Якщо функції “вкладені ”, то необхідно скористатися списком функцій зліва від рядка формул.

4.4.1. Математичні, статистичні та логічні функції

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

Математичні функції. Розглянемо роботу з функцією SIN. При визові певної функції через майстра з’явиться палітра для введення аргументу даної функції (рис.5). У рядку Число треба ввести або конкретне число (наприклад 0,1), або адресу комірки, де знаходиться потрібне число. Зрозуміло, що при введенні адреси комірки, а не константи при обчисленнях не треба вводити таку ж саму функцію у кожний рядок, а достатньо буде скопіювати формулу в інші комірки стовпця, якщо це необхідно. Число для тригонометричних функцій вводиться в радіанах. Функцію також можна вводити безпосередньо в комірку починаючи зі знака =, при цьому саму функцію треба набрати латинськими літерами.

Рисунок 5. Палітра формули

Статистичні функції: СРЗНАЧ (функція повертає середнє арифметичне своїх аргументів, аргументи функції можуть бути числа, діапазони чисел, імена комірок з числами); СРОТКЛ (повертає абсолютне середнє відхилення точок даних від середнього, аргументами функції може бути числа, діапазони чисел, імена комірок з числами); ДИСП (оцінюється дисперсія вибірки, аргументами функції може бути числа, діапазони чисел, імена комірок з числами); МАКС (повертає максимальне значення до 30 чисел, логічні значення та текст ігноруються) для обчислених значень математичних функцій.

Аргументи для функцій вказуйте у вигляді діапазону комірок, наприклад: СРЗНАЧ(B4:B13).

Логічні функції. Функція ЕСЛИ використовується для того щоб вибрати якусь певну дію в залежності від умови.

Синтаксис: ЕСЛИ (Умова;дія 1 якщо умова істина;дія 2 якщо умова не є істиною). Умова, дія 1, дія 2 це є аргументи функції.

Умова це є логічний вираз. Дія може бути: умовою, виразом чи значенням. Умова приймає значення ИСТИНА або ЛОЖЬ. Приклад умови: A10 =100 - це означає, якщо значення в комірці A10 дорівнює 100, то вираз приймає значення ИСТИНА. У противному випадку – ЛОЖЬ і в залежності від цього виконується одна чи друга дія. Наприклад, записана функція ЕСЛИ в якійсь комірці і має вид: =ЕСЛИ(A2<=100;"В межах бюджету";"Поза бюджетом"). Це означає, якщо в комірці А2 число менше або дорівнює числу 100 (тобто ИСТИНА) тоді функція приймає значення «В межах бюджету». У противному випадку - «Поза бюджетом». Функція И необхідна для перевірки всіх аргументів на їх істину, якщо хоча б один аргумент не є істиною то і функція не є істиною. Функція И повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ. Синтаксис: И (логічне значення 1;логічне значення 2;...)

Логічне значення 1, логічне значення 2,... це від 1 до 30 умов, що перевіряються, які можуть мати значення або ЛОЖЬ, або ИСТИНА. Наприклад, = И(2+2=4;2+3=5), Результат - ИСТИНА.

Функція ИЛИ необхідна для перевірки хоча б одного аргументу на істину, і коли хоч один аргумент має значення істини то і функція істина.

Функція ИЛИ повертає значення ИСТИНА, якщо хоча б один з аргументів має значення ИСТИНА; повертає ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.

Синтаксис: ИЛИ (логічне значення 1;логічне значення 2;...)

Логічне значення 1, логічне значення 2,...- від 1 до 30 умов, що перевіряються, які можуть мати значення або ИСТИНА, або ЛОЖЬ. Наприклад, = ИЛИ(2+1=4;2+3=5), Результат - ИСТИНА.

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

Синтаксис: НЕ (логічне значення)

Логічне значення - величина або вираз, які можуть приймати два значення: ИСТИНА або ЛОЖЬ. Приклад, = НЕ(1+1=2), Результат - ЛОЖЬ.

У функцій ИСТИНА, ЛОЖЬ аргументів не мають й вони безпосередньо вводяться в комірки.

Контрольні питання до лекції 2

1. Які існують програмні засоби для роботи з текстовими документами?

2. Який принцип впроваджено в текстові процесори Word які працюють в ОС Windows?

3. Які технічні можливості має Microsoft Word 2003?

4. Які базові інформаційні технології обробки текстових документів впрова­джені в Microsoft Word?

5. Скільки міліметрів складає 1 пункт розміру шрифту?

6. Яку структуру має вікно програми Word?

7. Де у вікні програми Word 2003 знаходяться значки які вказують на ре­жим перегляду документа?

8. Яка інформація про документ відображається у рядку стану?

9. У якому режимі відображення документа є відступ від принципу WYSIWYG?

10. У якому режимі документ представляється на екрані точно так, як він буде ви­глядати при друку на папері?

11. Який режим найбільш зручний для роботи над планом документа?

12. Щоб вводити текст з заміщенням який треба включити режим?

13. У якому режимі курсор при введенні символів зрушується вправо?

14. Щоб примусово завершити рядок і почати новий абзац, яку дії потрібно виконати при роботі в процесорі Word?

15. Які команди при роботі в процесорі Word використовують для пере­носу тексту в буфер обміну?

16. Яку команду варто виконати для настроювання параметрів вікна Word?

17. Яку команду треба виконати, щоб встановити межі тексту на сторінці?

18. За допомогою якої команди можна виконати настроювання панелей ін­струментів і команд меню?

19. Навіщо потрібні панелі інструментів?

20. Які панелі інструментів вста­новлюються як правило за замовчанням?

21. Якими поняттями необхідно керуватися при роботі з текстовим процесо­ром Microsoft Word?

22. З яких елементів складається повне ім'я файлу документа?

23. В якому випадку документу надають розширення rtf?

24. Коли потрібно виконувати команду Файл ►Сохранить как та ко­манду Файл ►Сохранить при збереженні документа?

25. Яких спеціальних символів не повинні містити імена файлів?

26. Яким чином можна розділити вікно документа на частині?

27. Яку команду використовують для перегляду тексту перед друком?

28. Що розуміють під форматуванням тексту?

29. Через яку команду встановлюється формат друкованої сторінки?

30. Як створити новий розділ?

31. Коли потрібно робити новий розділ в документі?

32. Яка команда використовується для додаткового графічного оформ­лення друкованих сторінок?

33. Що таке колонтитул та як його створити?

34. Яку команду треба виконати для нумерації друкованих сторінок?

35. Яка команда служить для нумерації об'єктів в документі Word?

36. Що таке фрагмент та які операції над фрагментами документа можна ви­конувати?

37. Як створити автотекст та автозаміну в документі?

38. Які існують об'єкти в текстовому документі та як до них переходити?

39. Що таке закладка в текстовому документі та навіщо вона потрібна?

40. Як створити закладку?

41. Як робити обчислення за допомогою полів у текстовому документі?

42. Чи можливо робити обчислення в таблицях у текстовому документі, якщо так то як це зробити?

43. Яка послідовність дій необхідна для редагування формули?

44. Що таке гіперпосилання, навіщо воно потрібно та як його створити?

45. Як форматувати символи та абзаци в тексті?

46. Які існують інтервали та як їх встановлювати?

47. Коли потрібно створювати списки, які вони бувають та як їх створю­вати?

48. Що таке табуляція та як її задати?

49. Що таке стиль та як його створити?

50. Як створюється колонки тексту?

51. Чи можливо створювати Web – сторінки за допомогою рамок в тексто­вому процесорі Word?

52. Як виконати пошук фрагменту та заміну в документі?

53. Що мають на увазі коли говорять про логічне розширення тексту?

54. Навіщо потрібні: виноски, примітки рецензентів, перехресні посилання, змісти, предметні покажчики, списки ілюстрацій та як їх створювати?

55. Що потрібно зробити в тексті щоб автоматично створити зміст докуме­нта?

56. Що розуміють під основою створення документа?

57. Чи поєднує в собі шаблон текстових документів: стилі, макроси, панелі інструментів, елементи автотекста?

58. Які існують конвертори файлів Microsoft Word?

59. В якому вікні задаються параметри збереження документа?

60. Які таблиці можна створювати в текстовому процесорі Microsoft Word?

61. Які команди використовуються для роботи з таблицями?

62. Чи можна малювати таблиці та сортувати рядки з даними, якщо так то як це зробити?

63. Як робити обчислення в таблицях Microsoft Word 2003?

64. Які вбудовані функції використовують для обчислень у таблицях Microsoft Word?

65. Чи можливе використання електронних таблиць Microsoft Excel в доку­менті Microsoft Word для обчислень, якщо так то як це зробити?

66. Як зв’язати впроваджену таблицю Microsoft Excel в документ Microsoft Word з оригыналом?

67. Що таке діаграма в текстовому процесорі Word?

68. Які способи існують створення діаграм у тестовому процесорі Microsoft Word?

69. Як виконати настроювання зовнішнього вигляду діаграми?

70. Які існують елементи подання даних і оформлення діаграм?

71. Що таке малюнки та зображення у текстовому процесорі Microsoft Word?

72. Яка панель служить для роботи з векторними малюнками?

73. Які прийоми й засоби використовують при створенні й редагуванні векторних об'єктів?

74. Як об’єднати кілька малюнків в один об'єкт?

75. Чи можливо створювати написи у полі малюнка, якщо так то як це можна зробити?

76. Що таке кліпарти та як з ними працювати?

77. Які існують прийоми редагування зображення?

78. Які існують засоби для створення формул у текстовому процесорі Microsoft Word??

79. Як створюються формули в текстовому процесорі Word?

80. Як настроїти редактор формул?

81. Що таке складений документ?

82. Яка команда дає можливість виконати злиття основного звичайного документа Word і джерела?

83. Яке призначення полів у списку Добавить поле Word?

84. Як створити складений документ?

85. Що таке мультимедійна презентація?

86. Навіщо потрібна програма PowerPoint?

87. В якому місці вікна PowerPoint відображені кнопки режимів роботи PowerPoint?

88. Яке призначення режимів роботи PowerPoint таких як: звичайний, сортува­льника слайдів, показу слайдів?

89. Який режим роботи не представлений окремою кнопкою в лівому ниж­ньому куті вікна PowerPoint?

90. Навіщо потрібний режим сторінки заміток, як ним скористатись?

91. Які існують способи створення презентації?

92. В якому режимі при створенні презентації можлива найбільша творчість?

93. Який режим створення презентації передбачає вибір дизайну слайдів?

94. В якому режимі створення презентації викликається майстер за допомо­гою якого можна створити презентацію на основі запропонованого змі­сту?

95. Чи можна створити презентацію на основі існуючої, якщо так то створіть словесний алгоритм її створення?

96. В якому режимі можна вибрати макет слайду?

97. Як додати слайд до існуючої презентації в режимі Новая презентация та в інших режимах?

98. Які таблиці можливо використовувати у презентації?

99. Як перенести таблиці Word, Excel, Access в PowerPoint?

100. Які види діаграм можна використовувати в слайдах та як їх створити?

101. Чи можна вставляти малюнки, звуки, фільми, анімації у слайд, якщо так, то вкажіть як це зробити?

102. Звуковий супровід ста­виться до якогось конкретного містозаповнювача у слайді чи до всього слайду?

103. З якою метою використовують гіперпосилання у слайдах?

104. Чи можна гіперпосилання організувати у вигляді готових кнопок, якщо так то як це зробити?

105. Як створити підсумковий слайд та ефекти анімації?

106. Які види керування показу слайдів існують у PowerPoint?

107. Чи можна із всієї презентації вибрати тільки деякі слайди для їхньої демон­страції, якщо так то як це зробити?

108. Які існують способи задати інтервал часу показу слайдів?

109. Чи можна друкувати слайди?

110. Які існують способи показу презентації?

111. Як у неавтоматичному режимі перейти до наступного слайда?

112. Що треба зробити для зміни запланованого порядку показу слайду?

113. Під час пояснення матеріалу іноді потрібно щось домальовувати або підкреслити на слайді, як це зробити?

114. Які існують системи електронного перекладу?

115. Яка послідовність роботи з електронними перекладачами?

116. Що таке табличний процесор Excel? Яке призначення та технічні можливості процесора Excel?

117. Які елементи має екран електронної таблиці для спілкування з користувачем?

118. У якому діалоговому вікні відбувається на­строювання електронної таблиці та як його встановити?

119. Яке вікно табличного процесора використовується для введення даних?

120. Яке діалогове вікно використовується для настроювання панелей інструментів і меню команд?

121. Що таке книга, лист, стовпець, рядок, комірка електронної таблиці та навіщо вони потрібні?

122. Скільки стовпців та рядків на одному листу електронної таблиці?

123. Яка кількість комірок на одному листу?

124. Як формується адрес комірки та які бувають адреси?

125. Поясніть наступну запис: [Про­дукція.xls]Товари!D23.

126. Поясніть таку запис: ‘С:\Мои доку­менты\Excel \[Продукція.xls]Товари’!D3.

127. Які бувають адреси комірок у табличному процесорі Excel?

128. Які це адреса комірок: A3, $A3, A$3, $A$3?

129. Яка функціональна клавіша використовується для встановлення абсолютної адреси комірки?

130. Як позначається діапазон комірок у табличному процесорі Excel?

131. Які існують способи створення, відкриття, збереження, закриття, видалення файлу робочої книги?

132. Чи можливо виконувати операції з робочими листами, якщо так то які?

133. Що треба зробити з коміркою для введення або редагування даних?

134. Як вводяться та редагуються дані та які існують типи даних у табличному процесорі Excel?

135. Які існують способи копіювання, переміщення, вилучення комірок з даними?

136. Що розуміють під автоматизацією введення даних?

137. Як можна надрукувати документ Microsoft Excel та вказати необхідні параметри для друку?

138. Проаналізуйте способи форматування даних у табличному процесорі Excel.

139. Проаналізуйте числові формати у табличному процесорі.

140. Що таке формула, як вона водиться та які вона може мати компоненти?

141. Які операції з формулами можна виконувати у табличному процесорі Excel?

142. Що розуміють під редагування формул?

143. Як копіювати та переміщувати формули, зробіть аналіз цих операцій?

144. Що таке майстер функцій у табличному процесорі та як з ним працювати?

145. Яка різниця між введенням звичайних формул і формул з масивами?

146. Як виглядає формула з масивами?

147. Чи можуть містити зовнішні посилання формули у таблицях на комірки іншої робочої книги?

148. Яким чином можна включити у формулу вбудовану функцію?

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

150. Що уявляє собою іменований блок комірок?

151. Яка максимальна допустима глибина вкладень вбудованих функцій у формулі?

152. Які типи даних можуть бути параметрами функцій?

153. Яким чином можна вводити діапазон комірок у палітру функції?

154. Проаналізуйте категорії вбудованих функцій табличного процесора Excel.

155. Що таке константа та змінна у табличному процесорі Excel, які можуть використовуватися в якості параметрів функції?

156. Проаналізуйте призначення та використання основних логічних вбудованих функцій:ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ.

157. Які способи фільтрації записів списків існують в табличному процесорі Excel?

158. Яка команда використовується для відміни всіх умов фільтрації?

159. Складіть словесний алгоритм побудови таблиць.

160. Яке призначення вкладок діалогового вікна Формат ячеек, для форматування даних?

161. Що уявляє собою графік? Проаналізуйте можливості Excel для побудови діаграм.

162. Складіть словесний алгоритм побудови діаграм.

163. Які існують прийоми редагування діаграм, який з них найкращій?

164. Яке призначення команд для редагування діаграм: Формат оси, Формат названия оси, Формат заголовка диаграммы, Формат области построения, Формат рядов данных, Формат легенды.

165. Як можна транспонувати таблицю та розрахувати питому вагу?

Ре­ко­мен­до­ва­на лі­те­ра­ту­ра

1. Баженов В.А та ін. Інформатика. Комп’ютерна техніка. Комп’ютерні технології: Підручник. 2-ге вид /. В.А. Баженов, – К.: Каравела, 2007. – 639 с.

2. Пушкаря О. І. Інформатика. Комп’ютерна техніка. Комп’ютерні технології: Посібник/ За заг. ред. О. І. Пушкаря. - К.: Академія, 2001. - 696 с.

3. Симонович С. В. Информатика. Базовый курс: Підручник / С. В. Симонович; За заг. ред. С. В. Симоновича. - С.- Пб.: Питер, 2009. - 640 с. ил.

Лекція № 3

Тема лекції:

СПЕЦІАЛЬНІ ПРИЙОМИ РОБОТИ В ТАБЛИЧНОМУ ПРОЦЕСОРІ

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

План лекції:

1. Підбір параметрів. Пошук рішення. Оптимізація.

2. Статистичний аналіз даних. Обмін даними.

3. Фінансові функції.

1. Підбір параметрів. Пошук рішення. Оптимізація.

1.1. Підбір параметра

Це така технологія яка дозволяє знайти необхідне числове значення в одній комірці із списку значень якщо відомий результат який пов’язаний формулою із числовим списком. Розглянемо задачу яка дана на рис.1. Як визначити кредит за лютий якщо він невідомий але відома формула для розрахунку значення Усього.

Рисунок 1. Визначення кредиту за лютий місяць

Для рішення таких задач використовують технологію яка називається Подбор параметра, для того щоб скористатись такою технологією треба виконати команду Сервис ► Подбор параметра, при цьому потрібно щоб у комірці B8 була формула: СУММ(B2:B7). Після виконання команди з’явиться вікно Подбор параметра рис.2. В цьому вікні треба вказати адресу комірки з формулою та вказати її числове значення, крім того треба вказати комірку значення якої треба змінювати.

Рисунок 2. Вікно для введення даних для підбору параметра

Після натиску кнопки ОК одержимо інформацію про виконання підбору параметра (рис. 3).

Рисунок 3. Інформативне вікно, в якому вказано, що рішення знайдено

А в таблиці яка приведена на рис.1. в комірці B2 буде знайдено число 20 рис.4.

Рисунок 4. Таблиця в якій знайдено рішення

Для підбору параметра необхідно виконати наступні дії:

1. Зробити копію таблиці, з якою і працюємо.

2. Розташувати курсор в комірці з формулою, наприклад, де сума кредитів.

3. У пункті меню Сервис вибрати команду Подбор параметра.

4. У діалогову вікні, що з’явиться у рядку Установить в ячейке вказуємо абсолютну адресу комірки в якій буде відомий результат, а у рядок Значение вводимо необхідне число.

5. У рядку Изменяя значение ячейки вводимо посилання на комірку, від якої залежить результат обчислень.

6. Клацаємо по кнопці ОК.

В результаті програма зробить підбір необхідних значень.

1.2. Пошук рішення

Технологія пошуку рішення яка використовується для рішення задач оптимізації це підбір даних в одних комірках таблиці Excel на основі обмежень, накладених на інші взаємозв’язані комірки. При цьому повинна бути визначена цільова функція, яка пов’язана формулою з комірками в яких відбувається підбір параметрів. Задачу оптимізації пошуку рішення можна сформулювати у загальному виді у такий спосіб. Знайти змінні x₁,x₂, …, x_n які задовольняють системі нерівностей або рівнянь, які являються обмеженням для задачі оптимізації

j_i (x₁, x₂, x₃, …, x_n) ≥ (≤) b_i, i = 1, 2, …, m

і при цьому досягає максимуму чи мінімуму або якогось числового значення цільова функція, тобто Z = f (x₁,x₂, …, x_n) ® числове значення, (max, min).

Умови позитивності змінних, якщо вони є, теж входять в обмеження: x₁ > = 0, x₂ > = 0, x₃ > = 0, …, x _n > = 0.

1.3. Оптимізація

Оптимізацію рішення задачі розглянемо на прикладі.

Задача. Внутрішній податок на товари і послуги в першому кварталі 2009 року склав 2796 тисяч гривень (рис.5). В другому кварталі, не змінюючи загальної суми податку (2796 тисяч гривень), варто зменшити на 35-45 тисяч гривень акцизний збір на вітчизняні товари і збільшити на таку ж суму акцизний збір на імпортні товари.

Рисунок 5. Таблиця на перший квартал

Рішення задачі:

1. Завантажуємо табличний процесор Excel.

2. Будуємо таблицю «Внутрішній податок на товари і послуги у 2009 р.» для першого кварталу і виконуємо необхідні обчислення (розраховуємо суму в комірках Е4=СУММ(B4:D4), Е5=СУММ(B5:D5), Е7= СУММ(B7:D7).

3. Робимо копію таблиці, вказуємо 2 квартал та відповідні місяці і вводимо обмеження на другий квартал рис. 6 (наприклад, копію робимо починаючи з комірки A9).

4. Активізуємо команду Поиск решения з підменю Сервис.

5. У діалоговому вікні Поиск решения (рис. 7) для цільової функції встановлюємо комірку $Е$15.

6. Ставимо перемикач на значення та вводимо відповідне число цільової функції 2796.

7. Для змінних х₁, х₂, х₃, х₄, х₅, х₆ (змінні знаходяться в стовпцях квітень, травень, червень для вітчизняних і імпортних товарів) вказуємо діапазон $B$12:$D$13.

8. Клацаємо по кнопці Добавить (рис.7) й у діалоговому вікні Добавление ограничения вказуємо обмеження: $E$12 ≥300, $ E$12 ≤310; $E$13 ≥245, $ E$13 ≤255 відповідно до умови задачі (рис. 8).

9. Після того як внесені всі обмеження, необхідно клацнути по кнопці Выполнить. У результаті з’явиться вікно Результаты поиска решения (рис. 9.), за допомогою якого можна зберегти рішення. У діалоговому вікні Результаты поиска решения необхідно клацнути по кнопці ОК, в таблиці за другий квартал з'являться необхідні зміни, тобто задача по формуванню державного бюджету на другий квартал буде вирішена (рис. 10).

Рисунок 6. Таблиця для другого кварталу

Рисунок 7. Вікно пошуку рішення

Рисунок 8. Діалогове вікно Добавление ограничения (для першого обмеження)

Рисунок 9. Вікно для збереження знайденого рішення

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

2. Статистичний аналіз даних. Обмін даних

2.1. Статистичний аналіз даних

Статистичний аналіз даних це приблизне знаходження відповідних данихна основі відомих даних.

Для статистичного регресивного аналізу програма Excel має статистичні функції: ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ. Дані функції дозволяють знайти невідомі дані в майбутньому чи минулому на основі відомих даних.

Наприклад. Нехай потрібно зробити розрахунки згідно рис.11 використовуючи функції: ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ.

Рисунок 11. Таблиця випуску продукції (у.о.)

Функція ПРЕДСКАЗ обчислює або передбачає майбутнє значення y відповіднодо лінійної регресії по існуючим значенням x та y.

Значення, що передбачається - це у -значення, відповідне новому заданому x - значенню. Відомі значення - це x і у – значення. Рівняння для функції ПРЕДСКАЗ має вид y = a + bx

де:

;

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

Синтаксис: ПРЕДСКАЗ (x; відомі значення y; відомі значення x),

де x - це нова точка даних, для якої передбачається розрахунок функції ПРЕДСКАЗ;

відомі значення y - це залежний масив або інтервал даних;

відомі значення x - це незалежний масив або інтервал даних.

Замітки:

· Якщо x не є числом, то функція ПРЕДСКАЗ повертає значення помилки #ЗНАЧ!.

· Якщо відомі значення y і відомі значення x порожні або містять різну кількість точок даних, то функція ПРЕДСКАЗ повертає значення помилки #Н/Д.

· Якщо дисперсія аргументу відомі значення x рівна нулю, то функція ПРЕДСКАЗ повертає значення помилки #ДЕЛ/0!.

Для введення аргументів функції ПРЕДСКАЗ може використовуватись палітра формул рис. 12.

Рисунок 12. Палітра формул для функції ПРЕДСКАЗ

Функція РОСТ розраховує прогнозоване експоненціальне зростання на підставі наявних даних.

Функція РОСТ повертає значення у для послідовності нових значень x, що задаються за допомогою існуючих x і у - значень. Функція робочого листа РОСТ може застосовуватися також для апроксимації існуючих x і у -зна­чень експоненціальної кривої.

Синтаксис: РОСТ (відомі значення y;відомі значення x;нові значення x; конст),

де відомі значення y -це безліч значень у, які вже відомі в співвідношенні у = b*m^x., відомі значення x -це безліч відомих значень x, нові значення x -це декілька значень x або одне значення x для яких розраховується нове значення y тобто функція РОСТ.

Замітки:

· Відомі значення x - це необов'язкова безліч значень x, які вже відомі для співвідношення у = b*m^x.

· Якщо які-небудь числа в масиві відомі значення y рівні 0 або негативні, то функція РОСТ повертає значення помилки #ЧИСЛО!

· Якщо масив відомі значення y має один стовпець, то кожен стовпець масиву відомі значення x інтерпретується як окрема змінна, це також відноситься і до рядка.

· Якщо відомі значення x опущені, то передбачається, що це масив {1;2;3;...} такого ж розміру, як і відомі значення y.

· Якщо аргумент нові значення x опущений, то передбачається, що він співпадає з аргументом відомі значення x.

· Якщо конст має значення ИСТИНА або опущено, то b обчислюється звичайним способом.

· Якщо конст має значення ЛОЖЬ, то b вважається рівним 1, а значення m підбираються так, щоб у = m^x.

Функція ТЕНДЕНЦИЯ повертає прогнозоване значення відповідно до лінійного тренда. Апроксимує прямою лінією (по методу найменших квадратів) масиви відомі значення у і відомі значення x. Повертає значення у, відповідно до цієї прямої для заданого масиву нових значень x.

Тренд – згладжуюча лінія.

Синтаксис: ТЕНДЕНЦИЯ (відомі значення у;відомі значення x;нові значення x; конст),

де відомі значення y -це безліч значень у, які вже відомі в співвідношенні у = у = mx + b, відомі значення x -це безліч відомих значень x, нові значення x -це декілька значень x або одне значення x для яких розраховується нове значення y тобто функція ТЕНДЕНЦИЯ.

Замітки:

· Відомі значення x -це необов'язкова безліч значень x, які вже відомі для співвідношення у = mx + b.

· Якщо використовується більш за одну змінну, то відомі значення у повинні бути вектором.

· Якщо відомі значення x опущені, то передбачається, що це масив {1;2;3;...} такого ж розміру, як і відомі значення у.

· Якщо опущено обидва масиви відомі значення x і нові значення x, то передбачається, що це масив {1;2;3;...} такого ж розміру, що і відомі значення у.

· Якщо відомі значення у - це один стовпець, то відомі значення x і нові значення x повинні мати таку ж кількість стовпців.

· Якщо конст має значення ИСТИНА або опущено, то b обчислюється звичайним способом.

· Якщо конст має значення ЛОЖЬ, то b вважається рівним 0, і значення m підбираються так, щоб виконувалося співвідношення у = mx.

· Якщо масив відомі значення у має один стовпець, то кожен стовпець масиву відомі значення x інтерпретується як окрема змінна.

· Якщо масив відомі значення у має один рядок, то кожен рядок масиву відомі значення x інтерпретується як окрема змінна.

Таким чином розрахунки в таблиці які потрібно було зробити на рис.11. наведені на рис.13.

Рисунок 13. Таблиця з розрахованим випуском продукції (у.о.) в 4 кварталі з використанням функцій: ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ.

2.1. Обмін даними

Обмін даними між додатками виконується за допомогою принципу OLE (Object Linked Embedded - зв’язування та впровадження об’єктів), або DDE (динамічний зв’язок). Технологія динамічного обміну даними (DDE) є стара і небезпечна. У додатках які підтримують принцип OLE данні наприклад, фрагмент текста, діаграму, таблицю можна вставляти в документи як зв’язані об’єкти або впроваджені. Для впровадженого об’єкта відсутній зв’язок з початковим файлом. Зв’язаний об’єкт (тобто діаграма або таблиця у редакторі Word) пов’язаний з початковим файлом. Це означає, що при зміні об’єкта в початковому файлі відбуваються зміни і у зв’язаному файлі.

Зв’язування та впровадження об’єктів виконується за допомогою команд Правка ► Специальная вставка як у процесорі Word так і в табличному процесорі Excel. У процесорі Word при виконанні команди Правка ► Специальная вставка у вікні Специальная вставка встановлюється перемикач Связать для зв’язування об’єкта з початковим файлом, якщо треба просто впровадити об’єкт тоді ставиться перемикач Вставить. При активізації зв’язаного об’єкта у процесорі Word завантажується початковий файл.

Якщо об’єкт табличного процесора Excel впроваджуються із буфера пам’яті в документ Excel то з’являється вікно Специальная вставка іє можливість установити у вікні перемикачі як на рис. 14, тобто у полі Вставить – все а у полі Операция – нет і тоді об’єкт впроваджується як зв’язаний при натиску кнопки Вставить связь. Якщо вказується операція (сложить, вычесть, умножить, разделить….) то вона виконується з даними які є у буфері пам’яті та з тими що копіюються і ніякого зв’язування з початковим файлом не відбувається.

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

У Excel для імпорту даних у вигляді таблиць використовується команда Данные ►Импорт внешних данных ►Импортировать данные. Можна імпортувати також і файли текстового процесора які мають розширення htm, html, txt тобто файли які не форматовані або є структуровані.

Рисунок 14. Вікно спеціальна вставка

3. Фінансові функції

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

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

3.1. Функції для розрахунку поточної та майбутньої вартості грошей

Для розрахунку поточної вартості грошей використовується функція ПС.

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

Синтаксис функції: ПС(ставка;кпер;плт;бс;тип),

де ставка – відсоткова ставка за період, кпер - загальне число періодів виплат інвестицій, плт – періодичні вкладення (виплати) які не змінюються протягом всього періоду; бс – необхідне значення майбутньої вартості або залишку після останньої виплати, якщо аргумент не заданий то він дорівнює 0. Обов’язково треба вказувати параметр бс якщо не вказаний параметр плт. Тип – це 1 або 0 (на початок періоду - 1, на кінець 0).

Для визначення чистого поточного об’єму вкладу розглянемо задачу коли параметр бс =0.

Яку суму грошей необхідно вкласти в банк щоб одержувати кожного року по 2000 грн. під 12% річних протягом 6 років.

Для рішення задачі треба ввести функцію в якусь комірку, наприклад B2: =ПС(12%;6;-2000) та натисніть клавішу Enter і ви отримаєте 8222,8 грн. Можна також вводити дані через палітру формул.

Приклад коли параметр бс не дорівнює 0.

Фірмі буде потрібно 5000 тис. грн через 12 років. На сьогодні фірма має у своєму розпорядженні гроші і готова покласти їх на депозит єдиним внеском, аби через 12 років він досяг 5000 тис. грн. Визначити необхідну суму поточного внеску, якщо ставка відсотка на нього – 12 % на рік.

Розв’язання: =ПС(12%;12;;5000)

Відповідь: -1283,38 тис. грн.

Для розрахунку майбутньої вартості грошей використовується функція БС.

БС - повертає майбутню вартість інвестицій на основі періодичних постійних (рівних по величіні сум) платежів і постійній відсотковій ставці (вартість інвестицій при нарахуванні складних процентів).

Синтаксис функції: БС(стака;кпер;плт;пс;тип),

де ставка – відсоткова ставка за період, кпер - загальне число періодів виплат інвестицій, плт – платежі (інвестиція), які проводяться кожного періоду і не змінні протягом всього часу сплати;

пс - приведена вартість інвестиції, або загальна сума, яка на теперішній момент рівноцінна серії майбутніх виплат, якщо вона не вказана, то значення пс = 0, тип – це 1 або 0 (платежі на початок періоду - 1, на кінець 0).

Наприклад. Яка сума грошей буде на рахунку, якщо кожного місяця вкладати в банк по 200 грн. під 12% річних при умові, що на рахунку уже є 500 грн. протягом 10 місяців. =БС(12%/12;10;-200;-500;1) після введення формули та натиснення Enter Ви одержите 2665,68 грн.

3.2. Функції для розрахунку амортизації майна

Функція АПЛ повертає величину амортизації активу за один період експлуатації майна, розраховану лінійним методом.

Синтаксис: АПЛ (нач. стоимость; ост._стоимость; время_эксплуатации)

де нач._стоимость - початкова вартість активу.

ост._стоимость - вартість наприкінці періоду амортизації (іноді називається залишковою вартістю майна).

время_эксплуатации - кількість періодів, за які власність амортизується (іноді називається періодом амортизації).

Розрахунок функції АПЛ наведено в таблиці 1.

Таблиця 1. Розрахунок функції АПЛ

	A	B
	Дані	Опис
	30 000	Початкова вартість
	7 500	Залишкова вартість
		Час експлуатації (у роках)
	Формула	Опис (результат)
	=АПЛ(A2; A3; A4)	Амортизаційні відрахування для кожного року (2250)

Примітка. Результати округляються до двох знаків після коми для усіх функцій.

Функція АСЧ повертає величину амортизації активу за вказаний період, розраховану методом «суми (річних) чисел».

Синтаксис: АСЧ (нач._стоимость; ост. _ стоимость; время_эксплуатации;период), де

нач._стоимость - витрати на придбання активу.

ост._стоимост - вартість наприкінці періоду амортизації (іноді називається залишковою вартістю майна).

время_эксплуатации - кількість періодів, за які власність амортизується (іноді називається періодом амортизації).

Период - період (повинен, бути обмірюваний у тих же одиницях, що й час повної амортизації).

Розрахунок функції АСЧ наведено в таблиці 2.

Таблиця 2. Розрахунок функції АСЧ

	А	B
	Дані	Опис
	30 000	Початкова вартість
	7 500	Залишкова вартість
		Час експлуатації у роках
	Формула	Опис (результат)
	=АСЧ(A2;A3;A4;1)	Річна амортизація за перший рік (4 090,91)

Функція ДДОБ повертає значення амортизації активу за даний період, використовуючи метод подвійного зменшення залишку (подвійний відсоток із залишку) або інший явно зазначений метод.

Синтаксис: ДДОБ(нач._стоимость;ост._стоимость;время_ эксплуатации;период;коэффициент), де

нач._стоимость -витрати на придбання активу.

ост._стоимость -вартість наприкінці періоду амортизації (іноді називається залишковою вартістю майна).

время_эксплуатации - це кількість періодів, за які власність амортизується (іноді називається періодом амортизації).

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

коэффициент - процентна ставка залишку, що знижується. Якщо коефіцієнт опущений, то він покладається рівним 2 (метод подвоєного відсотка із залишку, що знижується). Всі п'ять аргументів повинні бути позитивними числами.

Розрахунок функції ДДОБ наведено в таблиці 3.

Таблиця 3. Розрахунок функції ДДОБ

	A	B
	Дані	Опис
		Початкова вартість
		Залишкова вартість
		Час експлуатації у роках
	Формула	Опис (результат)
	=ДДОБ(A2;A3;A4*365;1)	Амортизація за перший день. Microsoft Excel автоматично припускає, що коефіцієнт дорівнює 2 (1,32)
	=ДДОБ(A2;A3;A4*12;1;2)	Амортизація за перший місяць (40,00)
	=ДДОБ(A2;A3;A4;1;2)	Амортизація за перший рік (480,00)
	=ДДОБ(A2;A3;A4;2;1,5)	Амортизація за другий рік з використанням коефіцієнта 1,5 замість методу подвоєного відсотка із залишку, що знижується (306,00)

Функція ПУО повертає величину амортизації активу для будь-якого обраного періоду, у тому числі для часткових періодів, з використанням методу подвійного зменшення залишку або іншого зазначеного методу.

Синтаксис: ПУО (нач._стоимость; ост._стоимость; время_эксплуатации; нач._период; кон._период; коэффициент),

де нач._стоимость - витрати на придбання активу.

ост._стоимость - вартість наприкінці періоду амортизації (іноді називається залишковою вартістю майна).

время_эксплуатации - кількість періодів, за які власність амортизується (іноді називається періодом амортизації).

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

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

У амортизаційної функції ПУО початковий період - від 0 до 9, кінцевий період - от 1 до 10, коефіцієнт (k) - по умовчанню 2. Розрахунок функції ПУО наведено в таблиці 4.

Таблиця 4. Розрахунок функції ПУО

	А	B
	Дані	Опис
		Початкова вартість
		Залишкова вартість
		Час експлуатації у роках
	Формула	Опис (результат)
	=ПУО(A2; A3; A4*365; 0; 1)	Амортизація за перший день. Microsoft Excel автоматично припускає, що коефіцієнт дорівнює 2 (1,32)
	=ПУО(A2; A3; A4*12; 0; 1)	Амортизація за перший місяць (40,00)
	=ПУО(A2; A3; A4; 0; 1)	Амортизація за перший рік (480,00)
	=ПУО(A2; A3; A4*12; 6; 18)	Амортизація за період між 6-м і 18-м місяцями експлуатації (396,31)
	=ПУО(A2; A3; A4*12; 6; 18; 1,5)	Амортизація за період між шостим й вісімнадцятим місяцями експлуатації при використанні аргументу коефіцієнт рівного 1,5, замість методу подвійного зменшення залишку (311,81)
	=ПУО(A2; A3; A4; 0; 0,875; 1,5)	Амортизація за перший фінансовий рік володіння активом, причому податкові закони обмежують знецінювання 150 відсотків по методу зменшення залишку. Актив був придбаний у середині першого кварталу фінансового року (315,00). Якщо рік прийняти за 1 то на квартал доводитися 0,25 а на середину кварталу 0,125 (0,25*3 +0,125=0,875)

Функція ФУО повертає величину амортизації активу для заданого періоду, розраховану методом фіксованого зменшення залишку.

Синтаксис: ФУО (нач_стоимость; ост_стоимость; время _эксплуатации; период; месяцы), де

нач._стоимость - витрати на придбання активу.

ост._стоимость - вартість наприкінці періоду амортизації (іноді називається залишковою вартістю майна).

время_эксплуатации - це кількість періодів, за які власність амортизується (іноді називається періодом амортизації).

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

месяцы - це кількість місяців у першому році. Якщо аргумент «місяці» опущений, то передбачається, що він дорівнює 12.

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

Розрахунок функції ФУО наведено в таблиці 5.

Таблиця 5. Розрахунок функції ФУО