Набор значений, которые могут быть введены в ячейку excel, ограничен: решение проблемы

Надстройка для сортировки данных в Excel

Microsoft Excel предоставляет пользователям достаточно большой набор стандартных средств для сортировки значений различного типа, но встречаются задачи, которые решать при помощи стандартных инструментов либо неудобно, либо долго, например, сортировать каждую строку/каждый столбец таким образом, чтобы сортировка производилась только в пределах строки/столбца и не затрагивала соседние ячейки.

Чтобы в Microsoft Exel упорядочить по алфавиту, необходимо выделить нужные вам ячейки и на панели инструментов нажать quot;Сортировка и фильтрquot;. И затем выбираете как вам нужно сортировать текст от А до Я или наоборот от Я до А.

Чтобы работать с определенной областью ячеек и данных в программе Microsoft Excel, для начала нужно ее выделить мышью, чтобы область затемнилась. Теперь на панели инструментов необходимо выбрать quot;А Я сортировка и фильтрыquot; и далее выбирать конкретные условия сортировки.

Списки в таблице Эксель Exel можно упорядочить по алфавиту от А до Я и наоборот: от Я до А. Для начала кликните по заголовку стобца — так он весь разом выделится. Затем нажимаем на кнопку quot;Сортировка и фильтрquot; вверху. Выбираем нужную сортировку. Аналогично поступаем, если столбцов несколько.

Если нужно просто упорядочить данные по алфавиту, то самый простой способ выбрать Фильтр quot;Сортировка от А до Яquot;, при этом данные сами упорядочатся.

При этом, если у вас много столбиков система может запросить уточнение: расширить области форматирования или нет. При выборе расширения областей данные в выделенной ячейке станут по алфавиту, а связанные с ними ячейки автоматом будут сортированы напротив той же ячейки что и до форматирования.

При сортировке в пределах указанного выделения будет поддаваться фильтру только выделенная область.

Для того что бы упорядочить по алфавиту список в Excel, необходимо выделить нужный столбец и нажать кнопку quot;АЯ сортировка и фильтрquot;, которая находится на панели управления, вкладка Главная, как показано на картинке и выбрать необходимую сортировку — от А до Я или наоборот от Я до А

Microsoft Excel очень удобная программа для создания и редактирования данных в виде таблицы. Конечно же в программе есть функция сортировки данных по алфавиту.

Для того чтобы упорядочить данные в Эксель по алфавиту необходимо:

2) нажать кнопку quot;Сортировка и фильтрquot; (обычно находится справа, имеет вид воронки с буквам А Я),

3) в сплывшем меню выбираем quot;Сортировка от А до Яquot;.

4) для того чтобы данные в соседних столбцах отсортировались в соответствии с изменениям в первом столбце в сплывшем окне quot;Обнаружены данные вне указанного диапазонаquot; выбираем quot;автоматический расширить выделенный диапазонquot; и нажимаем quot;Сортировкаquot;.

Сортировка по имени в таблице Exel от Microsoft довольно простая процедура.

Выделяем желаемый столбец. Жмем на панели инструментов вверху справа кнопку quot;Сортировка и фильтрquot;. Кликаем по выпавшей строке меню quot;Сортировка от А до Яquot;. вот и вся задача.

Упорядочить по алфавиту в Excel очень просто, нужно только воспользоваться кнопкой quot;Сортировка по возрастаниюquot;. Она выглядит как изображение двух букв quot;Аquot; и quot;Яquot; со стрелочкой сбоку.

Упорядочение происходит в пределах столбца. Нужно выделить необходимый столбец и нажать кнопку quot;Сортировка по возрастанию quot;. После этого сразу все слова в столбце расположатся по алфавиту.

Иногда после нажатия этой кнопки выскакивает диалоговое окно, которое спрашивает, что делать с соседними столбцами: или оставлять там все, как есть, или переместить эти данные в соответствии изменения расположения по алфавиту . Вам только нужно выбрать пригодный для вас вариант. Это как правило опция: quot;Автоматически расширить выделенный диапазонquot;.

Сортировать содержимое электронных таблиц в этой программе очень просто. Сначала нужно выделить диапазон, которые Вы хотите сортировать. Теперь можно либо нажать клавишу на панели quot;Сортировка и фильтрquot; и выбрать от чего к чему сортировать. Либо нажать правую кнопку мыши так же выбрать эту функцию.

Как в экселе разместить по алфавиту. Как сортировать по алфавиту в excel — сортировка по алфавиту в excel
В данном материале мы расскажем о том, как сделать список по алфавиту в табличном процессоре Excel. Материал будет полезен для пользователей всех современных версий этой программы, среди которых Excel 2007, 2010, 2013 и 2016.

Удаление раскрывающегося списка

1. ​ то два варианта.​ там, это сделала​

   ​ вводимых данных. Находясь​.​ любой ячейки. В​ имеющимся списочным элементам​​ ввести в ячейку​​после символа​

2. ​ использовать перечень, если​​«Список»​​ из имеющихся данных,​​ как создать диапазон​​, а затем —​

3. ​Ненужный раскрывающийся список на​​ If End Sub​​ в выпадающий список?».​​ Получаем следующий вид​​1. В поле​

4. ​ сотрудница, которая работала​​ в любом разделе​​Также вместо этого действия​

​ раскрывшемся списке видим,​ было добавлено нужное​ любое значение, которое​«=»​ его преобразовать в​. После этого перемещаемся​ читайте в статье​​ и присвоить ему​​Всех​ листе можно удалить.​​Чтобы выбранные значения показывались​​Нажмем «Да» и добавиться​ диапазона:​​ “Источник” данные разделены​​ здесь до меня​​ указанного инструмента, нам​​ можно кликнуть по​​ что удаленный элемент​​ нам значение. Теперь​​ отсутствует в перечне,​​следует указать наименование​ «умную» таблицу. В​

​ к полю​ “Создание списка в​ имя, смотрите в​или​Windows macOS Online​ снизу, вставляем другой​

1. ​ еще одна строка​Ставим курсор в ячейку,​

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

2. ​ то данное действие​​ оператора –​​ такой таблице будет​​«Источник»​​ Excel без повторов”.​

3. ​ статье «Диапазон в​​Этих же​​ ​​ код обработчика.Private Sub​​ со значением «баобаб».​

4. ​ где будет находиться​​ Просто добавляете новое​​ она это сделала.​

​ действие — нажать​ кнопкой мышки и​Что же делать, если​ можно выбрать, чтобы​ заблокируется. При этом,​«ДВССЫЛ»​ легче менять значения,​​. Тут нужно указать​​Как удалить выпадающий список​ Excel» здесь.​​. Далее повторите действия,​​Выделите ячейку, в которой​ Worksheet_Change(ByVal Target As​​Когда значения для выпадающего​​ выпадающий список. Открываем​​ значение в список.​​ А через проверку​​ на кнопку​​ остановиться на варианте​​ значения добавлялись в​​ вставить в элемент​ если вы вносили​

​. После этого в​ тем самым автоматически​ группу наименований, предназначенную​ Excel.​Список можно написать​ описанные выше.​

1. ​ есть раскрывающийся список.​ Range) On Error​

2. ​ списка расположены на​​ параметры инструмента «Проверка​​2. В поле​​ данных не получается,​​«Очистить всё»​

3. ​«Копировать»​​ окно проверки данных​​ таблицы.​​ сообщение предупреждения в​​ скобках в качестве​

4. ​ изменяя списочные элементы.​​ для использования в​​Нужно выделить ячейки​

​ и в столбце​Если вместо удаления раскрывающегося​Если вы хотите удалить​ Resume Next If​ другом листе или​ данных» (выше описан​

support.office.com>

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

1. Создадим первый выпадающий список, куда войдут названия диапазонов.
2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Как нам это может пригодиться?

Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.

Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых необходимо указать только одно.

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

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

Способ 2. Применяем именованный диапазон.

Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.

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

Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул.

Так вы присвоите какое-то имя этому диапазону Excel.

Создадим в С2 перечень значений. В качестве источника для него укажем выражение

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

Перечень ещё можно отсортировать, чтобы удобно было пользоваться.

Главное неудобство пользования таким списком заключается в том, что используемый нами диапазон — статический. Автоматически его размеры измениться не могут. Согласитесь, не слишком удобный и технологичный способ. Слишком много ручных операций.

А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.

Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(«Е2:Е9»)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Уловки для создания раскрывающихся списков, позволяющих выбрать несколько элементов

Наш приглашенный блоггер Дебра Далглиш — консультант, специализирующийся на программировании и разработке Microsoft Office.Каждую неделю она делится своими знаниями об Excel в блоге Contextures и ежегодно получает награду Excel MVP с 2001 года.

На Фабрике мистической магии * одно из преимуществ — то, что офисные сотрудники могут исчезать в полдень по большинству пятниц. Однако офис остается открытым, поэтому по крайней мере два человека дежурят, чтобы отвечать на телефонные звонки и приветствовать посетителей.

Перед волшебством

Чтобы отслеживать, кто будет оставаться в офисе каждую неделю, Ди, менеджер офиса, создал файл Excel.Этот файл содержит список имен сотрудников и список недель. Ди создал раскрывающийся список имен сотрудников на каждую неделю.

Выпадающий список хорошо подходит для выбора одного имени, но если Ди выбирает второе имя в ячейке, оно перезаписывает первое имя.

Добавьте волшебство

Чтобы позволить Ди выбрать несколько имен в каждой ячейке, мы добавим немного магии на ее рабочий лист с помощью нескольких строк кода VBA. Код сохраняет старое имя в ячейке и добавляет запятую, за которой следует новое имя.

Давайте взглянем на код и посмотрим, как вы можете использовать этот трюк в своих таблицах.

За занавеской

Чтобы увидеть код для рабочего листа, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код . Это откроет Visual Basic Explorer (VBE).

В списке проектов слева указана рабочая книга FridayStaffName, а лист Friday_Staff выделен. Справа мы видим код события Worksheet_Change для этого рабочего листа.Этот код запускается автоматически, если Ди вносит изменения в лист.

Скопируйте код в свой файл

Даже если вы ничего не знаете о программировании, вы можете скопировать этот код и использовать его в своей книге. Есть одна строка кода, которую вы можете изменить, если раскрывающийся список отсутствует в столбце B.

В коде Target относится к ячейке, которую вы изменили. На снимке экрана ниже код проверяет, в каком столбце находится эта целевая ячейка.Затем, если это был столбец 2 (B), остальная часть кода будет выполнена.

Вместо использования столбца 2 вы можете изменить его число или использовать несколько столбцов, например: Case 2, 5, 6

Пусть волшебство работает на вас

С помощью этого небольшого волшебства, добавленного к ее рабочему листу, Ди может быстро и легко выбрать офисный персонал для дневных смен в пятницу. На телефоны ответят, пока все отдыхают.

Вы можете загрузить образец файла, чтобы увидеть, как он работает, а также добавить этот удивительный трюк в свои рабочие книги: Загрузите файл образца имен сотрудников Friday

* Mystical Magic Factory — вымышленная компания, но эта тема блога основана на реальной проблеме клиента Contextures.

Дополнительная информация в раскрывающихся списках Excel:

Как создать раскрывающийся список ячеек в Excel 2010

Сортировка по нескольким столбцам Excel

Как настроить сортировку в Excel? Допустим мы хотим сортировать сотрудников по дате рождения (от старых к новым) в соответствии с каждым городом (места рождения). Для этого:

1. Сначала для данного примера лучше сбросить сортировку предыдущих примеров. Для этого отсортируйте таблицу по первому столбцу А (№п/п) – по возрастанию.
2. Перейдите на любую ячейку исходной таблицы и выберите инструмент: «ГЛЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Настраиваемая сортировка». Появиться диалоговое окно в котором можно задать несколько условий для сортировки таблицы.
3. В первом выпадающем списке выбираем заголовок столбца по которому будут сортированы данные в первую очередь. В данном примере – это «Место рождения». Второй выпадающий список оставим без изменений – «Значение», а в третьем указываем тип сортирования «От А до Я».
4. Нажмите на кнопку «Добавить уровень» и в следующем условии указываем новые критерии. 1 – «Дата рождения», 2 – «Значение», 3 – «От старых к новым». И нажмите на кнопку ОК.

Таблица приобрела следующую структуру:

Сначала ее значения отсортированы относительно названий городов в алфавитном порядке. Далее относительного каждого города распределены даты рождения от старых к новым.

Если мы работаем только с фрагментом таблицы где нет заголовков столбцов, а только лишь диапазон ячеек с текущими значениями, как показано ниже на рисунке:

Тогда в диалоговом окне инструмента «Настраиваемая сортировка» следует отключить опцию «Мои данные имеют заголовки столбцов». В результате чего в качестве критериев для каждого первого выпадающего списка секции «Столбец» в каждом уровне, будут использованы заголовки столбцов рабочего листа Excel (Столбец А, Столбец B, C и т.д.). А при применении всех параметров в сортировку будут включены все строки и ячейки исходного диапазона (фрагмента таблицы), что является правильным результатом в данном случаи.

Примечание. Кнопка вызова инструмента для настраиваемой сортировки так же доступна на закладке «ДАННЫЕ»-«Сортировка».

Читайте так же: Сортировка по месяцам в Excel.

Таблицы данных, которые мы получаем от разных сотрудников в большинстве случаев не упорядочены и работать с ними без организации – сложно. Возникает необходимость отсортировать: текстовые значение по алфавиту, числовые – по возрастанию, а даты от старых до новых. Сортировать вручную все столбцы по отдельности – это не наилучшее решение для данной задачи. Особенно если столбцов более тысячи. Поэтому лучше воспользоваться специальным встроенным инструментом Excel – настраиваемой сортировкой. Данный инструмент автоматическое сортирует данные одновременно по нескольким столбцам. Он настолько эластичен, что его можно применить практически для любой сортировки с любым уровнем сложности.

2 метода:Отсортируйте по алфавиту в два кликаУпорядочить по алфавиту при помощи сортировки

Excel — мощная программа для создания таблиц, которые служат для хранения и управления текстовой и цифровой информацией. Сортировка по алфавиту позволяет быстро находить и ссылаться на данные. И это только одна функция в числе огромного количества возможностей Excel. Ячейки в Excel можно отсортировать двумя способами.

Метод 1 Отсортируйте по алфавиту в два клика

Введите текст, который хотите отсортировать в ячейки одной колонки.

Выделите текст, который хотите отсортировать по алфавиту. Чтобы выделить область, кликните правой кнопкой мыши на верхней ячейке и перетащите выделение до последней. Либо можете выделить всю колонку, просто кликнув на название ячейки по горизонтали с буквой этой колонки.

Найдите иконки сортировки «АЯ» или «ЯА» в стандартной панели инструментов, на вкладке “Данные”.

Нажмите сортировать «АЯ», чтобы отсортировать по алфавиту в порядке возрастания, и «ЯА» — чтобы отсортировать в порядке убывания. Выделенные ячейки буду отсортированы.

Если не можете найти иконку сортировки «АЯ», то можете добавить стандартную панель инструментов, зайдя на вкладку “Вид”, выбрав “Панель инструментов” и отметив “Стандартная”. Теперь стандартная панель инструментов должна быть видна, и на ней присутствует иконка сортировки «АЯ».

Подстановка динамических данных Excel

Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив. Выделите его и на вкладке «Главная» выберите любой стиль таблицы. Обязательно поставьте галочку внизу. Вы получите такое оформление. Создайте активный элемент, как было описано выше. В качестве источника введите формулу

=ДВССЫЛ("Таблица1")

Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое. Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

Попробуем увеличить количество городов. Обратная процедура — подстановка данных из выпадающего списка в таблицу Excel, работает очень просто. В ячейку, куда надо вставить выбранное значение из таблицы, введите формулу:

 =Адрес_ячейки

Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу

 =D1

Как в Эксель сделать выпадающий список при помощи инструментов разработчика

Однако рассмотренный способ не единственный. Как вариант допускается использовать функционал, который называется инструментами разработчика. По-другому он называется ActiveX.

При установке данная функция не активна. Поэтому первым делом нужно ее включить.

Чтобы это сделать нам нужно, переключить верхнее меню на вкладку «Файл» и выбрать пункт «Параметры».

• После открытия дополнительного окна нам нужно обратить свой взор на подпункт «Настройка ленты» и активизировать флажок рядом со строкой «Разработчик».
• Подтверждаем свои действия, кликнув по кнопке «OK».

• Результатом этого действия станет появление вкладки с названием «Разработчик».
• После этого переходим в нее.

Продолжение

Дальнейшее действие похоже на предыдущий вариант.

• Выделяем массив с данными, которые должны превратиться в выпадающее меню.
• После этого в разделе «Разработчик» кликаем на кнопку «Вставить» и выбираем в появившемся списке «Поле со списком».

• После этого выбираем ячейку, где необходимо поместить кнопку для открытия перечня информации.
• При этом мы увидим, что в правой части ячейки появилась соответствующая кнопка.

Дальше нам понадобится режим конструктора. В котором нам интересна кнопка «Свойства элемента управления».

• Нам будет показано окно, в котором осуществляется управление списком.
• Переводим свой взгляд на графу «ListFillRange», где указываем диапазон с информацией.

Кликаем на пустую ячейку, и последовательно переходим «Объект ComboBox» — «Edit».

Таким образом, мы получили выпадающий список вторым способом.

Двухуровневый выпадающий список в Excel

Имеется несколько способов создания выпадающего списка. Выбор одного из них зависит от структуры имеющихся у вас данных.

Первый способ создания двухуровнего списка

Первый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.

Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

1. Выберите ячейку, в которую будете вставлять выпадающий список;
2. Переходим на вкладку ленты Данные;
3. Выбираем команду Проверка данных;
4. В выпадающем списке выбираем значение Список;
5. В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1»).

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1»). Ячейка F2 в данном случае — значение первого выпадающего списка.

Второй способ создания двухуровнего списка

Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается. Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы

Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel

Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, , ), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
• Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
• Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
• — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
• — 1 — так как это ширина нашего столбца с подгруппами.