Excel расширять форматы и формулы в диапазонах данных
Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:
- ссылки в формулах отчетов, которые ссылаются на нашу таблицу
- исходные диапазоны сводных таблиц, которые построены по нашей таблице
- исходные диапазоны диаграмм, построенных по нашей таблице
- диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных
Все это в сумме не даст вам скучать ;)
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.
Способ 1. Умная таблица
Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):
Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
Теперь можно использовать динамические ссылки на нашу «умную таблицу»:
Такие ссылки замечательно работают в формулах, например:
=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»
=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)
Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:
Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.
При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:
Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.
Способ 2. Динамический именованный диапазон
Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:
Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.
Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.
Ищем последнюю ячейку с помощью ПОИСКПОЗ
ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.
Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!
Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:
Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.
Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:
Формируем ссылку с помощью ИНДЕКС
Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:
ИНДЕКС(диапазон; номер_строки; номер_столбца)
Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.
Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.
И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:
=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))
Создаем именованный диапазон
Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :
Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.
В процессе работы с данными в Excel иногда заранее не известно, сколько данных будет собрано в той или иной таблице. Поэтому мы не всегда можем заранее знать какой диапазон должно охватывать имя. Ведь количество данных может изменяться. Для решения данной задачи следует автоматически изменить именованный диапазон используемых ячеек, в зависимости от количества введенных данных.
Как в Excel сделать автоматическое изменение диапазона
Допустим, у нас есть объект инвестирования, по которому мы хотим знать суммарную прибыль за весь период его использования. Заранее мы не можем определить период использования инвестиционного объекта. Но нам нужно постоянно следить за общим доходом, который приносит нам данный объект инвестирования.
Сформируйте отчет по доходности нашего объекта инвестирования, так как показано на рисунке:
Данную задачу можно было-бы решить путем суммирования целого столбца B и после появления в нем записей общая сумма изменялась бы автоматически. Но это некорректный способ решения задач в Excel. Во-первых, нам нельзя будет использовать ячейки столбца B для ввода других данных. Во-вторых, так функция суммирования будет нерационально потреблять больше памяти. Это может привести к серьезным проблемам при работе с документом. Самым рациональным решением является использование динамических имен.
- Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
- Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
- Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах.
Теперь постепенно заполняя ячейки в столбце B, мы можем проследить, как изменяется охват ячеек именем «доход».
Функция СМЕЩ в Excel
Разберем более детально функции, которые мы вводили в поле диапазон при создании динамического имени.
Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка; смещение размера диапазона по строкам; смещение по столбцам; размер диапазона в высоту; размер диапазона в ширину):
- «Начальная ячейка» – указывает верхнюю левую ячейку, от которой будет динамически расширяться диапазон как вниз, так и вправо (при необходимости).
- «Смещение по строкам» – параметр определяет, на какое количество нужно смещать диапазон по вертикали от начальной ячейки (первого параметра). Значения могут быть нулевыми и отрицательными.
- «Смещение по столбцам» – параметр определяет, на какое количество нужно смещать по горизонтали от начальной ячейки. Значения могут быть даже нулевыми и отрицательными.
- «Размер диапазона в высоту» – количество ячеек, на которое нужно увеличить диапазон в высоту. По сути, название говорит само за себя.
- «Размер диапазона в ширину» – количество ячеек, на которое нужно увеличить в ширину от начальной ячейки.
Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.
Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.
Что определяет функция СЧЕТ
Функция =СЧЕТ($B:$B) автоматически считает количество заполненных ячеек в столбце B.
Таким образом, мы с помощью функции =СЧЕТ() и =СМЕЩ() автоматизируем процесс формирования диапазона для имени «доход», что делает его динамическим. Теперь еще раз посмотрим на нашу формулу, которой мы присвоили имя «доход»: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Читать данную формулу следует так: первый параметры указывает на то, что наш автоматически изменяемый диапазон начинается в ячейке B2. Следующие два параметра имеют значения 0;0 – это значит, что динамический диапазон не смещается относительно начальной ячейки B2. А увеличивается только его размер по вертикали, о чем свидетельствует 4-тый параметр. В нем находится функция СЧЕТ и она возвращает число равно количеству заполненных ячеек в столбце B. Соответственно количество ячеек по вертикали в диапазоне будет равно числу, которое нам даст функция СЧЕТ. А за ширину диапазона у нас отвечает последний 5-тый параметр, где находиться число 1.
Благодаря функции СЧЕТ мы рационально загружаем в память только заполненные ячейки из столбца B, а не весь столбец целиком. Данный факт исключает возможные ошибки связанные с памятью при работе с данным документом.
Динамические диаграммы в Excel
У нас есть динамическое имя, теперь создадим динамическую диаграмму для данного типа отчета:
- Выделите диапазон B2:B6 и выберите инструмент: «Вставка»-«Диаграммы»-«Гистограмма»-«Гистограмма с группировкой».
- Щелкните левой кнопкой мышки по любому столбцу гистограммы и в строке формул отобразится функция диаграммы: =РЯД().
- В строке формул измените параметры функции: =РЯД(Лист1!$B$1;;Лист1!$B$2:$B$7;1) на =РЯД(Лист1!$B$1;;Лист1!доход;1).
- В отчет добавьте новую запись в ячейки A8 - «июль», B8 - «77000» соответственно. И убедитесь, что на диаграмме автоматически добавился новый столбец.
Используя наше динамическое имя «доход» нам удалось создать автоматически изменяемую динамическую диаграмму, которая сама добавляет и отображает новые данные в отчете.
Для существующего именованного диапазона вам может потребоваться изменить размер или расширить его, чтобы включить больше столбцов и строк. В этой статье мы покажем вам три метода изменения размера существующего именованного диапазона в Excel.
Измените размер существующего именованного диапазона с помощью функции диспетчера имен
Чтобы изменить размер существующего именованного диапазона с помощью функции диспетчера имен в Excel, сделайте следующее.
1. Нажмите Формулы > Менеджер имен. Смотрите скриншот:
2. в Менеджер имен диалоговом окне выберите имя диапазона, размер которого вы хотите изменить, а затем щелкните значок кнопку.
3. в Имя менеджера - относится к выберите новый диапазон для этого имени диапазона, а затем закройте это поле.
4. Когда он вернется в Менеджер имен диалогового окна, нажмите кнопку, чтобы сохранить новый диапазон, и, наконец, нажмите кнопку Закрыть кнопку, чтобы закрыть диалоговое окно. Смотрите скриншот:
Вы можете видеть, что указанный именованный диапазон немедленно изменяется.
Изменить размер существующего именованного диапазона с помощью кода VBA
Помимо вышеуказанного метода, код VBA также может помочь вам изменить размер указанного именованного диапазона.
1. Нажмите другой + F11 клавиши одновременно, чтобы открыть Приложение Microsoft Visual Basic окно.
2. в Приложение Microsoft Visual Basic окна, нажмите Вставить > Модуль.
3. Вставьте ниже код VBA в окно модуля.
VBA: изменение размера именованного диапазона
Ноты:
1. «10, 6» в этой строке .RefersTo = .RefersToRange.Resize (10, 6) означает, что указанный именованный диапазон будет изменен, чтобы иметь 10 строк и 6 столбцов.4. После изменения переменных нажмите кнопку F5 ключ для запуска кода. Во всплывающем диалоговом окне введите имя диапазона, размер которого вы хотите изменить, в Имя поле, а затем щелкните OK кнопку.
Затем размер именованного диапазона немедленно изменяется в соответствии с указанными вами условиями.
Измените размер существующего именованного диапазона с помощью Kutools for Excel
Все названия диапазонов перечислены в Область переходов of Kutools for Excel. И вы можете легко изменить их размер в области навигации.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. После установки Kutools for Excel, Область переходов по умолчанию отображается в левой части Excel. Пожалуйста, нажмите на Имя менеджера, выберите имя диапазона, размер которого нужно изменить, а затем нажмите кнопку кнопку.
2. Во всплывающем Kutools for Excel диалоговом окне выберите новый диапазон для этого имени диапазона, а затем щелкните значок OK кнопку.
3. Затем нажмите в Область переходов чтобы завершить настройку.
Внимание: Если вы не видите Область переходов, Пожалуйста, нажмите Kutools > Навигация чтобы включить его.
Освободи Себя Область переходов of Kutools for Excel это мощная утилита, с ее помощью вы можете не только легко изменять размер именованного диапазона, но также создавать автоматический текст для будущего использования, легко переключаться между книгами и листами и так далее.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Во время работы в Эксель нередко возникают ситуации, когда содержимое ячейки полностью в ней не помещается, и требуется расширить ее границы. Давайте посмотрим, как это можно сделать в программе разными способами.
Метод 1: ручной сдвиг границы
Данный метод считается наиболее простым. Все что нужно сделать – вручную сдвинуть границу на панели координат (горизонтальной или вертикальной).
-
На горизонтальной панели координат наводим указатель мыши на правую границу столбца, расширение которого требуется выполнить. Когда появится черный плюсик со стрелками в обе стороны, зажав левую кнопку мыши, тянем линию границы вправо.
Аналогичное действие можно выполнить в отношении строки. На вертикальной панели координат наводим указатель на нижнюю границу той строки, которую хотим расширить. В данном случае должен появиться плюсик со стрелками вверх и вниз. Зажав левую кнопку мыши, тянем линию границы вниз.
Примечание: Расширять границы ячейки можно, используя только ее правую и нижнюю границы, т.к. левая и верхняя будут менять размеры соседних ячеек.
Метод 2: расширение границ нескольких строк или столбцов
Данный метод, как следует из названия заголовка позволяет выполнить одновременное расширение нескольких строк и столбцов. Для этого выполняем следующие действия:
- Выделяем требуемые диапазоны на панелях координат – вертикальной или горизонтальной (в нашем случае).
- Наводим указатель мыши на правую границу самой правой ячейки выделенного диапазона. Как только увидим уже знакомый крестик со стрелками в обе стороны, зажав левую кнопку мыши тянем границу вправо.
Аналогичным образом можно одновременно расширить границы нескольких строк. Только теперь нужно выполнить их выделение на вертикальной координатной панели и тянуть вниз нижнюю границу самой нижней строки выделенного диапазона.
Метод 3: указание точного размера ячеек
В Эксель, также, можно указать точный размер строки или столбца. По умолчанию в программе установлены следующие значения (в зависимости от версии программы могут отличаться, ниже приведены данные для Excel 2019):
- ширина ячейки – 8,43 ед. (можно увеличить до 225);
- высота ячейки – 15 ед. (можно увеличить до 409).
Итак, алгоритм действий следующий:
Для того, чтобы вручную указать высотку строк:
Метод 4: инструменты на ленте
Изменить размер ячейки можно, воспользовавшись инструментами на ленте программы.
Метод 5: расширение всех ячеек листа или книги
В некоторых случаях требуется расширить границы всех ячеек на листе или во всей книге. Вот как это делается:
Чтобы изменить размеры всех ячеек книги, а не отдельного листа, действуем следующим образом:
Метод 6: автоподбор высоты и ширины ячеек по содержимому
Этот метод довольно часто используется для оперативной настройки размера ячеек, в частности, для его увеличения.
Аналогичные действия можно выполнить для автоподбора высоты строк, выделив одну или несколько элементов на вертикальной панели координат, и дважды кликнув по нижней границе выделенной строки (или нижней границе любого элемента выделенного диапазона).
Метод 7: настройка содержимого под ширину столбца
- Сначала выделяем ячейку (диапазон ячеек), для которых требуется выполнить изменения. После этого щелчком правой кнопки мыши по выделенным элементам раскрываем меню, в котором выбираем “Формат ячеек” (или можно просто нажать Ctrl+1).
- Переключаемся во вкладку “Выравнивание”. В группе параметров “Отображение” ставим галочку напротив опции “автоподбор ширины”, после чего щелкаем OK.
- После этого размер шрифта ячеек, содержимое которых не умещается в заданных границах, будет уменьшен. Тут есть один нюанс – если символов будет слишком много, в результате применения данного метода, содержимое ячейки может стать нечитаемым.
Заключение
Таким образом, в программе Excel настроить высоту строк и ширину столбцов можно различными способами. Все зависит от того, какой из них наиболее применим в конкретной ситуации, и какой кажется наиболее удобным для пользователя.
Читайте также: