Формування списку за допомогою формул excel

Відео: Excel | Створення формул. Робота з формулами, робота з посиланнями

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

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

Отже, подивіться на малюнок вище, до чого ми повинні прийти. Список боржників повинен автоматично поповнюватися прізвищами, якщо в стовпці «B» буде з`являтися слово «Ні». Нам буде потрібно додати допоміжний стовпець. Для зручності ми додамо його найпершим, щоб можна було використовувати функцію ВПР (Щоб шукане значення знаходилося лівіше, що повертається), можна було б використовувати функцію ІНДЕКС, в цьому випадком допоміжний стовпець можна було розмістити де завгодно.

В даному стовпці ми пропишемо таку формулу

= РЯДОК ()

Відео: Створення меню, що випадає (списку) в MS Excel

 Функція так і прописується без аргументів. Ця функція повертаємо нам номер рядка. Тобто якщо ця функція буде прописана в третьому рядку, то результатом буде 3 і так далі. Єдине, нам потрібно, щоб обчислення номера рядка було тільки в тих рядках, де є слово «Ні». Для цього скористаємося функцією ЯКЩО і зробимо перевірку. Якщо в стовпці «B» є слово «Ні», то обчислювати номер рядка, якщо немає, то вказати в осередок порожньо. Формула прийме наступний вигляд.

= ЕСЛИ (B2 = "Ні" -СТРОКА () - "")

Простягаємо формулу на всі рядки.

У підсумку ми отримали номер рядка тільки в тих осередках, де навпроти в стовпці «B» є слово «Ні».

Далі переходимо в стовпець «Е», це теж буде допоміжний стовпець, тут ми скористаємося функцією НАЙМЕНШИЙ, функція виглядає наступним чином

= НАЙМЕНШИЙ (A: A-k)

де k - це порядок старшинства. Наприклад, якщо вказати 2, то буде обрано друге найменше число з шпальти «A». Нам же потрібно, щоб спочатку було найменше число і потім воно повинно збільшуватися, тобто k має починатися з одиниці і з кожному осередку повинно збільшуватися. Для цього ми можемо зробити ще один додатковий стовпець і прописати пункти по порядку 1, 2, 3 і так далі і зробити посилання на ці осередки, але ми спробуємо обійтися без допоміжного стовпця. Замість k ми можемо так само скористатися функцією СТРОКА (), яка при протягуванні вниз якраз буде збільшуватися на одиницю, але так як формулу ми починаємо з другого рядка, а нам потрібна перша k дорівнює 1, то ми можемо прописати СТРОКА () - 1 , в результаті формула набуде вигляду в осередку «E2», цю формулу протягнемо вниз з запасом.

= НАЙМЕНШИЙ (A: A-СТРОКА () - 1)

Єдине, що коли все числа з шпальти «A» закінчаться, то формула буде видавати помилку «# ЧИСЛО», тому за допомогою функції ЕСЛІОШІБКА, зробимо перевірку і якщо повертається помилка, то будемо вказувати вільну позицію.

= ЕСЛІОШІБКА (НАЙМЕНШИЙ (A: A-СТРОКА () - 1) - "«)

Ось тепер, все як треба. Залишилося останнє дію. За допомогою функції ВПР, по згаданої значенням зі стовпчика bdquo-Eldquo-, повертаємо відповідні прізвища.

= ВПР (E2-A: B-2-0)

Зробимо так само перевірку, якщо в стовпці bdquo-Eldquo- буде порожня клітинка, то буде повертати порожньо, якщо осередок не порожня, то будемо виконувати функцію. Пропишемо підсумкову формулу в комірці bdquo-F2ldquo- і протягнемо формулу вниз так само з запасом.

= ЕСЛИ (E2 = "" - "" -ВПР (E2-A: B-2-0))

Все, тепер ПІБ буде автоматично з`являтися, коли навпроти прізвища буде з`являтися слово «Ні».

Завантажити приклад файлу https://yadi.sk/i/9HjjoSwzf4jS7

Сподобалася стаття? Ставте лайки, вступайте в нашу групу ВКонтакте.