Форматування числових даних. Створення та видалення числових форматів.

Ефективність списку обумовлена такими характеристиками.

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

• Один чи два верхні рядки у списку мають містити мітки, що описують призначення відповідного стовпця.

14 Заповнення даними діапазону комірок M S Excel

Введення даних здійснюється в три етапи. Перший етап - виділення клітинки. Другий етап - введення даних з клавіатури або з «Буфера обміну». Третій етап - підтвердження введення даних натисканням клавіші «Enter».

Необхідно запам'ятати, що є можливість вводити данні в рядок формул при виділенні клітинки для заповнення. Для форматування комірок необхідно у контекстному меню натиснути «Формат комірок…».

У вікні, що з'явилось на відповідних вкладках, змінюється формат комірок.

Ті самі дії можна зробити виділивши діапазон комірок та обравши необхідні кнопки на вкладці «Головна». Наприклад, для встановлення меж комірок необхідно Натиснути кнопку «Межі» на Вкладці «Головна» виділивши попередньо діапазон для редагування.

 15. Форматування данних M S Excel

Форматування комірок

Для зміни ширини стовбця/висоти рядка необхідно натиснути правою кнопкою миші на імені стовбця/рядка (ім'я стовбцялатинські літери, ім'я строки-цифри) та у контекстному меню обрати «Ширина стовбця» / «Висота рядка».

Ті самі дії виконуються для діапазону стовбців/рядків. Змінити ширину стовбця/висоту рядка можна за допомогою курсора.

Для закріплення виділеної області таблиці (рядок, стовпець) необхідно перейти на вкладку «Вид» і натиснути кнопку «Закріпити області». У меню, що з'явилося, обрати відповідну область для закріплення – перший рядок або перший стовпчик.

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

Для того, щоб зняти закріплення області, необхідно натиснути кнопку «Закріпити області» та у меню обрати «Зняти закріплення областей».

16.Види посилань на комірки MS Excel

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

Посилання, яке при копіюванні в інші комірки буде модифікуватись (змінюватись) відповідно новому розташуванню, є відносним. Наприклад, А1, В4, Р38 тощо.

Абсолютним називається посилання, що не змінюється при виконанні операцій з комірками і листами. В абсолютних посиланнях перед назвою стовпця і номером рядка стоїть знак $. Наприклад, $А$1, $B$4, $Р$38 тощо.

У змішаних посиланнях абсолютною є назва стовпця і відносною – назва рядка або навпаки (наприклад, $А1 та А$1 ). При виконанні операцій з комірками і листами модифікується тільки відносна частина посилань.

Перехід між типами посилань (відносним, абсолютним та змішаним) здійснюється за допомогою натискання клавіші F4 в рядку формул, виділивши попередньо те посилання, яке потрібно перетворити. Можна і просто ввести символ $ з клавіатури.

Можна здійснювати посилання на комірки, які знаходяться на іншому листі робочої книги. Зокрема, посилання Квартал1! В19 буде означати відносне посилання на комірку В19, яка розташована на листі Квартал1. Якщо в імені листа є пропуски, то воно береться в одинарні лапки. Наприклад, 'Лист 2'! А14: В14 (посилання на діапазон комірок А14: В14 з листа 2).

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

 

17/ Типи операторів MS Excel (17)

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

Типи операторів:

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

Арифметичний оператор	Значення	Приклад
+ (знак плюс)	Додавання	3+3
– (знак мінус)	Віднімання Від'ємність	3–1 –1
* (зірочка)	Множення	3*3
/ (коса риска)	Ділення	3/3
% (знак відсотка)	Percent	20 %
^ (кришка)	Піднесення до степеня	3^2

Нижче наведено оператори, за допомогою яких можна порівняти два значення. Результатом порівняння є логічне значення: ІСТИНА або ХИБНІСТЬ.

Оператор порівняння	Значення	Приклад
= (знак рівності)	Дорівнює	A1=B1
> (знак " більше" )	Більше	A1> B1
< (знак " менше" )	Менше	A1< B1
> = (знак " більше або дорівнює" )	Більше або дорівнює	A1> =B1
< = (знак " менше або дорівнює" )	Менше або дорівнює	A1< =B1
< > (знак нерівності)	Не дорівнює	A1< > B1

Амперсанд (& ) використовується, щоб об’єднати кілька текстових рядків в один фрагмент тексту.

Текстовий оператор	Значення	Приклад
& (амперсанд)	Об’єднує два значення в одне безперервне текстове значення	(" Північний" & " вітер" )

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

Оператор посилання	Значення	Приклад
: (двокрапка)	Оператор діапазону, який створює посилання на всі клітинки, розташовані між двома посиланнями, включно з ними	B5: B15
; (крапка з комою)	Оператор об’єднання, який об’єднує кілька посилань в одне	SUM(B5: B15; D5: D15)
(пробіл)	Оператор перетину, який створює одне посилання на спільні клітинки двох діапазонів	B7: D7 C6: C8

 

18 Робота з майстром вставки функції MS Excel (18)

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

До вбудованих функцій відноситься, зокрема, і функція підсумовування СУММ, розглянута вище у ряді прикладів. Взагалі, вбудовані функції широко використовуються в Excel для простих і складних обчислень, полегшуючи процес обробки даних. Крім того, застосування цих функцій позбавляє від довгих записів і знижує вірогідність помилок.
Модуль вбудованих функцій в Excel володіє дружнім інтерфейсом. При зверненні до тієї або іншої функції вам пропонується певний шаблон, який потрібно заповнити. Причому правила запису функцій ви можете уявляти собі лише у загальних рисах. Зупинимося на тому, який повинне виглядати звернення до функції в Excel.
Запис функції починається з вказівки імені функції, потім слідує список аргументів, ув'язнений в дужки. Наприклад, функція знаходження максимального значення серед аргументів число1, число2... має вигляд

МАКС(число1, число2..)

Аргументи - це величини, які використовуються для обчислення значення функції. Результат обчислення функції називається значенням, що повертається.
Дужки, що обмежують список аргументів, завжди повинні бути парними; пропуски перед дужками або після них не допускаються. Список аргументів може складатися з чисел, посилань, тексту, масивів, логічних величин (ІСТИНА або БРЕХНЯ), значень помилок (наприклад, #ДЕЛ/0). Окремі аргументи в списку повинні бути розділені комами. Деякі функції можуть мати до 30 аргументів.
Розглянемо запис різних типів аргументів. У попередніх розділах було показано, як записуються в списку аргументів посилання на осередки і діапазони осередків. Аналогічно записується список аргументів, що складається з чисел і посилань:

СУМ(1, 10, В2: В8).

Ця формула повертає значення, рівне сумі чисел 1, 10 і чисел в осередках від В2 до В8.
Нагадаємо, що посилання на осередки або діапазони у формулах можуть бути замінені іменами. Правила привласнення імен вже розглядалися. Для перегляду всіх наявних в Робочій книзі імен ви можете створити спеціальний список на листі. Це зробить зручнішим використання імен в процесі запису формул.
Excel допускає вкладення функцій один в одного, тобто використання функції як аргумент для іншої функції. Функція, яка є аргументом іншої функції, називається вкладеною. Наприклад, в записі

СУМ(А1: С5, МОПРЕД(D1: ЕЗ))

використана вкладена функція МОПРЕД, яка повертає значення визначника матриці осередків D1: E3. Рівень вкладеності функцій у формулах Excel не повинен перевищувати семи.
2. Майстер функцій.
Для роботи з Майстром функцій слід:
11Встановити курсор у комірку, в якій потрібно вивести результат обрахунку.
2.Натиснути кнопку Мастер функций панелі інструментів Стандартная або виконати команду Вставка / Функции.
3.При цьому відкривається діалогове вікно Мастер функций - шаг 1 из 2, в якому можна вибрати категорію функцій. При виборі категорії в поле Функции виводиться список функцій даної категорії. У цьому списку можна вибрати потрібну функцію. У рядку стану виводиться короткий опис функції.
4.Після вибору функції слід натиснути кнопку Далее.
5.У результаті цього відкриється вікно діалогу Мастер функций - шаг 2 из 2, в якому потрібно вказати аргументи функції.
6.Після цього слід натиснути кнопку Готово, при чому формула вставляється в активну комірку.
Основною особливістю використання формул і функцій в E c xel є те, що в комірках відображаються лише результати розрахунків, і для того, щоб побачити, за якою формулою проведено розрахунки необхідно виділити вказану комірку і подивитися у стрічку формул, у якій виводяться формули для розрахунків.
 Якщо стрічка формул на екрані відсутня, її слід вивести на екран, виконавши команду “Строка формул “ з меню Вид.

19

20

21.основні компоненти діаграми вексель

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

Основні лінії. Microsoft Excel створює значення на осі з даних робочого листа. У приведеному прикладі значення на осі змінюються від 150 до 850, що відповідає значенням клітинок діапазону на листі. Основні лінії позначають основні інтервали на осі. На діаграмі можна відображати і проміжні лінії, що позначають інтервали усередині основних інтервалів.

Імена рядів даних діаграм. Excel використовує заголовки стовпців або рядки даних, як імена рядків даних. Імена рядів відображаються в легенді діаграми. У приведеному прикладі як імена рядів виступають заголовки стовпців «Нараховане» та «До видачі».

Вісь Х – відображається в нижній частині діаграми, а вісь Y – з лівого краю діаграми.

Легенда повідомляє про те, як знайти різні ряди даних на діаграмі.

Заголовок діаграми – повинен описувати те, що відображається на діаграмі.

Заголовок осі – може застосовуватися для відображення додаткової інформації про значення.

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

Лист діаграми – це окремий лист в книзі, що має власне ім’я. Листи діаграм слід використовувати у випадках, коли потрібно проглянути або змінити великі або складні діаграми даних, або коли потрібно зберегти простір екрану для роботи з листом.

22.Типи діаграм вексель

23 Робота з майстром діаграм MS Excel

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

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

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

Щоб видалити діаграму, потрібно її виділити і натиснути кнопку Delete.

Щоб перемістити діаграму, потрібно становити курсор миші в область побудови діаграми і потягнути у потрібному напрямку.

Щоб змінити розміри діаграми, слід скористатися маркерами на границі області побудови діаграми.На панелі інструментів Конструктор можна поміняти Макет діаграми та Стиль: вибрати діаграму з підписами даних або без, з легендою або без неї, вибрати кольорову палітру для діаграми.а) команди контекстного меню б) команди вікна ФорматДля зміни параметрів діаграми також можна скористатися контекстним меню в області побудови діаграми. Контекстне меню на будь-якому елементі діаграми дозволяє змінити його Формат (колір, розмір, границю, шкалу і т.д.) за допомогою відповідної команди

24 Настроювання діаграм  MS Ecsel

Ви можете створити просту діаграму, виділивши будь-яку частину діапазону, який потрібно візуалізувати, і вибравши потрібний тип діаграми на вкладці Вставлення стрічки в групі Діаграми. Або просто натисніть клавіші Alt+F1, а Excel автоматично створить просту стовпчасту діаграму. Після цього ви матимете кілька можливих способів, як змінити діаграму на потрібну вам.

25. 26. Сортування у таблицях.

Особливий порядок сортування у таблицях.

 

27/Фільтрування за допомогою авто фільтру

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

1. Виділіть дані, які потрібно відфільтрувати.

2. На вкладці Дані натисніть кнопку Фільтр.

1. Клацніть стрілку в заголовку стовпця та виконайте одну з таких дій:

2. Виберіть потрібні значення. Зніміть прапорець (Виділити все), щоб зняти всі прапорці, а потім виберіть лише потрібні значення.

· Знайдіть значення. У полі Пошук введіть текст або числа, які потрібно знайти.

Натисніть кнопку OK, щоб застосувати фільтр. Порада: Якщо потрібно застосувати стандартний або настроюваний фільтр до даних, перегляньте фільтрувати дані в діапазоні або таблиці

28.застосування авто фільтру користувача.Приклади.

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

· для відносно простих критеріїв Автофильтр

· для більш складних критеріїв Расширенный фильтр.

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

Можливостями автофільтра можна скористатися, розташувавши активну комірку всередині списку та скориставшись командою Данные / Фильтр / Автофильтр. Кнопки зі стрілкою, що з’являються праворуч від кожного імені поля, дозволяють відкрити перелік значень даного поля та накласти обмеження, створивши критерій відбору за декількома полями. В результаті відбору відображаються тільки ті записи, що відповідають заданому критерію, а рядок стану містить повідомлення про кількість знайдених записів.

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

Елемент (Все) списку дозволяє зняти обмеження щодо даного поля.

За допомогою елемента (Первые 10…) користувач може відібрати певну кількість записів з найбільшими або найменшими значеннями у даному числовому полі. Вікно діалогу Наложение условия по списку дозволяє вибрати максимальні або мінімальні значення, а також встановити числове або процентне обмеження на кількість потрібних записів.

Можна задавати й більш складні критерії, ніж перевірка рівності. Скориставшись елементом списку (Условие…), можна відкрити вікно діалогу Пользовательский автофильтр й з’єднати за допомогою логічних операторів окремі умови відбору записів за одним полем (з будь якими відношеннями порівняння). При визначенні критеріїв за текстовими полями можна використовувати також символи шаблона "? " та " * ".

Застосувати автофільтр можна тільки до одного списку на робочому аркуші.

Для відображення всіх записів списку можна скористатись командою Данные / Фильтр / Отобразить все. Для відміни (деактивації) автофільтра потрібно повторно скористатись командою Данные / Фильтр / Автофильтр.

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

29.Види критеріїв розширеного фільтру.їх відмінності.

Варіанти формування критеріїв розширеного фільтру для списку, який був представлений на Рис. 5.2:

1. Витягти зі списку записи, що містять дані про співробітниць з окладом більше 500 (Рис. 14а). Розміщені в одному рядку критерії реалізують логічну функцію И.

2. Витягти зі списку записи, що містять дані про співробітниць, або тих, хто має оклад більше 500 (Рис. 5.14б). Розміщені в різних рядках критерії зв'язані логічною ИЛИ функцією.

3. Витягти зі списку дані про співробітників, чиї прізвища починаються з літер З, Ж и И. У цьому випадку повинний бути реалізований критерій, що використовує ИЛИ функцію для даних одного стовпця (Рис. 14в).

4. Витягти зі списку дані про співробітників, прізвище яких починаються з З, Ж и Н і оклад яких більше 700 (Рис. 14г). У цьому випадку одночасно використовуються ИЛИ функції і И.

5. Витягти зі списку дані про співробітників, чий оклад знаходиться в діапазоні від 450 до 1200 (Рис. 14д). У цьому випадку реалізується функція И для даних одного стовпця. Приклади формування критеріїв

Умови можна задавати не тільки числові, а також текстові. При завданні текстових умов треба пам'ятати наступні правила:

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

· знак " > " або " < " означає, що потрібно знайти всі значення, що знаходяться за алфавітом після або перед уведеною літерою.

Крім того можна задавати умови для часу, дати а також логічні умови.

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

Приклад 2. Для таблиці (Рис. 5.2) витягти прізвища і телефони менеджерів. При цьому вихідний діапазон визначиться як a1: f6.

Критерій заданий діапазоном h1: h2, діапазон заголовків результату - b10: c10, результатом фільтрації є дані в осередках b11: c12 (Рис. 15).

До результатів розширеної фільтрації може бути застосовна функція ПРОМЕЖУТОЧНЫЕ.ИТОГИ так само, як і для автофільтру. При використанні розширеного фільтра можна використовувати умови, що обчислюються, тобто значення, що повертаються формулою.

Створення цих умов вимагає виконання наступних правил:

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

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

· посилання на осередки в списку повинні бути відносними.

Наприклад, для списку (Рис. 2) необхідно знайти всіх співробітників, у яких заробітна плата більше середнього окладу. Нехай у клітинку H1 записана формула =СРЗНАЧ(D2: D7), а в осередках Н2: Н3 введена умова, що обчислюється (Рис.16).

30 Приклади використання текстових критеріїв розширеного фільтру

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

Складний критерій містить у собі декілька простих крите­ріїв, сполучених або по вертикалі — декілька умов на одне по­ле, або по горизонталі — по одній умові на декілька полів.ты в диапазон” зазначають адресу вільної комірки. ОК. З’являється нова БД.

2. Вибрати зі списку всі записи, що стосуються або клієнтів Васильєва, або Петрова, або Іванова.

Даний запит включає три умови, що накладаються на поле Прізвище клієнта (прізвище — або Васильєв, або Петров, або Іванов), тому створюється складний критерій із трьох простих, які поєднані зв'язкою ИЛИ.

У комірку ВЗ копіюється умова для пошуку — Петров, а в комірку В4 — Іванов:

Діапазоном створеного складного критерію будуть комірки В1: В4.

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

8. Вибрати зі списку всі записи про максимальну і міні­мальну суму замовлення.

У комірку Е6 копіюється заголовок поля Сума замовлення, у комірку Е7 заноситься функція

= МАКС(абсолютний діапазон числових значень цього поля), а у комірку Е8 — функція

=МИН(абсолютний діапазон числових значень цього поля).

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

⇐ Предыдущая234567891011Следующая ⇒

Поделиться: