DIONIS-CLUB ru
» » Создание таблицы в excel формула

Создание таблицы в excel формула

Категория : Файлы

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

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

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

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

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

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

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

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



excel в создание формула таблицы


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

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


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

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



Создание таблицы в excel формула видеоролик




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

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

Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС.

Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. B7 и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат.

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

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

Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться при помощи функции СУММ и умножаться на коэффициент с помощью формулы. Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Следует запуск окна Мастера функций.

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

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


Работа со связанными таблицами в Microsoft Excel

После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.



таблицы в excel формула создание


Но это ещё не все. Как мы помним, зарплата вычисляется путем умножения величины ставки на коэффициент.


Вычисления в Excel

Поэтому снова выделяем ячейку, в которой находится суммированная величина. После этого переходим к строке формул. Для выполнения вычисления щелкаем по клавише Enter на клавиатуре.



excel в формула таблицы создание


Как видим, программа рассчитала общую заработную плату по предприятию. Возвращаемся на Лист 2 и изменяем размер ставки любого работника. После этого опять перемещаемся на страницу с общей суммой. Как видим, из-за изменений в связанной таблице результат общей заработной платы был автоматически пересчитан. Кликаем по выделенному фрагменту правой кнопкой мыши. После этого перемещаемся на Лист 1. Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения.

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

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

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

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

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






Комментарии

здрасте всем!!!!!!!!!!
31.08.2018 00:09
По моему мнению Вы не правы. Я уверен. Могу отстоять свою позицию. Пишите мне в PM, обсудим.
03.09.2018 06:43

  • © 2009-2017
    dionis-club.ru
    RSS фид | Карта сайта