Програма обробки електронних таблиць Microsoft Excel

Размещено на http://www.allbest.ru/

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

КИЇВСЬКИЙ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ

БУДІВНИЦТВА І АРХІТЕКТУРИ

МЕТОДИЧНІ ВКАЗІВКИ

до виконання лабораторних робіт з дисципліни

“Офісні інформаційні технології”

Програма обробки електронних таблиць Microsoft Excel

Київ 2004

УДК 681

55К32.937.26-018.2

М54

Наведені короткі теоретичні відомості, завдання та методичні рекомендації до виконання лабораторних робіт з дисципліни “Офісні інформаційні технології”, метою яких є практичне засвоєння прийомів роботи з програмою обробки електронних таблиць Microsoft Excel.

 Зміст і порядок виконання лабораторних робіт

Дані методичні вказівки містять дев'ять лабораторних робіт, що охоплюють основні аспекти практичного використання програми обробки електронних таблиць Microsoft Excel.

Описи лабораторних робіт включають короткі теоретичні відомості по темі роботи і перелік питань, які треба вивчити самостійно, користуючись конспектом лекцій, спеціальною літературою, або довідковою системою самої програми Microsoft Excel.

До виконання лабораторної роботи допускаються студенти, що продемонстрували знання відповідей на контрольні запитання і способів виконання описаних в роботі завдань.

Виконання роботи зараховується студентам, які успішно справилися з усіма без винятку пунктами завдань і здатні дати докладні пояснення про спосіб їх виконання.

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

Лабораторна робота № 1 Відносні й абсолютні посилання.

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

Загальні положення

Файли Excel зберігаються на диску у вигляді робочих книг або шаблонів. Файли робочих книг мають розширення xls, файли шаблонів - xlt. Шаблони служать заготовками для створення нових робочих книг.

Після запуску Microsoft Excel на екрані відображається робоча книга, що містить три чисті робочі аркуші (рис. 1). Стовпці робочих аркушів позначаються латинськими літерами: A, B, C, ... , рядки - числами: 1, 2, 3, ... Комірки, розташовані на перетині відповідних рядків і стовпців, мають координати (адреси) виду: A5, C12, F31 і т.п.

Поточна комірка виділяється жирним контуром, а її координата або ім'я відображається над робочим аркушем зліва. Дещо лівіше координати розташований рядок формул. Саме у ньому редагується і відображається вміст поточної комірки. У самій же комірці може відображатися не весь текст, що фактично там міститься, або відображатися округлене значення тощо.

Для вибору поточної комірки, можна клацнути на ній мишкою, або скористатися клавішами переміщення курсору.

Введення інформації в комірки

Вмістом комірки може бути:

текст, наприклад: Роздрібна ціна, Товар, 300 у.о.;

число, наприклад: 253,3 або -78000 (у російській версії програми ціла частина числа від десяткового дробу відокремлюється комою. Крім самого числа комірка не повинна містити жодних сторонніх символів, інакше її вміст сприйматиметься як текст);

дата, наприклад: 30.09.99, 1 лют 2001, 9 травень, 12.98 (Якщо в даті відсутній день або рік, то Excel сам підставляє в них перше число місяця і поточний рік. При введенні дат день від місяця і місяць від року можна відокремлювати крапкою або косою лінією - / );

час, наприклад: 09:30, 15:55:12, 2:35,82 (Хвилини від годин і секунд відокремлюються двокрапкою. У форматі, що містить долі секунди, години відсутні);

дата і час, наприклад 30.09.99 15:25 (дата від часу відокремлюється одним пробілом);

формула, наприклад: =1,4*(M8-M9), ='Підсумки 2000 р.'! Н5 + Кошторис! Е28.

Формули завжди починаються знаком =. Перша з вищевказаних формул означає, що вміст даної комірки обчислюється як помножена на 1,4 різниця чисел, що знаходяться на цьому ж аркуші в комірках М8 і М9. Координати комірок , що згадуються у формулах , або діапазонів називаються посиланнями. Посилання на комірки, розташовані на інших робочих аркушах, включають назву аркуша, відділену знаком оклику. Наприклад, друга формула означає, що вміст даної комірки обчислюється як сума чисел в комірці Н5 на аркуші, що називається Підсумки 2000 р. і комірки Е28 на аркуші, що називається Кошторис (якщо ім'я аркуша не містить пробілів, то брати його в апострофи необов'язково).

При введенні формул, посилання можна вводити, клацнувши мишкою на потрібній комірці, або перемістивши на неї курсор за допомогою клавіатури. Наприклад, для введення першої формули можна набрати на клавіатурі =1,4*( , потім клацнути мишкою на комірці М8, потім увести мінус, клацнути мишкою на комірці М9 і ввести дужку, що закривається.

Введення інформації в комірку завершується натисканням клавіші Enter або Tab:

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

Tab викликає перехід до сусідньої правої комірки, що зручно при заповненні таблиці по рядках.

Виділення області

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

Переміщення, копіювання і заповнення

Для переміщення вмісту комірок необхідно підвести курсор до краю комірки або виділеної області так, щоб він набув форми стрілки. Потім потрібно натиснути клавішу мишки і відпустити її там, куди потрібно перемістити вміст комірок.

Якщо під час описаної вище операції утримувати натиснутою клавішу Ctrl, то вміст комірок буде не переноситися, а копіюватися.

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

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

За допомогою маркера заповнення можна виконувати і більш складні операції. Так, якщо, наприклад, виділити дві комірки з числами 1 і 2, то в кожній наступній комірці буде записуватися число, рівне вмісту попередньої плюс різниця між першими двома, тобто в наступних комірках ряд чисел буде продовжено значеннями 3, 4, 5 і т.д. Аналогічним чином, якщо записати в перші дві комірки числа 1 і 3, то цей ряд буде продовжено значеннями 5, 7 ,9... ; дати 24.02.99 і 26.02.99 будуть продовжені значеннями 28.02.99, 02.03.99... ; а записи розділ 1, розділ 2 - записами розділ 3, розділ 4 і т.д.

Особливості копіювання формул. Відносні, абсолютні і змішані посилання

Копіюючи формули, слід враховувати, що посилання вигляду А5, С12 є відносними. Тобто вони задають координати інших комірок відносно комірки, яка містить ці посилання. Якщо, наприклад, формула =А5*В5 записана в комірці С5, то це означає, що число в даній комірці буде обчислюватися як добуток двох сусідніх комірок, розташованих безпосередньо зліва від неї. Тому, якщо скопіювати цю формулу в комірку С6, то там вона набуде вигляду =А6*В6, а в комірці G1 та ж формула буде виглядати як=E1*F1.

У тих випадках, коли при копіюванні формул координата рядка або стовпця повинна залишатися незмінною, перед ними вказується символ $. Наприклад, посилання $У$7 називається абсолютним, оскільки при його копіюванні ні координата стовпця, ні координата рядка змінюватися не будуть і посилання усюди буде виглядати саме як $У$7.

У змішаних посиланнях зафіксована тільки одна з координат, наприклад, у посиланні $C12 стовпець С залишається фіксованим, а номер рядка при копіюванні змінюватиметься. В посиланні А$9 фіксованим залишається рядок 9, а позначення стовпця змінюватиметься.

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

Теми для самостійного вивчення

Присвоєння імен коміркам і діапазонам та використання імен.

Позначення діапазонів комірок.

Контрольні питання

Що таке робоча книга, робочий аркуш, рядок формул?

Які типи даних можуть бути введені в комірки, і по яких ознаках можна розрізнити тип даних?

Що таке відносні, абсолютні і змішані посилання та як вони вводяться?

Як виділити діапазон комірок і як позначається посилання на нього у формулах?

Як виділити декілька несуміжних діапазонів?

Як виділити всі комірки рядка, стовпця, робочого аркуша?

Як виділити тривимірний діапазон комірок (тобто діапазон, що містить комірки з одноіменними координатами на різних робочих аркушах)?

Як здійснюється переміщення і копіювання даних?

Що таке маркер заповнення і як він використовується?

Як виконується переміщення, копіювання, вилучення і перейменування робочих аркушів?

Завдання для виконання

Заповніть показаний на рис. 2 робочий аркуш наступними даними:

Ч1, Ч2, Ч3, Ч4 - довільні числа;

текст Т1 - довільні найменування приміщень;

формула Ф1 - добуток чисел Ч3 і Ч4 у тому ж рядку;

формула Ф2 - добуток Ф1 з того ж рядка на числа Ч1 і Ч2;

формула Ф3 - сума всіх значень Ф1 у стовпці;

формула Ф4 - сума всіх значень Ф2 у стовпці;

Таблиця повинна містити не менше 6 рядків даних і не мати порожніх стовпців.

Відрегулюйте ширину стовпців таблиці.

Введіть у перший рядок таблиці формулу Ф1 для розрахунку площі приміщення, а потім, за допомогою маркера заповнення, скопіюйте цю формулу у всі комірки стовпця Площа.

Введіть у другий рядок таблиці формулу Ф2 для розрахунку вартості оренди, звернувши увагу на необхідність використання абсолютних посилань на комірки, що містять числа Ч1 і Ч2.

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

У перші два рядки стовпця № п/п введіть цифри 1 і 2. Виділіть ці дві комірки, і за допомогою маркера заповнення одержіть в наступних рядках числа 3, 4, 5, ...

За допомогою кнопки Автосуммирование визначте загальну орендовану площу і вартість оренди.

Привласніть імена Вартість_метра і Курс_долара коміркам, що містять числа Ч1 і Ч2. У формулі Ф2 замініть посилання на координати цих комірок посиланнями на їхні імена.

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



Лабораторна робота № 2 Змішані посилання. Форматування даних

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

Працюючи з Excel, завжди слід пам'ятати, що форматування інформації служить не тільки для надання їй більш красивого і зручного для сприйняття вигляду. Все, що ми бачимо в комірці, визначається не тільки вмістом комірок, а й їхнім форматом. Наприклад, число в комірці може виглядати як 6, хоча його справжнє значення дорівнює 5,72. Просто в даному форматі не передбачене виведення дробової частини числа, і ми бачимо результат округлення.

Наведемо типовий приклад проблем з форматом, що часто збиває з толку новачків. Припустимо, що при введенні числа 4,5 ви помилково використали крапку замість коми: 4.5. Таке значення сприймається Excel як дата - четверте травня. Зрозумівши це, ви вводите правильне значення 4,5, але на ваше здивування тепер замість 4,5 у цій комірці ви бачите 04.01.1900 12:00:00 ...

Пояснюється це дуже просто. Справа в тім, що коли ви помилково ввели 4.5, в комірці автоматично встановився формат дати. Після виправлення на 4,5 значення в комірці змінилося, але формат дати залишився. Значення що спостерігається - 04.01.1900 12:00:00 - це і є 4,5, але тільки у форматі дати (4,5 доби, відраховуючи від 0 годин 1 січня 1900 р.). Таким чином, усе, що потрібно для виправлення помилки - це замінити в цій комірці формат дати загальним або числовим форматом.

Теми для самостійного вивчення

Форматування даних.

Умовне форматування.

Закріплення областей.

Контрольні питання

Як задається тип формату даних (загальний, числовий, грошовий, дата тощо)?

Як задати кількість знаків, що відображаються у дробовій частині числа?

Як відображається число і текст, якщо розмір комірки для них є недостатнім?

Якими способами можна регулювати розміри комірок?

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

Як можна задати вирівнювання й орієнтацію тексту?

Як задати спосіб обрамлення комірок (сітку)?

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

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

Що таке закріплення областей, як і для чого воно використовується?

Завдання для виконання

Заповніть робочий аркуш відповідно до рис. 3, обравши приблизно наступні значення: Ч1 = 2, Ч2=20%, Ч3=15%, Ч4=10%, Ч8 =5%, Ч9=20. Інші числові значення і найменування товарів (не менше 5 позицій) виберіть самі.

Надайте ім'я Авіатариф комірці, що містить число Ч1.

Розрахункові значення обчисліть по таких формулах: Ф1=Ч5*Ч7; Ф2=Ч6*Ч7; Ф3=Ф2*Ч1; Ф4=(Ф1+Ф3)*Ч8; Ф5=(Ф1+Ф3+Ф4)*20%; Ф6=Ч9*Ф5/Ф14; Ф7=Ч10*Ф2/Ф11; Ф8=(Ф1+Ф3+Ф4+Ф5+Ф6+Ф7)/ Ч7.

Ф9 обчислюється як значення Ф8 у поточному рядку, збільшене у (1+Ч2), (1+Ч3) або (1+Ч4) разом, у залежності від стовпця, в якому знаходиться формула Ф9. Формула Ф9 обов'язково повинна містити змішані посилання. Це дозволить увести формулу Ф9 тільки в одну комірку, а потім за допомогою маркера заповнення, скопіювати її в інші рядки і стовпці таблиці.

Формули Ф10 - Ф14 обчислюються як суми значень у відповідних стовпцях.

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

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

Виділіть комірки таблиці і виберіть у меню Формат опцію Автоформат, а потім один із варіантів форматування таблиці.

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

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

Збережіть файл з даним робочим аркушем на диску.

Відформатуйте аналогічним чином робочий аркуш, створений у лабораторній роботі №1.

Лабораторна робота № 3 Дані у форматі дати і часу. Використання функцій у формулах

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

У Excel усі дати, починаючи з 1 січня 1900 р., пронумеровані послідовністю натуральних чисел: 1.01.1900 = 1, 2.01.1900 = 2, 1.02.1900 = 32, 01.01.2000 = 36526, і т.д. Тому додавання до дати числа х означає збільшення дати на х днів, а різниця дат дає число днів між цими датами, наприклад: 29.08.97 + 4 = 2.09.97, а 2.01.98 - 30.12.97 = 3.

Якщо цілим числам відповідають дати, то дробам - час доби. Оскільки година - це 1/24 доби, то: 0,5 = 12:00, 0,25 = 6:00, 2,75 = 2.01.1900 18:00. Таким чином, дата і час розглядаються в Excel як звичайне число, тільки подане в специфічному форматі. Отже, арифметичні операції можуть виконуватись також з даними типу дати і часу, наприклад: 1:14:30 + 2:50:30 = 4:05:00. Не слід забувати, що якщо результат цього додавання перевести в числовий формат, то одержимо час, виражений у добах: 4:05:00 = 0,170138889 доби. Результат у годинах одержимо, примноживши останнє число на 24: 0,170138889*24 = 4,083333333 години.

Від'ємні значення не можуть бути подані у форматі дати і часу.

Теми для самостійного вивчення

Використання функцій у формулах.

Контрольні питання

Як у Excel представляються дати і час?

Які дії можна виконувати з даними у форматі дати і часу?

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

Які функції Excel ви знаєте?

Завдання для виконання

Відповідно до рис. 3 складіть таблицю для розрахунку дат "1000-денних ювілеїв":

в комірку Д1 введіть дату вашого народження;

в одну з комірок введіть формулу Ф1, що обчислюється як вміст комірки, розташованої над нею, плюс 1000;

за допомогою маркера заповнення скопіюйте формулу Ф1 у всі інші комірки таблиці;

встановіть в лівому стовпці числовий формат, а в правому - формат дати.

Відповідно до рис. 4 заповніть робочий аркуш розрахунку вартості прокату устаткування, де:

Т1 - довільні найменування (не менше 6 позицій), Ч1 - довільні числові значення, ДЧ1, ДЧ2 - довільні значення дати і часу Слідкуйте лише за тим, щоб момент повернення не був більш раннім, ніж момент видачі. .

Розрахункові формули: Ф1=(ДЧ2-ДЧ1)*24; Ф2=Ч1*Ф1;

Ф3 розраховується як округлене до цілого числа значення ДЧ2 плюс 10. Тут передбачається, що оплата за прокат повинна надійти не пізніше 10 днів після повернення устаткування. Округлення здійснюється для того, щоб при нарахуванні пені враховувати тільки дні, а не години прострочення платежу.

Дату фактичної оплати Д1 у деяких позиціях уведіть із перевищенням терміну, розрахованого в графі Сплатити до, в інших - без.

Для розрахунку пені ви повинні застосувати у формулі Ф4 функцію ЕСЛИ() так, щоб при своєчасній оплаті пеня дорівнювала нулю, а у випадку прострочення - Ф2*(Д1-Ф3)*1%.

Ф5=Ф2+Ф4, а Ф6, Ф7 і Ф8 розраховуються як суми значень у відповідних стовпцях.

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

Робітники листи обох таблиць відформатуйте і збережіть у файлі на диску.

Лабораторна робота № 4 Фільтрація даних

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

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

Excel має два засоби для фільтрації даних - автофільтр і розширений фільтр.

Автофільтр

Щоб встановити автофільтр, треба помістити курсор всередину таблиці і вибрати в меню Данные опції Фильтр і Автофильтр. В результаті біля заголовків стовпців з'являться кнопки списків, що розгортаються. В них можна вибирати наступні опції:

(Все) - знімає фільтр із даного стовпця і забезпечує вивід рядків із будь-якими значеннями в даному стовпці;

(Первые 10...) - дозволяє відфільтрувати задану кількість або заданий відсоток найбільших або найменших елементів даного стовпця;

(Условие...) дозволяє задати одну або дві умови фільтрації у формі рівності або нерівності Умови нерівності можна задавати не лише для чисел, а й для текстів і дат. Вважаються меншими ті дані, що стоять вище за алфавітним чи хронологічним порядком. Для фільтрації текстових даних можуть використовуватись також шаблон ?, що означає довільний символ, і шаблон *, що означає довільну кількість довільних символів. . Якщо умов дві, то їх можна зв'язати логічною операцією І чи АБО. У першому випадку будуть відфільтровані рядки, для яких одночасно виконуються обидві умови, у другому - хоча б одна з них.

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

Для зняття автофільтра потрібно виконати ті ж дії, що і при його встановленні.

Розширений фільтр

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

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

У комірки допоміжної таблиці можна включати і нерівності. Наприклад, вираз <100 забезпечить фільтрацію чисел, менших ста і т.д.

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

Для накладення розширеного фільтра, необхідно помістити курсор всередину основної таблиці і вибрати в меню Даные опції Фильтр і Расширенный фильтр. Тоді в поле Исходный диапазон (рис. 6) буде автоматично занесено діапазон комірок основної таблиці. Для занесення діапазону комірок, допоміжної таблиці, у поле Диапазон условий, треба встановити курсор у це поле, а потім виділити мишкою допоміжну таблицю разом з шапкою.

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

Для зняття розширеного фільтра потрібно вибрати в меню Данные опції Фильтр і Отобразить все.

Контрольні питання

Як встановлюється і знімається автофільтр?

Як користуватися автофільтром?

Як встановлюється і знімається розширений фільтр?

Чим розширений фільтр перевищує можливості автофільтра?

Завдання для виконання

Заповніть 25-30 рядків у робочому аркуші, показаному на рис. 7, де Ч1 - Ч5 - відповідні числові значення, Т1 - найменування 4-5 районів, формула Ф1=Ч5/Ч2. З огляду на те, що найменування районів, кількість кімнат і інші параметри квартир можуть повторюватися, для прискорення заповнення таблиці можна копіювати вміст комірок, але не копіюйте цілі рядки, щоб уникнути однакових наборів параметрів (бажано, щоб у кожному районі були присутні квартири з різними комбінаціями значень цих параметрів).

Скопіюйте вміст даної таблиці на 2 інших робочих аркуша.

На першому аркуші встановіть автофільтр і з його допомогою поекспериментуйте з пошуком квартир із заданими параметрами, наприклад: трикімнатна, у Святошинському районі, з кухнею на менше 8 м.кв., ціною в заданому діапазоні тощо.

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

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

Збережіть створений файл на диску.

Лабораторна робота № 5 Сортування даних. Підведення підсумків. Зведені таблиці

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

Сортуванням називають перевпорядковування даних, при якому значення обраних вами полів (їх називають ключовими полями або ключами) розташовуються:

числа - в порядку зростання або зменшення їх величин,

тексти - в алфавітному порядку (прямому чи зворотному),

дати і час - у хронологічному порядку (прямому або зворотному).

Перед сортуванням даних важливо правильно виділити область, у якій відбуватиметься сортування. Якщо перед вибором меню Данные / Сортировка помістити курсор всередину таблиці, то Excel автоматично виділить усі її рядки і стовпці. Якщо ж виділити тільки частину таблиці, то і сортування відбудеться тільки у межах виділеної області. Наприклад, якщо ви виділите тільки стовпець із прізвищами співробітників, але не виділите стовпець із сумами належних їм виплат, то прізвища будуть перевпорядковані, але суми виплат залишаться нерухомими в колишніх комірках. Так можна зіпсувати дані, на введення яких був витрачений значний час. Виправити помилку можна, якщо відразу після неправильного сортування натиснути кнопку скасування, або закрити файл, не зберігаючи на диску внесені зміни.

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

Для сортування за декількома полями слід помістити курсор всередину таблиці або виділити діапазон комірок, що підлягає сортуванню, та вибрати в меню Данные опцію Сортировка. В результаті не екрані з'явиться вікно, показане на рис. 8.

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

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

У графі Сортировать по виберіть заголовок чи координату стовпця, по якому буде виконуватися сортування, а також напрямок сортування: по возрастанию або по убыванию.

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

Щоб виконати сортування з обраними параметрами, клацніть мишкою на кнопці ОК.

Підведення підсумків

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

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

Закінчивши сортування, і обравши в меню Даные пункт Итоги, на екран виводиться вікно (рис. 9), у якому треба відповісти на наступні питання:

Куди треба вставляти рядки з підсумками? Якщо, наприклад, ми виберемо тут Дата або Товар, то рядок підсумків буде з'являтися там, де змінюється значення дати чи найменування товару.

Як треба підбивати підсумок? Найчастіше підсумок - це просто сума значень у стовпці. Але замість суми можна вибрати, наприклад, середнє арифметичне, максимальне або мінімальне значення тощо.

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

Чи потрібно вилучати всі старі підсумки перед виводом нових? Якщо відмовитись від вилучення, то нові підсумки додаватимуться до сформованих раніше. Це дозволяє виводити декілька різних підсумків одночасно, наприклад, до раніше обчислених сум додати ще й середні значення.

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

Де потрібно розміщати рядки з підсумками? (під відповідними групами рядків даних чи над ними).

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

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

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

Зведені таблиці

є ще одним засобом підведення підсумків. Щоб побудувати зведену таблицю, помістіть курсор всередині бази даних Базою даних у Excel називають таблицю регулярної структури, в якій перший рядок містить заголовки полів (стовпців), а решта рядків заповнені однотипними даними. В таблиці не повинно бути пустих стовпців і об'єднаних комірок., виберіть у меню Даные опцію Сводные таблицы і дайте відповіді на питання програми:

Звідки взяти вхідні дані для побудови зведеної таблиці? Тут виберіть опцію В списке или базе данных Microsoft Excel.

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

Якою повинна бути структура зведеної таблиці? Тут треба за допомогою мишки перетягнути кнопки з зображенням заголовків стовпців вхідної бази даних на відповідні позиції зведеної таблиці. Наприклад, для виконання завдання 6, кнопку Товар треба перемістити в позицію Строка, кнопку Продавец - у позицію Столбец, кнопку Стоимость - у позицію Данные, а кнопку Дата - у позицію Страница (див. рис. 10). Звичайно в позиції Данные кнопка, що відповідає числовим даним вхідної таблиці, набуває вигляду Сумма по полю... Але крім суми, зведена таблиця дозволяє обчислювати і багато інших підсумків: середнє, мінімум, максимум тощо. Щоб змінити спосіб обчислення підсумків, треба двічі клацнути на кнопці в позиції Данные і вибрати потрібний спосіб підведення підсумку.

Де треба розмісити побудовану зведену таблицю? Тут є можливість вибору між новим робочим аркушем і аркушем, що містить вхідну базу даних.

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

microsoft excel програма

Теми для самостійного вивчення

Створення і використання макросів.

Контрольні питання

Як здійснюється сортування даних у Excel?

Які параметри можна задати перед початком сортування?

Як повинні бути відсортовані дані перед підведенням підсумків і чому?

Як здійснюється підведення підсумків?

Що таке символи структури і як ними користуватися?

Що таке макроси? Як записати і виконати макрос?

Що в Excel називають базою даних?

Як здійснюється побудова зведеної таблиці?

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

Размещено на Allbest.ru

...