Как изменить часть массива

Формулы массива в Excel

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов: Формулы массива в Excel — это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории — те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах.

Пример 1. Классика жанра — товарный чек

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Пример 2. Разрешите Вас. транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

• Формула массива для извлечения непустых ячеек из диапазона
• Формула массива для извлечения уникальных ячеек из диапазона
• Формула массива для извлечения данных из списка (многоразовый ВПР)

Developing.ru

Уважаемые знатоки Excel, Который раз наступаю на грабли. При работе с ФОРМУЛОЙ МАССИВА, чуть зазеваешься, сразу попадаешь в капкан, который поставил MICROSOFT: НЕЛЬЗЯ ИЗМЕНЯТЬ ЧАСТЬ МАССИВА. Как из него выйти живым? Смотрел в библиях Excel. Ничего там нет об этом.

pashulka Сообщения: 831 Зарегистрирован: 24 ноя 2004, 03:46 Контактная информация:

Выделите любую ячейку, содержащую формулу массива, затем в меню Правка выберите команду Перейти (или воспользуйтесь клавишей F5 или CTRL+G), кликните кнопку Выделить, потом установите переключатель напротив Текущий массив и кликните кнопку Ok. Теперь Вы можете изменить формулу массива, разумеется, завершив эти действия нажатием клавиш CTRL+SHIFT+ENTER или вообще удалить её.

vigord Сообщения: 1 Зарегистрирован: 09 сен 2014, 17:25

Все лечится нажатием на красный крест (отмена) возле кнопки функций (fx).

3 сообщения • Страница 1 из 1

• Системное и прикладное программирование
• ↳ Win API, Shell..
• ↳ C и C++
• ↳ Delphi и Pascal
• ↳ Программирование на Pascal и Delphi (Object Pascal)
• ↳ Все вопросы ООП
• ↳ Компоненты в Delphi
• ↳ Delphi и WinAPI, ActiveX и OLE , COM и DCOM, и т.д
• ↳ Java
• ↳ MS Office и VB(A).
• ↳ Ассемблер
• WEB программирование
• ↳ JScript, VBScript, DHTML.
• ↳ Perl, PHP, ASP .
• Некатегоризированное
• ↳ Вопрошайка
• ↳ SQL
• ↳ Алгоритмы
• ↳ Дизайн и графика
• Администрирование
• ↳ Apache, IIS.
• ↳ Операционные системы
• Работа
• ↳ Вакансии и заказы для программистов
• ↳ Поиск работы и заказов.
• ↳ Решите мне задачку
• ↳ задачи на Паскале и Delphi
• ↳ задачи на C и C++
• ↳ задачи на Basic и Visual Basic (VB)
• Прочее
• ↳ Жалобная книга
• ↳ Флейм.

Как изменить часть массива

Добрый вечер, уважаемые форумчане!

Нашел решение своего кейса ( http://www.planetaexcel.ru/techniques/2/81/ ), но у меня оно почему-то не хочет работать.
К сожалению, не могу загрузить сюда файл, т.к. он слишком большой. Попробую описать иначе.

Во вложении скриншот с формулой. Саму формулу эксель берет в фигурные скобки и при попытке добавления ф-ции ЕСЛИОШИБКА выдает сообщение «Нельзя изменять часть массива»

Это как-то можно вылечить?

Прикрепленные файлы

• Рисунок1.jpg (90.75 КБ)

Пользователь
Сообщений: 14527 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
23.04.2015 23:06:20

Да. Выделяете все ячейки, в которые введена формула массива, переходите в строку формул, изменяете формулу и завершаете ввод сочетанием клавиш: Ctrl+Shift+Enter

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.

Excel works!

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

Нельзя изменять часть массива — уберите все объединение ячеек в файле

Как мы уже не раз обсуждали, объединение ячеек — это локальное зло Excel. Если вам нужно сделать красивое оформление текста, выровнять его по середине, то можно воспользоваться специальной возможностью Excel.

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

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

Затем переходим Главная — блок Выравнивание — Кнопку объединить ячейки, если она активна как на картинке, значит объединения есть — жмем на нее, должно помочь.

2. Используется формула массива

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

Включаем в Меню формулы видимость всех формул в модуле зависимости формул

И ищите, где формулы помещены в фигурные скобки <>. Как например:

3. Ячейка ссылает на группу ячеек с формулой массива

Скорее всего это глюк Excel 2007 и версий ниже. Т.е. есть формула массива, она корректно работает и так далее. Но вы ссылаетесь на 2 и более ячеек из этого массива, чтобы посчитать например сумму.

При попытке удалить строку, где вы считаете сумму, выдает ошибку выше. Не могу выложить пример — защищен конфиденциальностью, но, поверьте мне на слово, потратил минут 10 когда искал, почему не могу удалить строку.

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

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

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

Надеюсь помог вам!

Похожие статьи

• 10.03.2015Задать имя диапазона Excel? Горячие клавишиPosted in Excel, Работа с ячейками
• 17.03.2015Как сделать очень скрытый лист Excel?Posted in Excel
• 20.10.2015Как сортировать? Часть 2. Сортировка столбцов таблицы. Заданные параметры сортировкиPosted in Excel
• 21.04.2016Содержимое, которое не удалось прочитатьPosted in Excel
• 11.12.2015Как использовать имя листа в Excel?Posted in Excel
• 14.03.2015Как столбцы сделать строками? Транспонирование в ExcelPosted in Excel, Формулы
• 05.05.2016Как быстро выгрузить из 1С в Excel?Posted in Excel
• 10.05.2016Проблемы с Excel. Что делать, если он бесит?Posted in Excel