Как расширить диапазон таблицы в excel

Foodband

Как в офисе.

Увеличение таблицы в Microsoft Excel

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

Увеличение таблиц

Сразу нужно сказать, что увеличить таблицу можно двумя основными способами: увеличением размеров отдельных её элементов (строк, столбцов) и путем применения масштабирования. В последнем случае табличный диапазон будет увеличен пропорционально. Данный вариант разделяется на два отдельных способа: масштабирование на экране и на печати. Теперь рассмотрим каждый из этих способов более подробно.

Способ 1: увеличение отдельных элементов

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

Начнем с увеличения строк.

    Устанавливаем курсор на вертикальной панели координат на нижней границе той строки, которую планируем расширить. При этом курсор должен преобразоваться в двунаправленную стрелку. Зажимаем левую кнопку мыши и тянем вниз до тех пор, пока установленный размер строчки нас не удовлетворит. Главное не перепутать направление, ведь если потянуть его вверх, то строка сузится.

Расширение строки в Microsoft Excel

Строка расширена в Microsoft Excel

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

    Зажимаем левую кнопку мыши и выделяем на вертикальной панели координат сектора тех строк, которые хотим расширить.

Выделение строк в Microsoft Excel

Расширение всех строк таблицы в Microsoft Excel

Все строки таблицы расширины в Microsoft Excel

Существует также и другой вариант расширения строк.

    Выделяем на вертикальной панели координат сектора той строки или группы строк, которые требуется расширить. Кликаем по выделению правой кнопкой мыши. Запускается контекстное меню. Выбираем в нем пункт «Высота строки. ».

Переход в окно изменения высоты ячеек в Microsoft Excel

Окошко высоты строк в Microsoft Excel

Высота строк увеличена в Microsoft Excel

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

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

Расширение столбца в Microsoft Excel

Столбец расширен в Microsoft Excel

Как и в случае со строками, существует вариант группового увеличения ширины столбцов.

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

Выделение столбцов в Microsoft Excel

Расширение всех столбцов таблицы в Microsoft Excel

Ширина столбцов увеличена в Microsoft Excel

Кроме того, имеется вариант увеличения столбцов путем введения их конкретной величины.

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

Переход в окно изменения ширины ячеек в Microsoft Excel

Естественно, что если мы хотим произвести расширение таблицы, то размер ширины должен быть указан больше текущего. После того, как вы указали необходимую величину, следует нажать на кнопку «OK».

Окошко ширины столбцов в Microsoft Excel

Все столбцы таблицы расширены в Microsoft Excel

Способ 2: масштабирование на мониторе

Теперь узнаем о том, как увеличить размер таблицы путем масштабирования.

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

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

Перетягивание ползунка масштабирования в Microsoft Excel

Либо нажать на кнопку в виде знака «+» справа от этого ползунка.

Нажатие кнопки масштабирования в Microsoft Excel

Маштаб изменен на мониторе в Microsoft Excel

Кроме того, отображаемый на мониторе масштаб можно изменить следующим образом.

    Перемещаемся во вкладку «Вид» на ленте Excel. Щелкаем по кнопке «Масштаб» в одноименной группе инструментов.

Переход к масштабированию в Microsoft Excel

Установка предуставновленного масштаба в окне масштабирования в Microsoft Excel

Но в этом же окне существует возможность установить собственный, пользовательский масштаб. Для этого нужно выставить переключатель в позицию «Произвольный» и в поле напротив этого параметра занести то числовое значение в процентах, которое будет отображать масштаб табличного диапазона и листа в целом. Естественно, чтобы произвести увеличение вы должны вписать число, превышающее 100%. Максимальный порог визуального увеличения таблицы 400%. Как и в случае использования предустановленных вариантов, после внесения настроек следует нажать на кнопку «OK».

Установка произвольного масштаба в окне масштабирования в Microsoft Excel

Произвольный масштаб установлен в Microsoft Excel

Довольно полезным является инструмент «Масштаб по выделенному», который позволяет увеличить масштаб таблицы ровно настолько, чтобы она полностью вписалась в область окна Excel.

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

Выделение таблицы в Microsoft Excel

Переход в масштаб по выделенному в Microsoft Excel

Таблица масштабирована по выделению в Microsoft Excel

Foodband

Кроме того, масштаб табличного диапазона и всего листа можно увеличить, зажав кнопку Ctrl и прокрутив колесико мышки вперед («от себя»).

Способ 3: изменение масштаба таблицы на печати

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

    Перемещаемся во вкладку «Файл».

Переход во вкладку Файл в Microsoft Excel

Переход в раздел Печать в Microsoft Excel

Переход к регулировке масштабирования в Microsoft Excel

Переход к параметрам настраиваемого масштабирования в Microsoft Excel

Окно параметров страницы в Microsoft Excel

Область препросмотра в Microsoft Excel

Печать страницы в Microsoft Excel

Изменить масштаб таблицы при печати можно и другим путем.

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

Масштаб страницы для печати в Microsoft Excel

Масштаб страницы для печати увеличен в Microsoft Excel

Урок: Как распечатать страницу в Экселе

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

Динамический диапазон с автоподстройкой размеров

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

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

Все это в сумме не даст вам скучать 😉

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

dynamic_range1.png

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .

dynamic_range3.png

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

  • Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
  • Таблица1[#Все] – ссылка на всю таблицу целиком (A1:D5)
  • Таблица1[Питер] – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
  • Таблица1[#Заголовки] – ссылка на «шапку» с названиями столбцов (A1:D1)

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

dynamic_range4.png

Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:

dynamic_range5.png

Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.

Способ 2. Динамический именованный диапазон

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

dynamic_range6.png

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

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!

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

dynamic_range7.png

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» — последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

dynamic_range8.png

Формируем ссылку с помощью ИНДЕКС

Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(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) :

Динамический именованный диапазон

Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.

Foodband

Ссылка на основную публикацию