Як зробити вибірку в excel зі списку

Як зробити випадкову вибірку в Excel зі списку?

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

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

Як зробити випадкову вибірку в excel зі списку?

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

Список унікальних значень можливо створити 6-ю способами:

Створити список унікальних значень за допомогою спеціальної функції

Це дуже простий спосіб для власників Excel вище 2007 версії як зробити відбір унікальних значень. Вам потрібно на вкладці «Дані», в розділі «Робота з даними», використовувати спеціальну команду «Видалити дублікати».

У діалоговому вікні «Видалити дублікати», ви виділяєте ті стовпчики, де необхідно провести відсів унікальних значень і натискаєте «Ок».

Як зробити випадкову вибірку в excel зі списку?

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

Створити список унікальних значень за допомогою розширеного фільтра

Це також не складний спосіб зробити відбір унікальних значень в таблиці.

Використовувати цей інструмент можливо на вкладці «Дані», потім вибрати «Фільтр», і нарешті «Розширений фільтр», цей шлях підходить для Excel 2003, а от власники більш юних версій, від 2007 і вище стоїть пройти по шляху: «Дані» — «Сортування і фільтр» — «Додатково».

Величезний плюс цього способу в тому, що ви можете створити новий список унікальних значень в іншому місці. Як зробити випадкову вибірку в excel зі списку?Після появи діалогового вікна «Розширений фільтр», встановлюємо галочку напроти пункту «Скопіювати результат в інше місце », потім вказуємо діапазон з вашими даними в поле «Вихідний діапазон», при необхідності вказуємо критерій відбору, але для загального відсіву поле залишаємо порожнім «Діапазон критеріїв», в третьому полі «Помістити результат в діапазон» вказуємо перший осередок куди будуть поміщатися наші дані, відзначаємо галочкою пункт «Тільки унікальні записи» і натискаємо «Ок». Як зробити випадкову вибірку в excel зі списку?Якщо ж вам не потрібно нікуди переносити ваші дані, то просто встановіть прапорець для пункту «Фільтрувати список на місці», дані не постраждають, відбудеться накладення звичайного фільтра.

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

Створити список унікальних значень за допомогою формул

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

приклад 1. Вам потрібно пронумерувати, унікальні, значення в списку значень, для цього потрібно використовувати функцію ЯКЩО у формулі такого вигляду:

ЯКЩО (СЧЁТЕСЛІ (B $ 1: B2; B2) = 1; МАКС (A $ 1: A1) +1; »«)

Суть формули в тому, що вона перевіряє скільки разів, поточне значення зустрічається в вашому діапазоні (починаючи з початку), і якщо це значення дорівнює 1, тобто це перше унікальне значення, формула ставить послідовно повертає номер один по одному.

Як зробити випадкову вибірку в excel зі списку?

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

ЯКЩО (МАКС (A1: A100)

Випадкова вибірка

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

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

У будь-якому випадку перед нами стоїть завдання відібрати випадковим чином задану кількість елементів з будь-якого набору (наприклад, ось такого):

спосіб 1. Випадкове сортування

Додати до нашого списку ще один стовпець і вставити в нього функцію генерації випадкових чисел СЛЧИС (RAND). Потім впорядкувати наш список по доданому стовпці (Дані — Сортування) І взяти N перших елементів з отриманої таблиці:

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

спосіб 2. функція НАЙМЕНШИЙ

Цей спосіб полягає у використанні функції НАЙМЕНШИЙ (SMALL) для вибору зі списку N позицій з найменшим випадковим числом в стовпці А:

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

спосіб 3. Випадкова вибірка без повторів — функція Lotto на VBA

Можна створити просту функцію на VBA, яка буде видавати задану кількість випадкових чисел з потрібного інтервалу. Відкриємо редактор Visual Basic (ALT + F11 або в старих версіях Excel через меню Сервіс — Макрос — Редактор Visual Basic), Вставимо новий модуль через меню Insert — Module і скопіюємо туди текст ось такої функції:

Function Lotto (Bottom As Integer, Top As Integer, Amount As Integer)
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Dim Out (1000) As Variant

ReDim iArr (Bottom To Top)
For i = Bottom To Top
iArr (i) = i
Next i

For i = Top To Bottom + 1 Step -1
r = Int (Rnd () * (i — Bottom + 1)) + Bottom
temp = iArr (r)
iArr (r) = iArr (i)
iArr (i) = temp
Next i
j = 0
For i = Bottom To Bottom + Amount — 1
Out (j) = iArr (i)
j = j + 1
Next i

У цій функції буде три аргументи:

  • Bottom — нижня межа інтервалу випадкових чисел
  • Top — верхня межа інтервалу випадкових чисел
  • Amount — кількість випадкових чисел, яке ми хочемо відібрати з інтервалу

Т.е., наприклад, щоб відібрати 5 випадкових чисел від 10 до 100, потрібно буде ввести = Lotto (10; 100; 5)

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

Зверніть увагу, що наша функція Lotto повинна бути введена як формула масиву, т.е. спочатку необхідно виділити діапазон комірок результатів (D2: D6) потім ввести нашу функцио Lotto і, після введення аргументів функції, натиснути Ctrl + Shift + Enter, щоб ввести цю функцію саме як функцію масиву в усі виділені осередки.

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

Вибірка і аналіз даних в Excel

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

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

Деякі інструменти дозволяють представити результати аналізу в графічному вигляді.

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

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

У меню Сервіс вибирається команда Аналіз даних. У списку Інструменти аналізу вибирається необхідна рядок. Далі вводяться вхідний і вихідний діапазони.

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

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

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

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

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

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

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

Наприклад, можна використовувати нормальний розподіл для моделювання сукупності даних по арифметичним помилок в бухгалтерському обліку. Щоб в результаті виконання обчислень повернути рівномірно розподілене випадкове число, більше або дорівнює 0 і менше 1, використовується функція СЛЧИС ().

Щоб повернути випадкове число, яке лежить між довільними заданими значеннями, використовується функція СЛУЧМЕЖДУ ().

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

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

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

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

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

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

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

Як зробити випадаючий список в Excel

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

Клацніть по кнопці нижче для завантаження файлу з прикладами випадаючих списків в Excel:

  1. відеоурок
  2. Як створити список, що випадає в Ексель на основі даних з переліку
  3. Як зробити випадаючий список в Excel за допомогою ручного введення даних
  4. Як створити список, що розкривається в Ексель за допомогою функції зміщений
  5. Як ця формула працює?
  6. Як зробити випадаючий список в Excel з підстановкою даних (з використанням функції зміщений)
  7. Як створити список, що випадає в Excel з автоматичною підстановкою даних
  8. Як скопіювати список, що випадає в Excel
  9. Як виділити всі комірки, які містять список, що випадає в Ексель
  10. Як зробити залежні списки, що випадають в Excel

відеоурок

Як створити список, що випадає в Ексель на основі даних з переліку

Уявімо, що у нас є перелік фруктів:

Як зробити випадаючий список в Excel

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

  • Вибрати клітинку, в якій ми хочемо створити список, що випадає;
  • Перейти на вкладку " дані "=> Розділ" Робота з даними "На панелі інструментів => вибираємо пункт" Перевірка даних ".

  • У спливаючому вікні " Перевірка вводятьсязначень "На вкладці" параметри "В типі даних вибрати" перелік ":

Перевірка вводяться значень в Excel

  • У полі " джерело "Ввести діапазон назв фруктів = $ A $ 2: $ A $ 6 або просто поставити курсор миші в поле вводу значень " джерело "І потім мишкою вибрати діапазон даних:

Якщо ви хочете створити списки, що випадають в декількох осередках за раз, то виберіть всі осередки, в яких ви хочете їх створити, а потім виконайте зазначені вище дії. Важливо переконатися, що посилання на комірки є абсолютними (наприклад, $ A $ 2 ), А не відносними (наприклад, A2 або A $ 2 або $ A2 ).

Як зробити випадаючий список в Excel за допомогою ручного введення даних

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

Наприклад, уявімо що в випадаючому меню ми хочемо відобразити два слова "Так" і "Ні". Для цього нам буде потрібно:

  • Вибрати клітинку, в якій ми хочемо створити список, що випадає;
  • Перейти на вкладку " дані "=> Розділ" Робота з даними "На панелі інструментів => вибрати пункт" Перевірка даних ":

  • У спливаючому вікні " Перевірка вводятьсязначень "На вкладці" параметри "В типі даних вибрати" перелік ":

Перевірка вводяться значень в Excel

  • У полі " джерело "Ввести значення" Так; ні ".
  • натискаємо " ОК "

Так / ні

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

Якщо ви хочете одночасно створити список, що випадає в декількох осередках — виділіть потрібні комірки і дотримуйтесь інструкцій вище.

Як створити список, що розкривається в Ексель за допомогою функції зміщений

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

Наприклад, у нас є список з переліком фруктів:

Як зробити випадаючий список в Excel

Для того щоб зробити список, що випадає з допомогою формули зміщений необхідно зробити наступне:

  • Вибрати клітинку, в якій ми хочемо створити список, що випадає;
  • Перейти на вкладку " дані "=> Розділ" Робота з даними "На панелі інструментів => вибрати пункт" Перевірка даних ":

  • У спливаючому вікні " Перевірка вводятьсязначень "На вкладці" параметри "В типі даних вибрати" перелік ":

Перевірка вводяться значень в Excel

  • У полі " джерело "Ввести формулу: = Зміщений (A $ 2 $; 0; 0; 5)
  • натиснути " ОК "

Система створить список, що випадає з переліком фруктів.

Як ця формула працює?

На прикладі вище ми використовували формулу = Зміщений (посилання; смещ_по_строкам; смещ_по_столбцам; [висота]; [ширина]).

Ця функція містить у собі п’ять аргументів. В аргументі "посилання"(В прикладі $ A $ 2) вказується з якого осередку починати зміщення. В аргументах "смещ_по_строкам"Смещ_по_столбцам" (В прикладі вказано значення "0") — на скільки рядків / стовпців потрібно зміщуватися для відображення даних. В аргументі "[Висота]"Вказано значення" 5 ", яке позначає висоту діапазону комірок. аргумент "[Ширина]"Ми не вказуємо, так як в нашому прикладі діапазон складається з однієї колонки.

Використовуючи цю формулу, система повертає вам в якості даних для списку діапазон комірок, що починається з осередку $ A $ 2, що складається з 5 осередків.

Як зробити випадаючий список в Excel з підстановкою даних (з використанням функції зміщений)

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

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

  • Вибрати клітинку, в якій ми хочемо створити список, що випадає;
  • Перейти на вкладку " дані "=> Розділ" Робота з даними "На панелі інструментів => вибрати пункт" Перевірка даних ";
  • У спливаючому вікні " Перевірка вводятьсязначень "На вкладці" параметри "В типі даних вибрати" перелік ";
  • У полі " джерело "Ввести формулу: = Зміщений (A $ 2 $; 0; 0; СЧЕТЕСЛІ ($ A $ 2: $ A $ 100; "<>"))
  • натиснути " ОК "

У цій формулі, в аргументі "[висота] "Ми вказуємо як аргумент, що позначає висоту списку з даними — формулу СЧЕТЕСЛІ, яка розраховує в заданому діапазоні A2: A100 кількість не порожніх клітинок.

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

Як створити список, що випадає в Excel з автоматичною підстановкою даних

Для того щоб в створений вами список, що випадає автоматично довантажувати нові дані, потрібно виконати наступні дії:

  • Створюємо список даних для відображення в випадаючому списку. У нашому випадку це список квітів. Виділяємо перелік лівою кнопкою миші:

випадає з автоматіеской підстановкою в Ексель

  • На панелі інструментів натискаємо пункт " Форматувати як таблицю ":

  • У спадному меню вибираємо стиль оформлення таблиці:

Список, що випадає в Excel

  • Натиснувши клавішу " ОК "У спливаючому вікні, підтверджуємо обраний діапазон комірок:

Автоматична підстановка даних в Excel

  • Потім, виділимо діапазон даних таблиці для списку і дамо йому ім’я в лівому полі на колонку "А":

Присвоїти ім'я таблиці в Excel

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

  • Вибрати клітинку, в якій ми хочемо створити список;
  • Перейти на вкладку " дані "=> Розділ" Робота з даними "На панелі інструментів => вибрати пункт" Перевірка даних ":

  • У спливаючому вікні " Перевірка вводятьсязначень "На вкладці" параметри "В типі даних вибрати" перелік ":

Перевірка вводяться значень в Excel

  • В поле джерело вказуємо = "Назва вашої таблиці" . У нашому випадку ми її назвали " перелік ":

Поле джерело автоматична підстановка даних в список, що випадає Ексель

  • Готово! Список, що випадає створений, в ньому відображаються всі дані з зазначеної таблиці:

Список, що випадає в Excel

  • Для того щоб додати нове значення в список, що випадає — просто додайте в наступну після таблиці з даними осередок інформацію:

Автоматична підстановка даних в Excel

  • Таблиця автоматично розширить свій діапазон даних. Список, що випадає відповідно поповниться новим значенням з таблиці:

Автоматична підстановка даних в список, що випадає Ексель

Як скопіювати список, що випадає в Excel

В Excel є можливість копіювати створені списки, що випадають. Наприклад, в комірці А1 у нас є список, що випадає, який ми хочемо скопіювати в діапазон комірок А2: А6 .

Список, що випадає в Excel

Для того щоб скопіювати список, що випадає з поточним форматуванням:

  • натисніть лівою клавішею миші на осередок з списком, що випадає, яку ви хочете скопіювати;
  • натисніть клавіші на клавіатурі CTRL + C ;
  • виділіть осередки в діапазоні А2: А6 , в які ви хочете вставити, що випадає;
  • натисніть клавіші на клавіатурі CTRL + V .

Так, ви скопіюєте випадає, зберігши вихідний формат списку (колір, шрифт і.т.д). Якщо ви хочете скопіювати / вставити список, що випадає без збереження формату, то:

  • натисніть лівою клавішею миші на осередок з списком, що випадає, який ви хочете скопіювати;
  • натисніть клавіші на клавіатурі CTRL + C ;
  • виберіть осередок, в яку ви хочете вставити, що випадає;
  • натисніть праву кнопку миші => викличте меню, що випадає і натисніть " спеціальна вставка ";

випадає в excel

  • У вікні в розділі " вставити "Виберіть пункт" умови на значення ":

Список, що випадає в Excel

  • натисніть " ОК "

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

Як виділити всі комірки, які містять список, що випадає в Ексель

Іноді, складно зрозуміти, яка кількість осередків в файлі Excel є пропущені списки. Є простий спосіб відобразити їх. Для цього:

  • Натисніть на вкладку " Головна "На Панелі інструментів;
  • натисніть " Знайти і виділити "І виберіть пункт" Виділити групу осередків ":

Як знайти осередки з списком, що випадає в Excel

  • У діалоговому вікні виберіть пункт " Перевірка даних ". У цьому полі є можливість вибрати пункти " всіх "І" цих же ". " всіх "Дозволить виділити всі списки, що випадають на аркуші. пункт " цих же "Покаже списки, що випадають схожі за змістом даних в випадаючому меню. У нашому випадку ми вибираємо " всіх ":

Список, що випадає в Excel. Як знайти всі списки

  • натисніть " ОК "

натиснувши " ОК ", Excel виділить на аркуші всі осередки з списком, що випадає. Так ви зможете привести за раз все списки до загального формату, виділити кордону і.т.д.

Як зробити залежні списки, що випадають в Excel

Іноді нам потрібно створити кілька списків, що випадають, причому, таким чином, щоб, вибираючи значення з першого списку, Excel визначав які дані відобразити в другому випадаючому списку.

Припустимо, що у нас є списки міст двох країн Росія і США:

Функція Indirect (ДВССИЛ) в Excel

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

  • Створити два іменованих діапазону для осередків " A2: A5 "З ім’ям" Росія "і для осередків" B2: B5 "З назвою" США ". Для цього нам потрібно виділити весь діапазон даних для випадаючих списків:

залежний випадає в Excel

  • Перейти на вкладку " формули "=> Клікнути в розділі" певні імена "На пункт" Створити з виділеного ":

  • У спливаючому вікні " Створення імен з виділеного діапазону "Поставте галочку в пункт" в рядку вище ". Зробивши це, Excel створить два іменованих діапазону "Росія" і "США" зі списками міст:

залежний-випадає-список-в-excel

  • натисніть " ОК "
  • В осередку " D2 "Створіть список, що випадає для вибору країн" Росія "або" США ". Так, ми створимо перший список, що випадає, в якому користувач зможе вибрати одну з двох країн.

функція INDIRECT (ДВССИЛ) в Excel

Тепер, для створення залежного списку:

  • виділіть клітинку E2 (Або будь-яку іншу клітинку, в якій ви хочете зробити залежний випадає);
  • Клацніть по вкладці " дані "=>" Перевірка даних ";
  • У спливаючому вікні " Перевірка вводятьсязначень "На вкладці" параметри "В типі даних виберіть" перелік ":

Перевірка вводяться значень в Excel

  • У розділі "Джерело" вкажіть посилання: = INDIRECT (D2) або = ДВССИЛ (D2);

Як створити залежний випадає в Excel

  • натисніть " ОК "

випадає-список-в-excel-6

Тепер, якщо ви оберете в першому випадаючому списку країну "Росія", то в другому випадаючому списку з’являться тільки ті міста, які відносяться до цієї країни. Також і в разі, коли вибираєте "США" з першого списку.

Ще більше корисних прийомів в роботі зі списками даних і функціями в Excel ви дізнаєтеся в практичному курсі "Від новачка до майстра Excel". успей зареєструватися за посиланням!

Вибірка даних в Microsoft Excel

Вибірка в Microsoft Excel

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

виконання вибірки

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

Спосіб 1: застосування розширеного автофильтра

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

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

Включення фільтра в Microsoft Excel

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

Включення фільтра через вкладку Дані в Microsoft Excel

Після цього дії в шапці таблиці з’являються піктограми для запуску фільтрування у вигляді перевернутих вістрям вниз невеликих трикутників на правому краю осередків. Кількома по даному значку в заголовку того стовпця, по якому бажаємо здійснити вибірку. У запустити меню переходимо по пункту «Текстові фільтри». Далі вибираємо позицію «Настроюваний фільтр …».

Перехід в настроюється фільтр в Microsoft Excel

Давайте як приклад задамо умову так, щоб відібрати тільки значення, за якими сума виручки перевищує 10000 рублів. Встановлюємо перемикач в позицію «Більше». У праве поле вписуємо значення «10000». Щоб зробити виконання дії, клацаємо по кнопці «OK».

Пользвательскій фільтр в Microsoft Excel

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

Результати фільтрації в Microsoft Excel

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

Крім того, існує ще перемикач умов. У нього два положення «І» і «АБО». За замовчуванням він встановлений в першому положенні. Це означає, що у вибірці залишаться тільки рядки, які задовольняють обом обмеженням. Якщо він буде виставлений в положення «АБО», то тоді залишаться значення, які підходять під будь-який з двох умов. У нашому випадку потрібно виставити перемикач в положення «І», тобто, залишити це налаштування за замовчуванням. Після того, як всі значення введені, клацаємо по кнопці «OK».

Установка верхньої межі в призначеному для користувача фільтрі в Microsoft Excel

Тепер в таблиці залишилися тільки рядки, в яких сума виручки не менше 10000 рублів, але не перевищує 15000 рублів.

Результати фільтрації по нижній і верхній межі в Microsoft Excel

Перехід до фільтрації за датою в Microsoft Excel

Знову запускається вікно призначеного для користувача автофильтра. Виконаємо відбір результатів в таблиці з 4 по 6 травня 2016 року включно. У перемикачі вибору умов, як бачимо, ще більше варіантів, ніж для числового формату. вибираємо позицію «Після або дорівнює». В поле праворуч встановлюємо значення «04.05.2016 ». У нижньому блоці встановлюємо перемикач в позицію «До або дорівнює». У правому полі вписуємо значення «06.05.2016 ». Перемикач сумісності умов залишаємо в положенні за замовчуванням — «І». Для того, щоб застосувати фільтрацію в дії, тиснемо на кнопку «OK».

Пользвательскій фільтр для формату дати в Microsoft Excel

Як бачимо, наш список ще більше скоротився. Тепер в ньому залишені тільки рядки, в яких сума виручки варіюється від 10000 до 15000 рублів за період з 04.05 по 06.05.2016 включно.

Результати фільтрації за сумою і датою в Microsoft Excel

Ми можемо скинути фільтрацію в одному з стовпців. Зробимо це для значень виручки. Кількома по значку автофильтра у відповідному стовпці. У випадаючому списку клацаємо за пунктом «Видалити фільтр».

Видалення фільтра з одного із стовпців в Microsoft Excel

Як бачимо, після цих дій, вибірка по сумі виручки буде відключена, а залишиться тільки відбір по датах (з 04.05.2016 з 06.05.2016).

Обмеження тільки за датою в Microsoft Excel

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

Кількома по значку фільтра в найменуванні стовпчика. Послідовно переходимо за найменуваннями списку «Текстові фільтри» і «Настроюваний фільтр …».

Перехід до текстової фільтрації в Microsoft Excel

Знову відкривається вікно призначеного для користувача автофильтра. Давайте зробимо вибірку за найменуваннями «Картопля» і «М’ясо». У першому блоці перемикач умов встановлюємо в позицію «Так само». В поле праворуч від нього вписуємо слово «Картопля». Перемикач нижнього блоку так само ставимо в позицію «Так само». В поле навпроти нього робимо запис — «М’ясо». І ось далі ми виконуємо те, чого взагалі раніше не робили: встановлюємо перемикач сумісності умов в позицію «АБО». Тепер рядок, що містить будь-який із зазначених умов, буде виводитися на екран. Клацаємо по кнопці «OK».

Пользвательскій фільтр для формату тексту в Microsoft Excel

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

Обмеження за датою і по найменуванню в Microsoft Excel

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

Очищення фільтра в Microsoft Excel

Другий варіант передбачає перехід у вкладку «Головна». Там виконуємо клацання на стрічці по кнопці «Сортування і фільтр» в блоці «Редагування». В активувати списку натискаємо на кнопку «Фільтр».

Очищення фільтра у вкладці Головна в Microsoft Excel

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

Фільтр скинутий в Microsoft Excel

Спосіб 2: застосування формули масиву

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

    На тому ж аркуші створюємо порожню таблицю з такими ж назвами стовпців в шапці, що і у исходника.

Створення порожній таблиці в Microsoft Excel

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

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

Введення формули в Microsoft Excel

Так як це формула масиву, то для того, щоб застосувати її в дії, потрібно натискувати не кнопку Enter, а поєднання клавіш Ctrl + Shift + Enter. робимо це.

Формула масиву введена в стовпець найменувань в Microsoft Excel

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

Тиснемо поєднання клавіш Ctrl + Shift + Enter.

Формула масиву введена в стовпець дати в Microsoft Excel

Аналогічним чином в стовпець з виручкою вписуємо формулу такого змісту:

Знову набираємо поєднання клавіш Ctrl + Shift + Enter.

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

Формула масиву введена в стовпець виручки в Microsoft Excel

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

Перехід до форматування осередків в Microsoft Excel

У вікні форматування відкриваємо вкладку «Число». У блоці «Числові формати» виділяємо значення «Дата». У правій частині вікна можна вибрати бажаний тип відображення дати. Після того, як налаштування виставлені, тиснемо на кнопку «OK».

Установка формату дати в Microsoft Excel

Тепер дата відображається коректно. Але, як бачимо, вся нижня частина таблиці заповнена осередками, які містять помилкове значення «# ЧИСЛО!». По суті, це ті осередки, даних з вибірки для яких не вистачило. Більш привабливо було б, якби вони відображалися взагалі порожніми. Для цих цілей скористаємося умовним форматуванням. Виділяємо всі елементи таблиці, крім шапки. Перебуваючи у вкладці «Головна» натискаємо на кнопку "Умовне форматування", яка знаходиться в блоці інструментів «Стилі». У списку вибираємо пункт «Створити правило …».

Перехід до створення правила в Microsoft Excel

У вікні вибираємо тип правила «Форматувати тільки осередки, які містять". У першому полі під написом «Форматувати тільки осередки, для яких виконується така умова» вибираємо позицію «Помилки». Далі тиснемо на кнопку «Формат …».

Перехід до вибору формату в Microsoft Excel

У запустити вікні форматування переходимо у вкладку «Шрифт» і в відповідному полі вибираємо білий колір. Після цих дій клацаємо по кнопці «OK».

Формат ячеек в Microsoft Excel

  • На кнопку з точно такою ж назвою тиснемо після повернення в вікно створення умов.
  • Створення умови форматування в Microsoft Excel

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

    Вибірка зроблена в Microsoft Excel

    Спосіб 3: вибірка по декількох умовах за допомогою формули

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

      Вписуємо в окремому стовпці граничні умови для вибірки.

    Умови в Microsoft Excel

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

    У наступні колонки вписуємо точно такі ж формули, тільки змінивши координати відразу після найменування оператора ІНДЕКС на відповідні за потрібне нам стовпчиках, по аналогії з попереднім способом.

    Щоразу після введення не забуваємо набирати поєднання клавіш Ctrl + Shift + Enter.

    Результат вибірки по декільком умовам в Microsoft Excel

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

    Спосіб 4: випадкова вибірка

    У Ексель за допомогою спеціальної формули СЛЧИС можна також застосовувати випадковий відбір. Його потрібно проводити в деяких випадках при роботі з великим об’ємом даних, коли потрібно представити загальну картину без комплексного аналізу всіх даних масиву.

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

    Ця функція виводить на екран випадкове число. Для того, щоб її активувати, тиснемо на кнопку ENTER.

    Випадкове число в Microsoft Excel

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

    Маркер заповнення в Microsoft Excel

    Тепер у нас є діапазон комірок, заповнений випадковими числами. Але, він містить в собі формулу СЛЧИС. Нам же потрібно працювати з чистими значеннями. Для цього слід виконати копіювання в порожній стовпець праворуч. Виділяємо діапазон комірок з випадковими числами. Розташувавшись у вкладці «Головна», клацаємо по іконці «Копіювати» на стрічці.

    Копіювання в Microsoft Excel

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

    Вставка в Microsoft Excel

    Після цього, перебуваючи у вкладці «Головна», натискаємо по вже знайомому нам значку «Сортування і фільтр». У випадаючому списку зупиняємо вибір на пункті «Настроюється, сортування».

    Перехід до настроюваної сортуванні в Microsoft Excel

    Активується вікно настройки сортування. Обов’язково встановлюємо галочку навпроти параметра «Мої дані містять заголовки», якщо шапка є, а галочки немає. У полі "Сортувати за" вказуємо найменування того стовпця, в якому містяться скопійовані значення випадкових чисел. У полі «Сортування» залишаємо налаштування за замовчуванням. У полі «Порядок» можна вибрати параметр як "За зростанням", так і "По спаданню". Для випадкової вибірки це значення не має. Після того, як налаштування зроблені, тиснемо на кнопку «OK».

    Налаштування сортування в Microsoft Excel

  • Після цього всі значення таблиці шикуються в порядку зростання або зменшення випадкових чисел. Можна взяти будь-яку кількість перших рядків з таблиці (5, 10, 12, 15 і т.п.) І їх можна буде вважати результатом випадкової вибірки.
  • Випадкова вибірка в Microsoft Excel

    Як бачимо, вибірку в таблиці Excel можна зробити, як за допомогою автофільтра, так і застосувавши спеціальні формули. У першому випадку результат буде виводитися в вихідну таблицю, а в другому — в окрему область. Є можливість проводити відбір, як по одній умові, так і за кількома. Крім того, можна здійснювати випадкову вибірку, використавши функцію СЛЧИС.

    Ми раді, що змогли допомогти Вам у вирішенні проблеми.

    Ссылка на основную публикацию