Процедуры из статьи доступны в !SEMTools Lite и Pro.
Изменить диапазон ячеек в формуле Excel проще всего так: выделите ячейку с формулой, нажмите F2 — диапазоны подсветятся цветными рамками, — и перетащите угол рамки, чтобы расширить или сузить диапазон, либо исправьте адрес вручную. Диапазон «умной» таблицы меняют через Конструктор таблиц → Размер таблицы, именованный — через Формулы → Диспетчер имён. А переставить элементы диапазона, отсортировать или свести его помогут процедуры !SEMTools из меню ИЗМЕНИТЬ → Диапазон.
Под трансформацией диапазона понимается изменение порядка его элементов без изменения самих элементов, или "сжатие" диапазона путём построения сводной.
Функции, связанные с теми или иными трансформациями диапазонов, находятся в разделе "Изменить" надстройки: ИЗМЕНИТЬ -> Диапазон. В число этих функций входят:
- Сортировка ячеек по условию;
- Вставить N строк с шагом
- Быстрая сводная таблица.
Как изменить диапазон ячеек в формуле
- Выделите ячейку с формулой и нажмите F2 (или щёлкните в строке формул).
- Каждый диапазон в формуле подсветится рамкой своего цвета. Потяните за угол рамки, чтобы расширить диапазон, или за край, чтобы сдвинуть его.
- Либо исправьте адрес вручную: =СУММ(A2:A10) → =СУММ(A2:A100).
- Нажмите Enter. Чтобы изменить диапазон во всех похожих формулах сразу, используйте «Найти и заменить» (Ctrl+H) с параметром «Область поиска: формулы».
Чтобы диапазон не приходилось расширять при добавлении строк, оформите данные как таблицу (Ctrl+T) — ссылки вида Таблица1[Сумма] растут вместе с ней — или ссылайтесь на весь столбец: =СУММ(A:A).
Как изменить диапазон таблицы и именованного диапазона
Умная таблица. Щёлкните в любой ячейке таблицы, на вкладке «Конструктор таблиц» (в старых версиях — «Работа с таблицами → Конструктор») нажмите «Размер таблицы» и укажите новый диапазон. Можно также потянуть маркер в правом нижнем углу таблицы.
Именованный диапазон. Откройте «Формулы → Диспетчер имён» (Ctrl+F3), выберите имя и исправьте адрес в поле «Диапазон» внизу окна.
Сводная таблица. «Анализ сводной таблицы → Источник данных → Изменить источник данных». Диаграмма — правой кнопкой → «Выбрать данные».
Формулы для изменения диапазонов
Многие преобразования диапазона можно сделать формулой, не меняя исходные данные. Ниже — по формуле на каждую задачу раздела.
Диапазон, который расширяется сам
СМЕЩ строит диапазон от A2 высотой в число заполненных ячеек столбца. Новое значение в A6 сразу попадёт в сумму — править формулу не нужно.
| A | B | C | |
|---|---|---|---|
| 1 | Сумма | Итого | |
| 2 | 100 | 1000=СУММ(СМЕЩ(A2;0;0;СЧЁТЗ(A:A)-1)) | |
| 3 | 200 | ||
| 4 | 300 | ||
| 5 | 400 |
СЧЁТЗ(A:A) считает и заголовок, поэтому из него вычитается 1. Тот же эффект без формул даёт умная таблица (Ctrl+T).
В английском Excel: =SUM(OFFSET(A2,0,0,COUNTA(A:A)-1))
Отсортировать диапазон формулой
В Excel 365 СОРТПО выводит отсортированную копию таблицы, а исходный порядок не меняется. Сортировку по длине текста, количеству слов и другим условиям смотрите в статье сортировка ячеек по условию.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Товар | Продажи | Товар | Продажи | |
| 2 | Чай | 120 | Кофе=СОРТПО(A2:B5;B2:B5;-1) | 300 | |
| 3 | Кофе | 300 | Вода | 150 | |
| 4 | Сок | 80 | Чай | 120 | |
| 5 | Вода | 150 | Сок | 80 |
−1 в третьем аргументе — сортировка по убыванию, 1 — по возрастанию.
В английском Excel: =SORTBY(A2:B5,B2:B5,-1)
Вставить пустую строку через одну
Формула в соседнем столбце раскладывает список через строку: в нечётных строках — очередное значение, в чётных — пусто. Чтобы вставлять по N пустых строк, замените 2 на N+1.
| A | B | C | |
|---|---|---|---|
| 1 | Месяц | С пустыми строками | |
| 2 | Январь | Январь=ЕСЛИ(ОСТАТ(СТРОКА(A1);2)=0;"";ИНДЕКС($A$2:$A$4;(СТРОКА(A1)+1)/2)) | |
| 3 | Февраль | =ЕСЛИ(ОСТАТ(СТРОКА(A2);2)=0;"";ИНДЕКС($A$2:$A$4;(СТРОКА(A2)+1)/2)) | |
| 4 | Март | Февраль=ЕСЛИ(ОСТАТ(СТРОКА(A3);2)=0;"";ИНДЕКС($A$2:$A$4;(СТРОКА(A3)+1)/2)) | |
| 5 | =ЕСЛИ(ОСТАТ(СТРОКА(A4);2)=0;"";ИНДЕКС($A$2:$A$4;(СТРОКА(A4)+1)/2)) | ||
| 6 | Март=ЕСЛИ(ОСТАТ(СТРОКА(A5);2)=0;"";ИНДЕКС($A$2:$A$4;(СТРОКА(A5)+1)/2)) |
Протяните формулу вниз на нужное число строк и замените результат значениями.
Сводка без дублей формулой
УНИК оставляет каждое значение один раз, а СЧЁТЕСЛИ со ссылкой C2# считает, сколько раз оно встречалось. Без формул — быстрая сводная таблица.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Город | Город | Сколько | |
| 2 | Москва | Москва=УНИК(A2:A6) | 3=СЧЁТЕСЛИ(A2:A6;C2#) | |
| 3 | Казань | Казань | 1 | |
| 4 | Москва | Тула | 1 | |
| 5 | Тула | |||
| 6 | Москва |
Знак # после C2 — ссылка на весь результат УНИК, сколько бы строк он ни занял. Обе функции есть в Excel 365 и 2021.
В английском Excel: =COUNTIF(A2:A6,C2#)
Повернуть диапазон: строки в столбцы
ТРАНСП меняет строки и столбцы местами. Без формул — копирование и «Специальная вставка → Транспонировать».
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Месяц | Январь | Февраль | Месяц=ТРАНСП(A1:C2) | Выручка | |
| 2 | Выручка | 100 | 120 | Январь | 100 | |
| 3 | Февраль | 120 |
Результат связан с исходником: поменяйте число в B2 — изменится и E2.
В английском Excel: =TRANSPOSE(A1:C2)
Изменение диапазонов в Excel — частые вопросы
Как изменить диапазон ячеек в формуле?
Выделите ячейку, нажмите F2 и перетащите цветную рамку диапазона или исправьте адрес вручную.
Как изменить диапазон таблицы в Excel?
Конструктор таблиц → «Размер таблицы» или перетащите маркер в правом нижнем углу таблицы.
Как поменять диапазон во многих формулах сразу?
Через Ctrl+H с параметром «Область поиска: формулы»: найти A2:A10, заменить на A2:A100.
Как сделать, чтобы диапазон расширялся сам?
Оформите данные как таблицу (Ctrl+T) и ссылайтесь на её столбцы — диапазон будет расти при добавлении строк.
Отсортировать ячейки по условию, вставить строки с шагом или свести диапазон в таблицу без дублей можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как изменить диапазон в Excel
| Что меняем | Где |
|---|---|
| Диапазон в формуле | F2 → цветная рамка или правка адреса |
| Диапазон во многих формулах | Ctrl+H, «Область поиска: формулы» |
| Умная таблица | Конструктор таблиц → Размер таблицы |
| Именованный диапазон | Формулы → Диспетчер имён (Ctrl+F3) |
| Порядок и структуру диапазона | ИЗМЕНИТЬ → Диапазон в !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: сортировка ячеек по условию и быстрая сводная таблица. А чтобы разложить список из ячейки по строкам, используйте «Развернуть по разделителю».