Как поставить фигурные скобки в excel

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

Формулы массива являются мощными формулами, которые позволяют выполнять сложные вычисления, которые часто невозможно выполнить с помощью стандартных функций листа. Они также называются формулами “Ctrl-Shift” или “CSE”, так как для их ввода требуется нажать клавиши CTRL + SHIFT + ВВОД. Формулы массива можно использовать для того, чтобы сделать это невозможным, например

Подсчитайте количество символов в диапазоне ячеек.

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

Суммирование всех n-х значений в диапазоне значений.

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

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

Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.

Щелкните ячейку, в которую нужно ввести формулу массива.

Введите необходимую формулу.

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

Например, эта формула вычисляет общую сумму для массива цен на акции и доли и поместит результат в ячейку рядом с полем “Общая сумма”.

Формула сначала умножает доли (ячейки B2 – F2) на цены (ячейки B3 – F3), а затем добавляет эти результаты для создания общего итога в 35 525. Это пример формулы массива с одной ячейкой, так как формула находится только в одной ячейке.

Нажмите клавишу Ввод (если у вас есть текущая подписка на Office 365 ); в противном случае нажмите клавиши CTRL + SHIFT + ВВОД.

При нажатии клавиш CTRL + SHIFT + ВВОДExcel автоматически вставляет формулу между <> (пару открывающих и закрывающих фигурных скобок).

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

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

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

Введите необходимую формулу.

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

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

Нажмите клавишу Ввод (если у вас есть текущая подписка на Office 365 ); в противном случае нажмите клавиши CTRL + SHIFT + ВВОД.

При нажатии клавиш CTRL + SHIFT + ВВОДExcel автоматически вставляет формулу между <> (пару открывающих и закрывающих фигурных скобок).

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

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

Удаление формулы массива (Кроме того, вы нажимаете клавиши CTRL + SHIFT + ВВОД)

Присвоение имени константе массива (они могут быть более простыми в использовании константами)

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

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

Скопируйте приведенную ниже таблицу и вставьте ее в Excel в ячейку a1. Не забудьте выделить ячейки E2: E11, введите формулу = C2: C11 * D2: D11, а затем нажмите клавиши CTRL + SHIFT + ВВОД, чтобы сделать ее формулой массива.

Источник: support.office.com

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

Терминология

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

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

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

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

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

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

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

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

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

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

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

    жмем 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 умножаются на ноль и суммируются только нужные заказы.

    Источник: www.planetaexcel.ru

    Многоячеечные формулы массива в Excel

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

    Применение многоячеечной формулы массива

    На рисунке ниже представлена таблица с наименованием товара, его ценой и количеством. В ячейках D2:D6 вычисляется общая стоимость каждого вида товара (с учетом количества).

    В данном примере диапазон D2:D6 содержит пять формул. Многоячеечная формула массива позволяет вычислить тот же самый результат, используя одну единственную формулу. Чтобы воспользоваться формулой массива выполните следующие действия:

    1. Выделите диапазон ячеек, где должны отображаться результаты. В нашем случае это диапазон D2:D6.
    2. Как и при вводе любой формулы в Excel, первым делом необходимо ввести знак равенства.
    3. Выделите первый массив значений. В нашем случае это диапазон с ценами товаров B2:B6.
    4. Введите знак умножения и выделите второй массив значений. В нашем случае это диапазон с количеством товаров C2:C6.
    5. Если бы мы вводили обычную формулу в Excel, то закончили бы ввод нажатием клавиши Enter. Но поскольку это формула массива, то нужно нажать комбинацию клавиш Ctrl+Shift+Enter. Этим мы укажем Excel, что это не обычная формула, а формула массива, и он автоматически заключит ее в фигурные скобки.

    Excel автоматически заключает формулу массива в фигурные скобки. Если Вы вставите скобки вручную, Excel воспримет это выражение как обычный текст.

    1. Обратите внимание, что все ячейки диапазона D2:D6 содержат абсолютно одинаковое выражение. Фигурные скобки, в которые оно заключено, говорят о том, что это формула массива.
    2. Если бы мы при вводе формулы массива выделили меньший диапазон, например, D2:D4, то она вернула бы нам только первые 3 результата:
    3. А если больший диапазон, то в “лишних” ячейках оказалось бы значение #Н/Д (нет данных):

    Когда мы умножаем первый массив на второй, перемножаются их соответствующие элементы (B2 с C2, B3 с C3, B4 с C4 и т.д.). В итоге образуется новый массив, который содержит результаты вычислений. Поэтому, чтобы получить корректный результат, размерности всех трех массивов должны быть соответствующими.

    Преимущества многоячеечных формул массива

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

    1. Используя многоячеечную формулу массива, Вы на 100% уверены, что все формулы в рассчитываемом диапазоне введены правильно.
    2. Формула массива в большей степени защищена от случайного изменения, поскольку редактировать можно только весь массив в целом. Если Вы попробуете изменить часть массива, у Вас ничего не получится. Например, при попытке удалить формулу из ячейки D4, Excel выдаст следующее предупреждение:
    3. Вы не сможете вставить новые строки или столбцы в диапазон, где введена формула массива. Чтобы вставить новую строку или столбец, придется заново перезадавать весь массив. Данный пункт можно рассматривать как преимущество, так и недостаток.

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

    Источник: office-guru.ru

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

    Если Вы довольно много работаете в Excel то наверняка уже слышали выражение “формула массива“. Так же его часто можно встретить на форумах и сайтах, посвященных Excel. Но не все знают что это такое и тем более, как пользоваться. Главное это не путать формулы массива с функциями для работы с массивами и функциями для работы с базами данных. Итак, не буду затягивать.
    Большинство уже наверняка знакомо с функцией СУММ (SUMM) . Она суммирует значения в заданных диапазонах ячеек. Теперь рассмотрим поближе. Запишем в ячейки А1:А5 числа от одного до пяти. В ячейке В1 запишем функцию: =СУММ(A1:A5) . Получим сумму этих чисел – 15. Все просто и понятно. И вроде бы – функция уже работает с массивами – ведь группа ячеек А1:А5 по сути уже массив ячеек. Но это не формула массива. А теперь изменим функцию на такую: =СУММ(A1:A5+1) . Что я хочу получить от такой функции? Чтобы суммировались значения ячеек А1:А5, но с прибавлением к каждому аргументу 1. Ввожу функцию и. Результат будет 2. Явно что-то не так. Верно, ведь при такой записи Excel произведет сложение ячейки А1 и 1. Все дело в том, что напрямую Excel не понимает, что необходимо производить подобные операции с массивами. Ему надо явно указать, что мы хотим это сделать и результат вернуть в одну ячейку. Чтобы ему указать это, надо при вводе функции нажать не Enter, как мы привыкли, а целую комбинацию клавиш – Ctrl+Shift+Enter. Т.е. записали функцию в ячейку и не жмем Enter, чтобы завершить ввод, а жмем Ctrl+Shift+Enter. Функция при таком вводе будет заключена в фигурные скобки – < =СУММ(A1:A5+1) > . Если Вы после ввода их увидели – значит все сделано верно и формула массива введена правильно. И результат будет 20.

    Важно: не надо пытаться ввести фигурные скобки вручную с клавиатуры -результатом будет лишь текст в ячейке <=СУММ(A1:A5+1)>и ни о какой формуле и суммировании речи быть уже не может.

    Что же происходит внутри функции в этот момент? Все очень просто. Мы в ячейки А1:А5 ввели поочередно цифры от 1 до 5. Т.е. получили: 1, 2, 3, 4, 5. В сумме они дают 15. Я разложу на слагаемые: =СУММ(1;2;3;4;5) . Теперь мы изменили функцию и ввели её как формулу массива: <=СУММ(A1:A5+1)>. И внутри происходит сначала прибавление к каждому числу 1, а затем сложение уже измененных аргументов: =СУММ(1+1;2+1;3+1;4+1;5+1) ⇒ =СУММ(2;3;4;5;6)

    Рассмотрим еще один пример, когда формула массива может решить задачу непосильную стандартной формуле(да еще и в одной ячейке без доп.столбцов). Необходимо получить минимальное значение из массива чисел: 0;1;5;5;9;0;6;2;6;3
    Применив обычную формулу =МИН(A1:A10) мы получим нуль. Что будет верным. Но если нам как раз нуль учитывать не надо? Мы можем ввести такую формулу:
    =МИН(ЕСЛИ(A1:A10<>0;A1:A10)) Казалось бы условие задано верно и мы должны получить нужный результат, т.е. 1. Но! Т.к. это простая формула, она обрабатывает не массив значений, а только первое значение массива(A1:A10) из условия ЕСЛИ, т.е. только А1. Сама по себе функция ЕСЛИ не станет работать с массивом значений в данном случае. Это означает, что формула не просматривает весь заданный массив. Но если ввести её как формулу массива
    <=МИН(ЕСЛИ(A1:A10<>0;A1:A10))> то в таком случае формула последовательно просмотрит каждое значение из массива на предмет выполнения условия и выполнит необходимые вычисления, заданные в этой формуле, так как будто бы мы последовательно для каждой строки в отдельном столбце вывели результат выполнения заданного условия ЕСЛИ и уже по этим результатам определили минимальное значение. И результат формулы будет – 1.

    Так же формула массива может вернуть несколько значений. Очень наглядно это демонстрирует функция ТРАНСП (TRANSPOSE) . Функция преобразовывает вертикальный массив в горизонтальный и наоборот. Массив может быть многомерным. Как работает функция(на примере исходного диапазона A1:C10 ):

    • выделяете диапазон ячеек( D1:M3 ), равный по количеству ячеек исходному диапазону значений( A1:C10 ), которые необходимо транспонировать;
    • вписываете функцию ТРАНСП;
    • в качестве аргумента указываете ссылку на исходный диапазон значений: =ТРАНСП( A1:C10 ) ;
    • завершаете ввод функции сочетанием клавиш Ctrl+Shift+Enter.

    В диапазоне D1:M3 получите транспонированную таблицу. При написании функции следует учитывать, что число строк в диапазоне функции( D1:M3 ) должно быть равно числу столбцов в исходном диапазоне( A1:C10 ), а число столбцов – числу строк. Если указать меньше – не все значения будут транспонированы. Если больше – то все лишние ячейки будут заполнены значениями #Н/Д

    Какие особенности подобного применения функций массива:

    • во всех ячейках формула отображается совершенно одинаково, даже если ссылки на ячейки относительные. Это не должно вас пугать – так надо;
    • ячейки диапазона, в который подобным образом введена формула массива нельзя изменять по отдельности – только все вместе. В противном случае просто получите сообщение “Нельзя изменять часть массива!”. Бывает очень удобно иногда в целях защиты формул от изменений.

    Итак, что самое важное надо запомнить для использования формул массива:

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

    Статья помогла? Поделись ссылкой с друзьями!

    Источник: www.excel-vba.ru

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

    Формулы массива являются мощными формулами, которые позволяют выполнять сложные вычисления, которые часто невозможно выполнить с помощью стандартных функций листа. Они также называются формулами “Ctrl-Shift” или “CSE”, так как для их ввода требуется нажать клавиши CTRL + SHIFT + ВВОД. Формулы массива можно использовать для того, чтобы сделать это невозможным, например

    Подсчитайте количество символов в диапазоне ячеек.

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

    Суммирование всех n-х значений в диапазоне значений.

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

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

    Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.

    Щелкните ячейку, в которую нужно ввести формулу массива.

    Введите необходимую формулу.

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

    Например, эта формула вычисляет общую сумму для массива цен на акции и доли и поместит результат в ячейку рядом с полем “Общая сумма”.

    Формула сначала умножает доли (ячейки B2 – F2) на цены (ячейки B3 – F3), а затем добавляет эти результаты для создания общего итога в 35 525. Это пример формулы массива с одной ячейкой, так как формула находится только в одной ячейке.

    Нажмите клавишу Ввод (если у вас есть текущая подписка на Office 365 ); в противном случае нажмите клавиши CTRL + SHIFT + ВВОД.

    При нажатии клавиш CTRL + SHIFT + ВВОДExcel автоматически вставляет формулу между <> (пару открывающих и закрывающих фигурных скобок).

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

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

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

    Введите необходимую формулу.

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

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

    Нажмите клавишу Ввод (если у вас есть текущая подписка на Office 365 ); в противном случае нажмите клавиши CTRL + SHIFT + ВВОД.

    При нажатии клавиш CTRL + SHIFT + ВВОДExcel автоматически вставляет формулу между <> (пару открывающих и закрывающих фигурных скобок).

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

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

    Удаление формулы массива (Кроме того, вы нажимаете клавиши CTRL + SHIFT + ВВОД)

    Присвоение имени константе массива (они могут быть более простыми в использовании константами)

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

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

    Скопируйте приведенную ниже таблицу и вставьте ее в Excel в ячейку a1. Не забудьте выделить ячейки E2: E11, введите формулу = C2: C11 * D2: D11, а затем нажмите клавиши CTRL + SHIFT + ВВОД, чтобы сделать ее формулой массива.

    Источник: support.office.com

    Операции с формулами массивов в Excel 2007

    Автор: Индык Игорь Викторович
    e-mail: exelentc@yandex.ru

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

    Вы можете воспользоваться функцией ВПР.

    Для решения данной задачи создадим пример из двух таблиц – Руководители и Компании.

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

    В таблице Руководители в ячейку G2 (первая запись таблицы) вводим функцию ВПР (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).

    Искомое_значение – делаем ссылку на ячейку, в которой содержится название компании в таблице Руководители (которое идентично названию компании в первом столбце таблицы Компании).

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

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

    Интервальный_просмотр – может иметь только два значения:

    • А) ИСТИНА – поиск в таблице будет проводиться по приблизительному совпадению;
    • Б) ЛОЖЬ – поиск в таблице будет проводиться по точному совпадению.

    Нажимаем кнопку ОК. С помощью функции Автозаполнение копируем функцию на все строки таблицы.

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

    Как в Экселе перевернуть таблицу из столбика в строчку?

    Транспонировать таблицу (т.е. развернуть таблицу поменяв местами столбцы и строки) можно двумя способами.

    1. С помощью функции ТРАНСП. Она позволяет сохранять связь между таблицами. Таким образом, меняя данные в исходной таблице – они автоматически будут изменены и в транспонированной.

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

    Вводим функцию ТРАНСП (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).

    В поле Массив укажите диапазон ячеек, который нужно транспонировать. Нажмите ОК. В ячейке будет отображаться ошибка формулы #ЗНАЧ!, нажимаем F2, а затем CTRL+SHIFT+ENTER. Благодаря этим действиям формула преобразовалась в формулу массива и весь выделенный диапазон заполнился данными, а в строке формул вы увидите фигурные скобки.

    Внимание! Фигурные скобки нужно проставлять исключительно путем нажатия клавиш CTRL+SHIFT+ENTER, если их поставить вручную, формула не будет работать.

    2. С помощью опции Специальная вставка – Транспонировать. Данный способ транспонирует (переворачивает) таблицу без создания связей с исходной таблицей.

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

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

    С этой целью можно использовать формулу массива и промежуточные итоги.

    1. Формула массива.

    Предположим у нас есть таблица со следующими данными:

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

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

    В нем мы кликаем дважды по функции СУММ и начинаем вводить аргументы:

    А) открываем скобку, выделяем диапазон ячеек, которые содержать названия магазинов, устанавливаем курсор в строку формул, вводим знак “=” и кликаем на ячейку с данным условием, закрываем скобку;

    Б) ставим знак умножить “*”, открываем скобку и выделяем диапазон ячеек, который содержит марки телефонов, устанавливаем курсор в строку формул, вводим знак “=” и кликаем на ячейку с данным условием, закрываем скобку;

    В) ставим знак умножить “*”, открываем скобку и выделяем диапазон ячеек, который содержит модель телефонов, устанавливаем курсор в строку формул, вводим знак “=” и кликаем на ячейку с данным условием, закрываем скобку;

    Г) ставим знак умножить “*”, выделяем диапазон, который содержит выручку, закрываем скобку;

    Д) нажимаем CTRL+SHIFT+ENTER – формула заключается в фигурные скобки, что и делает ее формулой массива.

    Внимание! Фигурные скобки нужно проставлять исключительно путем нажатия клавиш CTRL+SHIFT+ENTER, если их поставить вручную, формула не будет работать.

    В результате у нас просуммирована выручка по телефонам Samsung Е2121, которые проданы в Магазине №1

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

    2. Функция Промежуточные итоги.

    Ее можно использовать для вычислений данных отобранных с помощью Автофильтра. В конце таблицы в столбцах, в которых необходимо провести вычисления, вводим функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).

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

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

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

    В начало страницы

    В начало страницы

    Источник: on-line-teaching.com