Просмотров: 64174

Как сделать в excel чтобы формула не менялась

Закрыть ... [X]


Автор: Индык Игорь Викторович e-mail:
 

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


 

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


 

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


 
 

 
 

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


 
 

В таблице Руководители в ячейку 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 СРЗНАЧ 2 СЧЁТ 3 СЧЁТЗ 4 МАКС 5 МИН 6 ПРОИЗВЕД 7 СТАНДОТКЛОН 8 СТАНДОТКЛОНП 9 СУММ 10 ДИСП 11 ДИСПР

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


 

Нажимаем ОК.


 

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


 
 

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


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


Источник: http://on-line-teaching.com/excel/lsn047.html

Похожие новости


Бижутерия из бисера своими рука
Самозастывающая масса для лепки своими руками
Как сделать на телефоне скриншот на асусе
Как сообщения вк сделать непрочитанными сообщения в
Как подготовится к химии самостоятельно
Как в фотошопе сделать открытку из фотографии
Как сделать углы для плинтуса потолочного
Препараты для удаление папилломы в домашних условиях




ШОКИРУЮЩИЕ НОВОСТИ