Нельзя изменить часть массива excel что делать

Обновлено: 06.07.2024

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

Правила редактирования формул массива

Когда формула массива помещена в одну ячейку, то ее редактирование в Excel обычно не представляет особой сложности. Здесь главное не забыть закончить редактирование комбинацией клавиш Ctrl+Shift+Enter.

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

Нельзя изменять содержимое одной ячейки, содержащей формулу массива. Но к каждой ячейке можно применить свое форматирование.
Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.
Нельзя перемещать ячейки, которые входят в формулу массива. Зато можно переместить весь массив.
Нельзя вставлять новые ячейки, в том числе строки и столбцы, в диапазон массива.
Нельзя использовать многоячеечные формулы массива в таблицах, созданных с помощью команды Таблица.

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

Выделение массива в Excel

Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это:

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

Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.

Текущий массив будет выделен:

Как удалить формулу массива

Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete.

Как отредактировать формулу массива

Чтобы отредактировать формулу массива, выполните следующие действия:

Изменение размеров формулы массива

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

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

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

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

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

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

Операции с массивами

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

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

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

Создание формулы

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

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

Координаты массива имеют вид адресов первой её ячейки и последней, разделенные двоеточием. Если диапазон двумерный, то первая и последняя ячейки расположены по диагонали друг от друга. Например, адрес одномерного массива может быть таким: A2:A7.

А пример адреса двумерного диапазона выглядит следующим образом: A2:D7.

Изменение содержимого массива

«OK»

Но что делать, если действительно нужно удалить или изменить формулу массива? В этом случае следует выполнить нижеуказанные действия.

Delete

Функции массивов

Наиболее удобно в качестве формул использовать уже готовые встроенные функции Excel. Доступ к ним можно получить через Мастер функций, нажав кнопку «Вставить функцию» слева от строки формул. Или же во вкладке «Формулы» на ленте можно выбрать одну из категорий, в которой находится интересующий вас оператор.

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

Правила ввода и редактирования функций, если они выводят результат сразу в несколько ячеек, те же самые, что и для обычных формул массива. То есть, после ввода значения обязательно нужно установить курсор в строку формул и набрать сочетание клавиш Ctrl+Shift+Enter.

Оператор СУММ

Одной из наиболее востребованных функций в Экселе является СУММ. Её можно применять, как для суммирования содержимого отдельных ячеек, так и для нахождения суммы целых массивов. Синтаксис этого оператора для массивов выглядит следующим образом:

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

Оператор ТРАНСП

Функция ТРАНСП является типичным оператором массивов. Она позволяет переворачивать таблицы или матрицы, то есть, менять строки и столбцы местами. При этом она использует исключительно вывод результата в диапазон ячеек, поэтому после введения данного оператора обязательно нужно применять сочетание Ctrl+Shift+Enter. Также нужно отметить, что перед введением самого выражения нужно выделить на листе область, у которой количество ячеек в столбце будет равно числу ячеек в строке исходной таблицы (матрицы) и, наоборот, количество ячеек в строке должно равняться их числу в столбце исходника. Синтаксис оператора следующий:

Оператор МОБР

Для того чтобы рассчитать определитель, применяется функция со следующим синтаксисом:

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Пример формулы массива

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

Не знаю, новичок вы в Excel или уже опытный пользователь, но коль судьба привела вас на эту страницу, в ходе прочтения вы:

узнаете все о формулах массива;
увидите множество примеров таких формул;
сможете начать активно их применять на практике.

Что такое формула массива

Формула массива (array formula) — это такая формула, в процессе вычисления которой создается один или несколько массивов. При этом не обязательно как результат вычисления, но и как промежуточный этап.

Будьте на 100% уверены: если формула

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

— это формула массива.

Слишком очевидно и все еще непонятно? Не беда — читаем далее.

На деле, прежде, чем говорить о формулах массива, нужно упомянуть сами массивы и их отличие от диапазонов. Т.к. многие не до конца понимают разницу.

Массивы в Excel

Массив (array) — это фиксированный набор элементов с фиксированным порядком. Иными словами, все элементы массива, включая их положение в нем, являются его неотъемлемой частью, их порядок и количество нельзя изменить.

Отличие массивов от диапазонов в Excel

В отличие от массива, диапазон (range) — это просто метод адресации к ячейкам в Excel, никак не связанный с самим их содержимым.

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

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

Если же на лист Excel выведен массив, ни сортировка строк, ни вставка строк и столбцов в середину диапазона, в который он выведен, не возможна — это нарушило бы само его определение.

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

Ошибка Excel при попытке нарушить целостность массива

Размерность массивов

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

Одномерные массивы (векторы) в свою очередь могут быть вертикальными и горизонтальными.

Одномерные и двумерные массивы в Excel

Трехмерные массивы — довольно редко используемая на практике сущность.

Виды массивов

По типам элементов в них:
По типу данных —

числовой массив (массив чисел)
строковый массив (текстовые значения)
булевый массив (значения ИСТИНА-ЛОЖЬ)
смешанного типа

По изменяемости элементов —

Методы создания массивов

Создать массив в Excel можно множеством способов. Ниже — подробнее с примерами. Все перечисленные выражения в них являются формулами массива, даже если не содержат в себе функций.

Массив констант, созданный вручную

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

При этом разделителем между элементами слева направо является точка с запятой, а разделителем строк — двоеточие.

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

Именованный массив констант

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

Массив цифр как в примере ниже, поможет быстро найти цифры в текстовых ячейках.

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

Используем именованный массив

С помощью функций

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

Единственное условие для создания массива с помощью функции — функция не должна обладать агрегирующим свойством. Например, СУММ, СРЗНАЧ не создадут массив при обращении к диапазону.

На этом сайте есть статья — как дописать символ, слово или текст к нескольким ячейкам. Там рассматриваются несколько способов, в том числе без создания дополнительного столбца. Но вот еще один — с помощью функции СЦЕПИТЬ и формулы массива:

Формула массива с функциями СЦЕПИТЬ и СИМВОЛ

Кавычки-ёлочки в этой формуле нам помогает создать функция СИМВОЛ.

С помощью математических операторов

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

Массивы в Excel, полученные с помощью математических операторов

С помощью операторов сравнения

Аналогично математическим операторам, массивы создаются при сравнении диапазонов с константой или значением ячейки. Результатом операции сравнения являются значения ИСТИНА или ЛОЖЬ.

Массивы в Excel, полученные с помощью операторов сравнения

С помощью других массивов

В двух предыдущих примерах массивы создавались на основе взаимодействия диапазона ячеек и некой константы.

Но можно создать массив и по принципу наоборот — на основе одной ячейки, произведя ее взаимодействие (операторами или функциями) с массивом констант.

Размер результирующего массива в таком случае будет аналогичен размеру массива констант.

Еще раз обратите внимание на разделители строк и столбцов в массивах. Столбцы разделяются точкой с запятой, строки — двоеточием.

С помощью комбинаций указанных методов

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

Вывод массива на лист

Для корректного вывода массива на лист нужно выделить диапазон ячеек эквивалентной размерности и размера, использовать метод создания массива (вручную или формулой), и нажать

На примере результирующий массив должен быть размером 5*5, но перед вводом формулы массива был выделен диапазон 6*6.

Ошибка НД при несоответствии размера диапазона и массива

Взаимодействие функций Excel с массивами

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

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

Из раздела математических функций к таковым также относятся ПРОИЗВЕД, СУММПРОИЗВ, СУММЕСЛИ, СУММЕСЛИМН.

Практически все статистические функции по природе берут на вход диапазон или массив и возвращают одно число. Наиболее популярные из них — СРЗНАЧ, МИН, МАКС, СЧЁТ, СЧЁТЗ, СЧЁТЕСЛИ.

Среди логических функций агрегирующим свойством обладают функция И и функция ИЛИ.

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

учитывают весь массив
выводят одно значение

ВЫБОР, ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ — эти функции поиска используются наиболее часто.

Формулы массива — примеры формул

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

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

Объединение ячеек

Выделите ячейки для объединения.

Выберите пункт Слияние в центре &.

Важно: В объединенной ячейке отображается содержимое только одной ячейки (верхней левой ячейки для языков с направлением письма слева направо или правой верхней ячейки для языков с направлением письма справа налево). Данные в других объединенных ячейках удаляются.

Отмена объединения ячеек

Нажмите стрелку вниз рядом с кнопкой Объединить и поместить в центре.

Выберите команду Отменить Объединение ячеек.

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

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

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

В приведенном ниже примере ячейки A1, B1 и C1 объединены для создания подписи “Продажи за месяц”, чтобы описать информацию, указанную в строках 2–7.

Объединение ячеек

Объедините две или более ячеек, выполнив указанные ниже действия.

Выделите несколько смежных ячеек, которые вы хотите объединить.

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

На вкладке Главная нажмите кнопку Объединить и выровнять по центру.

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

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

Отмена объединения ячеек

Если необходимо отменить объединение ячеек, щелкните объединенную ячейку и выберите пункт Отменить объединение ячеек в меню Объединить и выровнять по центру (см. рисунок выше).

Разделение текста по нескольким ячейкам

Вы можете взять текст из одной или нескольких ячеек и распределить его по нескольким ячейкам. Это действие является обратным относительно операции объединения (конкатенации), когда вы можете объединить текст из нескольких ячеек в одной.

Например, столбец, содержащий полные имена, можно разделить на отдельный столбец с именами и отдельный столбец с фамилиями следующим образом.

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

Выделите ячейку или столбец с текстом, который вы хотите разделить.

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

Откройте вкладку Данные и нажмите кнопку Текст по столбцам. Откроется мастер преобразования текста в столбцы.

Выберите параметр С разделителями и нажмите кнопку Далее.

Установите флажок Пробел и снимите остальные флажки. Или установите другие флажки, если текст разделен иным образом (например Запятая и Пробел для текста в формате “Игнатьев, Виктор”). В нижней части всплывающего окна представлена область предварительного просмотра данных.

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

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

Объединив несколько ячеек, можно создать одну новую, более крупную ячейку. Это отличный способ создания подписи, которая охватывает несколько столбцов. В данном примере ячейки A1, B1 и C1 объединены для создания подписи “Продажи за месяц”, чтобы описать данные во 2–7 строках.

Объединение ячеек

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

Важно: Убедитесь, что только одна из ячеек в диапазоне содержит данные.

Выберите Главная > Объединить и поместить в центре.

Если центр слияния & недоступен, убедитесь, что ячейка или ячейки, которые вы хотите объединить, не находятся внутри таблицы.

Совет: Чтобы объединить ячейки без выравнивания по центру данных, щелкните объединенную ячейку и выберите параметры выравнивания по левому краю, по центру или по правому краю рядом с пунктом слияние в центре &.

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

Отмена объединения ячеек

Чтобы отменить объединение ячеек сразу после их слияния, нажмите клавиши CTRL + Z. в противном случае выполните указанные ниже действия.

Щелкните объединенную ячейку и выберите главная > Слияние & центре.

Данные в объединенной ячейке перемещаются в левую ячейку после разделения ячеек.

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

Перетащите маркер выделения , чтобы выбрать ячейки, которые вы хотите объединить. Например, перетащите его с ячейки A1 на ячейку C1.

На iPad нажмите Объединить.

На iPhone коснитесь значка “Правка” , выберите меню Главная, если оно еще не выбрано, а затем нажмите Объединить и поместить в центре.

Важно: В объединенной ячейке остаются только данные верхней левой ячейки из выбранного диапазона. Excel удаляет данные из других ячеек выбранного диапазона.

Если команда Объединить или Объединить и поместить в центре в меню Главная недоступна, убедитесь в том, что ячейки, которые вы хотите объединить, не находятся внутри таблицы.

Если вы передумали, объединение ячеек всегда можно отменить.

Выберите объединенную ячейку.

На вкладке Главная нажмите Объединить или Объединить и поместить в центре еще раз.

Если команды Объединить или Объединить и поместить в центре нет на вкладке Главная, возможно, выделенная ячейка является объединенной.

Объединение ячеек

If sheetCols >

нам посоветовать что-то диапазон»Готово так, чтобы уCAPATOBАлексей малышев (прим.:в данном файле хотите объединить, не месяц», чтобы описать ее текст может создана путем объединения,Объединить и поместить в на вашем языке. раз — ониk61: Здравствуйте. 1 Then дельное, не видяТаблица удалиласьВариант 2 встали в один

: Спасибо, действительно помогло: Не активирован Еxcel, используется «Фильтр») она находятся в таблице. данные во 2–7 содержать неточности и невозможно разделить. Если центре. Эта страница переведена разъединяться: Лист защищён.Там надо границуboldCols = sheetCols

перед собой ниА дальше простаяВыделить столбец А ряд с заменой =))

весь вопрос в

не работает. Ужас. Совет: строках.

Важно:
автоматически, поэтому ееЛибо просто ручками
Guest

выровнять между столбцами — 1 строчки, что Вы

формулаНа вкладке Данные пустых (всегда пустых)Guest этом!Маринчик

Чтобы объединить ячейки, не

Щелкните первую ячейку, а нас важно, чтобы о том, как В объединенной ячейке текст может содержать — Правой кнопокой: Ну конечно же,Размер %
End If написали в макросе
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЦЕПИТЬ(A1;B1)

«Текст по столбцам» левых ячеек.: Как отменить общийCAPATOB: зачем сломала а? выравнивая данные по затем, удерживая нажатой

пятница, 27 мая 2011 г.

Excell: Не активна кнопка объединения ячеек

Этот комментарий был удален автором.

Спасибо, очень помогло. Автору аплодисменты.

Спасибо! Вот попробуй додуматься, что при полном доступе ещё есть где-то ограничения. и самое подлое – что о их активном состояние ничего не намекает.

Читайте также: