Разбиваем диапазон ячеек на отдельные группы формулами. Часть первая.
Обычно функции в Excel применяются для выполнения определенных расчетов. Многие считают, что если Excel предназначен для обработки таблиц, то и функции в нем нужны только для выполнения различных вычислений.
На самом деле это конечно далеко не так. На нашем занятии мы рассмотрим в качестве примеров разделение общего диапазона на группы с помощью функций. В процессе работы часто приходится сталкиваться с задачей, когда имеющийся перечень необходимо определенным образом разбить на части в зависимости от имеющихся требований. Чаще всего это происходит при получении данных из внешнего источника определенного типа базы данных.
К примеру, исходные данные могут выглядеть так
Или – так
Или – так
В первом случае надо вывести в отдельные колонки соответствующие друг другу даты, виды банковских операций и денежные суммы. Во втором случае требуется общий список преобразовать в таблицу с графами ФИО, адрес, дополнительный адрес, телефон и сумма, в которой вывести данные по каждому сотруднику в отдельной строке. В третьем случае необходимо вывести в отдельные группы данные по каждому годовому периоду по отдельности в виде построчных блоков по 12 месяцев.
Все эти ситуации имеют сходные признаки. Имеется определенный перечень, определенный список, который требуется разделить на части. При этом нет никакой связи с разделением текста по столбцам, так как разделить по группам необходимо не значения в ячейках, а сами ячейки. Обычно в таких случаях предлагается использовать макросы и платные надстройки. На самом деле для решения задачи достаточно применить функции СТРОКА, СТОЛБЕЦ и, а также инструменты «фильтр» и «выделение группы ячеек». И если инструмент фильтр достаточно хорошо известен многим, а инструмент «выделение группы ячеек» мы довольно часто применяем для обработки таблиц, то функции ОСТАТ, СТРОКА и СТОЛБЕЦ в большинстве случаев незаслуженно забыты. Действительно, ну какая может быть польза от знания номера строки или столбца? Но не будем торопиться. Сегодня мы рассмотрим, как выполняется нумерация ячеек отдельных групп в общем диапазоне с помощью указанных функций.
Вначале разберемся с принципами работы данных функций. Прежде всего рассмотрим работу функций СТРОКА и СТРОЛБЕЦ. Эти функции выводят соответственно номер строки и номер столбца по адресу указанной ячейки или диапазона. Причем, если в качестве параметра указать диапазон, то будет выведен номер строки или столбца для верхней левой ячейки данного диапазона. Поэтому результат работы этих функций как для ячейки B3, так и для диапазона B3:G10 будет одинаков.
Как видно из примера, синтаксис и принцип работы этих функций несложен. Мы указываем в качестве исходных данных адрес, а функции нам выводят номер строки или столбца соответственно. Однако есть важный нюанс. Если адрес не указывать, то есть после названия функции вывести просто парные круглые скобки, то функции СТРОКА и СТОЛБЕЦ выведут соответственно номер строки и столбца именно для той ячейки, в которой функция прописана. И именно этот факт и поможет нам разделить группу ячеек на отдельные части по заданному принципу.
Применение функции ОСТАТ немного сложнее для понимания. Эта функция возвращает целый остаток от деления. Что это значит? Это – целое число, которое остается после деления двух чисел. Разберем несколько вариантов. Вариант первый. Мы будем делить число 20 на 5. Будет ли у нас какой-то остаток после деления? Нет. Поэтому в этом примере целый остаток равен нулю. А теперь возьмем то же число 20 и будем его делить на число 7. Итогом вычисления целой части результата будет число 2 и остаток 6. Если мы продолжим деление, то уже получим дробную часть итогового результата. Так вот, 6 это и есть целый остаток от деления 20 на 7. Другими словами, целый остаток – это минимальное целое число, возникающее в процессе деления двух чисел (обычно – натуральных). Аналогично, если мы будем делить число 20 на 6, то целый остаток будет равен 2, а если попробуем поделить 14 на 5, то целый остаток будет равен 4.
Так вот, функция ОСТАТ и предназначена для вывода такого целого остатка. В качестве параметров (то есть аргументов) этой функции указывают вначале исходное число, которое мы будем делить, а затем – сам делитель. При этом допускается указание как конкретных числовых значений, так и ячеек, которые их содержат. Кстати, интересный момент. Если остатком от деления выбранной даты на 7 будет 0, то дата является субботой, если результат равен 1, то это понедельник, и так далее до 6. Если результат равен 6, та дата является пятницей. Только не забудьте применить к результату расчета функции ОСТАТ на основе даты применить к результату числовой формат.
Однако вернемся к нашей задаче. Использование функций ОСТАТ, СТРОКА и СТОЛБЕЦ для разделения списка на группы проходит в 3 этапа.
- Выполняем нумерацию позиций для каждой группы.
- Отфильтровываем данные каждой группы.
- Копируем отображаемые в фильтре позиции группы в нужное место.
Рассмотрим все эти действия по прядку. И начнем мы с третьего примера, приведенного в начале занятия. Имеется некий помесячный список, который необходимо разбить на части по 12 строк в каждой. Что дальше будет происходить с этими частями – неважно. Будет ли анализироваться размах значений в каждой группе, будут ли рассчитываться квартили в каждой группе или выполняться иные расчеты – для нас неважно. Наша задача – подготовить эти группы.
Итак, приступим. Для начала выведем номер строки для каждой позиции списка. Очевидно, что для этого достаточно применить формулу
=СТРОКА()
А теперь сделаем следующее. Так как в году всего 12 месяцев, то вычислим остаток от деления номера строки на 12. Применим в качестве первого параметра для функции ОСТАТ функцию СТРОКА(), а в качестве второго – число 12. Наша формула принимает следующий вид:
=ОСТАТ(СТРОКА();12)
Обратите внимание, что напротив значений января наша формула вывела результат 2.И вырисовывается следующая картина. Если результатом вычисления формулы, которую мы использовали, будет число 2, то номер группы, взятый из ячейки сверху, надо увеличить на единицу, во всех остальных случаях оставить неизменным. Выполнить же такую проверку можно с помощью функции ЕСЛИ. Введем в ячейке C2 число 1, а далее скопируем такую формулу.
=ЕСЛИ(ОСТАТ(СТРОКА();12);C2+1;C1)
В результате мы получим дополнительную графу в исходной таблице, которую так и назовем – НОМЕР ГРУППЫ.
Думается теперь вряд ли для кого станет проблемой отфильтровать группы и вывести их значения в отдельные ячейки.
Первый и второй случай, о которых сказано в начале, немного посложнее. Дело в том, что там номера нужных позиций идут не подряд, а раскиданы по списку. Тем не менее, это никак не помешает нам в работе.
Рассмотрим пример с банковскими операциями. Очевидно, что нам требуется вывести построчно элементы списка в три группы. Применим уже знакомую нам формулу нахождения остатка от деления номера строки ячейки с формулой на заданное число, и теперь мы будем использовать число 3. Так же создадим заголовок для исходной таблицы. НЕ будем изобретать велосипед, а зададим для колонки с исходными данными и для колонки с нашей формулой соответственно названия «Данные» и «Формула»
Мы видим, что в колонке «формула» поочередно повторяются значения 2, 0 и 1. Этот факт мы и будем использовать. Но вначале, чтобы избежать возможных проблем, скопируем содержимое ячеек с формулами, и вставим их в этот же диапазон, но уже в виде значений, избавившись тем самым от формул. Затем применим фильтр и оставим только те строи исходной таблицы, где содержится число 2. Напомним, что это ячейки, относящиеся к первой группе элементов.
Выделим все ячейки в графе данные вместе с заголовком. Как вариант – щелкнем по заголовку этого столбца и нажмем сочетание Ctrl+Shift + стрелка вниз. Теперь запускаем инструмент «выделить группу ячеек» (на вкладке «Главная» в конце с правой стороны в блоке «редактирование» раскрываем, нажав по кнопке справа, список «Найти и выделить», после чего выбираем в списке пункт , который так и называется – «выделить группу ячеек»)
В появившемся окне отметим переключатель «только видимые ячейки» и нажимаем «ОК». В результате Excel выберет только те ячейки из графы «данные», которые соответствую значению 2 из графы формула. Копируем выделенные ячейки и вставляем их в отдельный столбец за нашей таблицей. Убираем фильтр, и убеждаемся, что наши значения скопировались в отдельный диапазон без пустых ячеек между скопированными данными.
Аналогичным образом последовательно отфильтровываем графу «Формула» по значениям 0 и 1, выделяем только видимые ячейки для каждого результата фильтрации в графе «Данные» и копируем из в колонки E и F соответственно.
Готово! Вся операция заняла менее пяти минут, и при этом не потребовалось применение макросов и дополнительных надстроек.
Осталось только удалить исходные данные и задать необходимые заголовки в итоговой таблице, а также настроить при необходимости ширину ее столбцов и формат ячеек.
Вторая задача решается точно так же, как и первая задача, рассмотренная нами только что, только в качестве делителя надо указать число 5, так как количество граф в будущей таблице равно именно пяти – ФИО, адрес, дополнительный адрес, телефон и сумма. Предлагаем решить эту задачу самостоятельно, используя учебный файл, который можно скачать по ссылке в конце урока. Кроме этого, вы можете посмотреть учебное видео, посвященное изученной на данном занятии теме.
Стоит отметить, что рассмотренный нами способ имеет один недостаток. При достаточно большом количестве групп, на которые надо разделить исходный диапазон ячеек, копировать вручную каждую группу отдельно становится неудобно. На следующем занятии мы поговорим о том, как исправить ситуацию, применив для автоматизирования СТОЛБЕЦ. Текущее же наше занятие подошло к концу. Желаем всем удачной работы и хорошего дня. Если наш материал помог вам справиться с проблемами, не забудьте отметить это в комментариях или лайком при просмотре видеоверсии урока по ссылке ниже
Ссылка на учебный файл.
