Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как в офисе

Динамическая таблица в Excel

Если вам необходимо постоянно обновлять и добавлять данные или если вы работаете с диаграммами и сводными таблицами, вам понадобятся динамические именованные диапазоны, которые увеличиваются и уменьшаются в зависимости от ваших данных. Чтобы понять, как работают динамические именованные диапазоны, сначала нужно познакомиться с функцией Excel СМЕЩ (OFFSET), если вы еще не знакомы с ней. Функция СМЕЩ (OFFSET) относится к ссылочным функциям и функциям поиска Excel.

Начнем с простейшего динамического именованного диапазона, который будет расширяться вниз по одному столбцу, но только до тех пор, пока в этом столбце есть записи. Например, если в столбце А есть 10 последовательных строк с данными, динамический именованный диапазон будет охватывать диапазон А1:А10. Чтобы создать базовый динамический именованный диапазон, сделайте следующее.

Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите MyRange. В поле Формула (Refers to) введите следующую формулу: =OFFSET($A$1;0;0;COUNTA($A$l:$A$100);l), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А$1:$А$100);1). Теперь щелкните на кнопке Добавить (Add), затем щелкните на кнопке ОК.

К сожалению, динамические именованные диапазоны нельзя проверить в стандартном поле имени слева от строки формул. Несмотря на это, можно щелкнуть в поле имени, ввести имя MyRange и нажать клавишу Enter. Excel автоматически выделит диапазон. Конечно, можно воспользоваться и диалоговым окном Переход (Go То), выбрав команду Правка → Перейти (Edit → Go To) (сочетание клавиш Ctrl/Apple+G). В поле Ссылка (Reference) введите MyRange и щелкните на кнопке ОК.

В динамическом именованном диапазоне, который вы создали в предыдущем примере, функция СЧЁТЗ (COUNTA) стоит на месте аргумента Высота (Height) функции СМЕЩ (OFFSET).

В следующем примере динамический именованный диапазон мы применим для определения таблицы данных, которая должна быть динамической. Для этогоn в поле Формула (Refers to) введите следующую формулу: =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);COUNTA($1:$1)), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А$1:$А$100);СЧЁТЗ($1:$1)). Теперь динамический именованный диапазон будет расширяться на столько записей, сколько есть в столбце А, и на столько строк, сколько заголовков в строке 1. Если вы уверены, что количество столбцов в таблице данных меняться не будет, можете заменить вторую функцию СЧЁТЗ (COUNTA) постоянным числом, например, 10.

Единственная проблема при использовании динамического именованного диапазона для таблицы данных заключается в том, что предполагается, что столбец А определяет максимальную длину таблицы. Чаще всего это так, однако иногда самым длинным столбцом может быть другой столбец таблицы. Преодолеть эту проблему можно при помощи функции Excel МАКС (МАХ), которая возвращает самое большое число в диапазоне ячеек. В качестве примера создайте таблицу, как на рис. 3.4.

Рис. 3.4. Динамическая таблица данных и диалоговое окно присвоения имени

В строке 1 хранятся функции СЧЁТЗ (COUNTA), которые ссылаются вниз на соответствующий столбец и, таким образом, возвращают количество записей в каждом столбце. Функция МАКС (МАХ) будет использоваться в качестве аргумента Высота (Height) функции СМЕЩ (OFFSET). Это гарантирует, что динамический именованный диапазон для этой таблицы всегда будет расширяться вниз на столько ячеек, сколько их содержится в самом длинном столбце в таблице. Конечно же, можно скрыть строку 1, так как пользователю совершенно не нужно ее видеть.

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

В следующем примере список чисел в столбце А также содержит пустые ячейки. Это означает, что, если вы попытаетесь воспользоваться функцией СЧЁТ (COUNT) или СЧЁТЗ (COUNTA), динамический именованный диапазон закончится раньше, чем последняя ячейка с данными. Взгляните, например, на рис. 3.5.

Рис. 3.5. Диапазон чисел и диалоговое окно присвоения имени

В этом случае, хотя последнее число диапазона в действительности находится в строке 10, динамический диапазон расширяется вниз только до шестой строки. Причина этого лежит в функции СЧЁТ (COUNT), которая считает значения в ячейках от А1 до А100. Так как в списке только шесть числовых значений, диапазон содержит только шесть строк.

Чтобы преодолеть эту проблему, воспользуйтесь функцией Excel ПОИСКПОЗ (MATCH). Функция ПОИСКПОЗ (MATCH) возвращает относительную позицию элемента массива, соответствующего указанному значению в указанном порядке. Например, если вы примените следующую функцию ПОИСКПОЗ (MATCH): =МАТСН(6;$А$1:$А$100;0), в русской версии Excel =ПОИСКПОЗ(6;$А$1:$А$100;0), к тому же набору чисел, что и на рис. 3.5, она вернет число 10, представляющее строку 10 столбца А. Она возвращает 10, так как вы приказали функции найти число 6 в диапазоне А1:А100.

Очевидно, когда вы используете функцию ПОИСКПОЗ (MATCH) в динамическом именованном диапазоне, последнее число диапазона, вероятно, заранее неизвестно. Таким образом, понадобится задать в функции поиск слишком большого числа, которое никогда не появится в диапазоне, и изменить ее последний аргумент с 0 на 1.

В предыдущем примере вы приказали функции ПОИСКПОЗ (MATCH) найти в точности число 6, не больше и не меньше. Заменив 0 на 1, вы заставите функцию искать самое большое значение, меньшее или равное указанному. Для этого воспользуйтесь формулой =МАТСН(1Е+306;$А$1:$А$100;1), в русской версии Excel =ПОИСКПОЗ(1Е+306;$А$1:$А$100;1).

Чтобы создать динамический именованный диапазон, который будет расширяться до последней строки, содержащей число (независимо от наличия пустых ячеек до нее), введите следующую формулу в поле Формула (Refers to) диалогового окна Присвоение имени (Define Name) (рис. 3.6): =OFFSET(Sheet2!$A$1;0;0;MATCH(lE+306;Sheet2!$A$l:$A$100;1);1), в русской версии Excel =CMEЩ(Sheet2!$A$1;0;0;ПОИСКПОЗ(1E+306;Sheet2!$A$l:$A$100;1);1).

Рис. 3.6. Динамический диапазон, расширяющийся до последней записи, содержащей число

Следующий логический тип динамических именованных диапазонов, вытекающий из данного, — это диапазон, расширяющийся до последней текстовой записи независимо от пустых ячеек в списке или таблице. Для этого измените функцию ПОИСКПОЗ (MATCH) таким образом: МАТСН(«*»;$А$1:$А$100;-1), в русской версии Excel ПОИСКПОЗ(«*»;$А$1;$А$100;-1). Такая функция всегда возвращает номер строки, где содержится последняя текстовая запись в диапазоне $А$1:$А$100.

Читайте так же:
Как в ворде сделать колонки с разделителем

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

Для этого сначала вставьте две пустые строки выше списка. Выделите строки 1 и 2 и выберите команду Вставка → Строки (Insert → Row). В первой строке (строка 1) введите следующую функцию: =МАХ(МАТСН»*»;$А$3:$А$100;-1);МАТСН(1Е+306;$А$3:$А$100;1)), в русской версии Excel =МАКС(ПОИСКПОЗ»*»;$А$3:$А$100;-1);ПОИСКПОЗ(1Е+306;$А$3:$А$100;1)). В ячейке под ячейкой с формулой введите число 1. Ячейка еще ниже, под ячейкой с числом 1, должна содержать текстовый заголовок списка. Число 1 было добавлено, чтобы вторая функция ПОИСКПОЗ (MATCH) не вернула ошибку #N/A, если в диапазоне АЗ:А100 не окажется чисел. Первая функция ПОИСКПОЗ (MATCH) всегда найдет текст — в заголовке.

Дайте ячейке А1 имя MaxRow. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define), присвойте динамическому диапазону имя, например, MyList и в поле Формула (Refers to:) введите следующую формулу: =OFFSET(Sheet2!$A$3;0;0;MaxRow;1), в русской версии Excel =CMEЩ(Sheet2!$A$3;0;0;MaxRow;1).

В следующем списке перечислены типы динамических именованных диапазонов, которые могут оказаться полезными. Для веет этих примеров понадобится заполнить столбец А и текстом, и числовыми значениями. Кроме того, выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите имя, состоящее из одного слова (например, MyRange). Все, что будет изменяться, — это формула в поле Формула (Refers to).

Расширить диапазон на столько строк, сколько существует числовых записей: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;COUNT($A:$A);1), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТ($А;$А);1).

Расширить диапазон на столько строк, сколько существует числовых и текстовых записей: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;COUNTA($A:$A);1), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А:$А);1).

Расширить до последней числовой записи: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)), в русской версии Excel =СМЕЩ($А$1;0;0;ПОИСКПОЗ(1Е+306;$А:$А)). Если вы ожидаете, что может встретиться число, большее 1Е+306 (1 с 306 нулями), введите еще большее число.

Расширить до последней текстовой записи: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;МАТCH(«*»;$А:$А;-1)), в русской версии Excel =СМЕЩ($А$1;0;0;ПОИСКПОЗ(«*»;$А:$А;-1)).

Расширить вниз в зависимости от значения в другой ячейке:В ячейку В1 введите число 10, а затем в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;$B$1;1), в русской версии Excel =СМЕЩ($А$1;0;0;$В$1;1). Теперь измените число в ячейке В1, и диапазон изменится соответствующим образом.

Расширять вниз по одной строке каждый месяц: В поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0,MONTH(TODAY());1), в русской версии Excel =СМЕЩ($А$1;0;0.МЕСЯЦ(СЕГОДНЯ));1).

Расширять вниз по одной строке каждую неделю: В поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;WEEKNUM(TODAY());1), В русской версии Excel =СМЕЩ($А$1;0;0;ДЕНЬНЕД(СЕГОДНЯ());1)

Для последней формулы необходимо установить Пакет анализа (Analysis ToolPak). Это можно сделать командой Сервис → Надстройки (Tools → Add-ins).

Ссылка на динамический именованный диапазон в Excel Formula

У меня есть таблица в Excel с заголовками столбцов, которые соответствуют части динамического именованного диапазона в другом месте моей книги. Например, у меня есть такие заголовки столбцов: «10», «20» и т. д., И эти динамические именованные диапазоны: «ExampleRange10″,»ExampleRange2» и т. д. Я хотел бы ввести формулу VLookup, которая ссылается на ExampleRange10, объединив строку «ExampleRange» и заголовок столбца «10». Это позволило бы мне просто расширить формулу по всем столбцам таблицы, вместо того чтобы вручную вводить «ExampleRange10», «ExampleRange20» и т. д.

Я знаю о функции INDIRECT и успешно использовал ее в прошлом с именованными диапазонами, но в данном случае она, похоже, не работает с динамическим именованным диапазоном. Я предполагаю, что это нюанс, который имеет какое-то отношение к тому, как динамические именованные диапазоны определяются Excel (они не отображаются в раскрывающемся списке именованный диапазон слева от строки формул, и у них есть некоторые интересные свойства в VBA, например). Есть ли способ использовать формулу INDIRECT в сочетании с динамическим именованным диапазоном или есть другой способ решения этой проблемы?

Правка: вот точные используемые формулы.
Это основная формула: =VLOOKUP(B2,INDIRECT(«ExampleRange»&C1),2,FALSE) , где C1 содержит «10», а формула для моего динамического именованного диапазона под названием «ExampleRange10» равна: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2) . Основная формула возвращает «#REF!», но она работает правильно, когда я удаляю динамическую формулу именованного диапазона и просто определяю «ExampleRange10» как статический диапазон.

8 ответов

  • Excel Interop — как изменить именованный диапазон

У меня есть файл шаблона excel, в который я заполняю данные из соединителя SQL Server OLEDB. Файл содержит несколько таблиц pivot, которые ссылаются на набор данных, заполняемый базой данных. В настоящее время я делаю то, что выбираю все строки в листе, используя диапазон Data!$A:$K. Это приводит.

Я пытаюсь определить именованный диапазон в Excel, используя VBA. В принципе, у меня есть переменный номер столбца. Затем выполняется цикл для определения первой пустой ячейки в этом конкретном столбце. Теперь я хочу определить именованный диапазон от строки 2 этого конкретного столбца до.

Читайте так же:
Можно ли 3pin подключить к 4pin

Насколько я мог судить после дальнейших исследований, функция Excel INDIRECT просто не работает с динамическими диапазонами. Возможно, есть умный способ обойти использование INDIRECT и придерживаться мира, отличного от VBA Excel, но я не знаю такого способа. Вместо этого я создал пользовательскую функцию, очень похожую на описанную здесь . Я изменил свою основную формулу на =VLOOKUP(B2,DINDIRECT(«ExampleRange»&C1),2,FALSE) , где DINDIRECT -это имя функции VBA, которую я создал.

Единственным недостатком (который может быть или не быть недостатком в зависимости от того, как вы на это смотрите) этой альтернативы является то, что книга должна быть сохранена как книга с поддержкой макросов, а использование пользовательской функции не очень самодокументировано и требует небольшого объяснения другим пользователям. Однако, учитывая все обстоятельства, это было приемлемым решением для меня.

Для отвращения к ссылкам вот код:

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

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

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

Если ваши данные имеют заголовки типа 10, 20 и т. Д., Вам не нужно использовать Косвенные. Почему бы просто не использовать Index/Match для выбора нужных вам данных?

Например, назовите всю таблицу ExampleRanges и используйте эту формулу:

Я знаю, что это довольно старое, но я только что наткнулся на это и подумал, что добавлю решение, которое избегает любого кодирования VBA, если это поможет кому-то еще, кто наткнется на это:

Это предполагает,что соглашение об именовании составляет 10,20,30 и т. Д. И не будет идеальным для сотен диапазонов.

  • Ссылка на книгу через именованный диапазон в Excel

У меня есть 3 рабочие книги-Parent1.xlsx и Child.xlsx Parent1.xlsx имеет данные, на которые будет ссылаться Child.xlsx через vlookup. Формула ВПР =VLOOKUP(1,[Parent1.xlsx]Sheet1!$A$1:$B$7,2,FALSE) Это прекрасно работает. Теперь я должен сделать копию с Parent1.xlsx по Parent2.xlsx. Для того чтобы.

У меня есть несколько диапазонов имен в моей электронной таблице: range1 сетей2 range3 Приведенная ниже формула работает: =Rank(a1,range1) Когда я оцениваю формулу, она решает быть такой: Это говорит мне о том, что формула разрешает именованный диапазон в диапазон и, следовательно, может выполнять.

Непроверено, но я думаю, что это сработает:

определяемая пользователем функция для возврата адреса вашего динамически именованного диапазона:

тогда вы сможете использовать свою формулу vlookup:

Сегодня я возился с Excel именованными диапазонами и обнаружил, что, хотя вы действительно не можете вычислить имя диапазона в самом вызове INDIRECT() , вы все равно можете получить его в чистом «Excel-way», добавив промежуточный шаг: просто создайте скрытую ячейку, в которой вы вычисляете именованный диапазон.

Например, предположим, что A1 содержит «dynamic part» имени диапазона , затем в A2 используйте формулу = «ExampleRange» & A1 , и теперь у вас есть полное имя диапазона, которое вы можете использовать как = INDIRECT(A2) .

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

чтобы получить адрес: $T$56: =АДРЕС(СОВПАДЕНИЕ(S56,Линейный!A:A,0),1,1,1,»Linear»)

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

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

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

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

Похожие вопросы:

У меня есть именованный диапазон под названием ‘CityLocation’. Если я хочу сослаться на этот именованный диапазон в другом месте формулы, я могу просто написать что-то вроде =COUNTA(CityLocation) .

У меня есть именованный диапазон в Excel, который я пытаюсь очистить с помощью VBA. Проблема с диапазоном заключается в том, что он охватывает несколько листов, и я не уверен, как правильно.

Я назвал несколько диапазонов в excel и хотел бы использовать их в Формуле (countif). Вместо того чтобы вводить формулу и постоянно изменять ее вручную, я хотел бы ссылаться на ячейку с именем.

У меня есть файл шаблона excel, в который я заполняю данные из соединителя SQL Server OLEDB. Файл содержит несколько таблиц pivot, которые ссылаются на набор данных, заполняемый базой данных. В.

Я пытаюсь определить именованный диапазон в Excel, используя VBA. В принципе, у меня есть переменный номер столбца. Затем выполняется цикл для определения первой пустой ячейки в этом конкретном.

Читайте так же:
Можно ли восстановить удаленную папку с флешки

У меня есть 3 рабочие книги-Parent1.xlsx и Child.xlsx Parent1.xlsx имеет данные, на которые будет ссылаться Child.xlsx через vlookup. Формула ВПР =VLOOKUP(1,[Parent1.xlsx]Sheet1!$A$1:$B$7,2,FALSE).

У меня есть несколько диапазонов имен в моей электронной таблице: range1 сетей2 range3 Приведенная ниже формула работает: =Rank(a1,range1) Когда я оцениваю формулу, она решает быть такой: Это.

У меня есть этот динамический диапазон с именем: = OFFSET(‘Sheet1’!$H$1, 0, 0, COUNTA(‘Sheet1’!$H$1:$H$100)-COUNTIF(‘Sheet1’!$H$1,0)) Это работает только для листа 1. Как я могу изменить эту.

У меня есть лист, где количество ‘items’ в списке может меняться в зависимости от количества элементов в заказе. У меня есть вкладка шаблона, которую я затем использую для создания определенной.

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

Динамические диапазоны в excel

Использование динамических именованных диапазонов

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

Стандартное решение такой задачи — делая ссылку на таблицу, захватить пустые ячейки — выделить "с запасом". Такой способ имеет два серьезных недостатка:

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

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

Чтобы создать динамический именованный диапазон, ссылающийся на эту таблицу, откроем меню Вставка — Имя — Присвоить (Insert — Name — Define) и введем туда Имя диапазона (например Железо ) и формулу, которая будет ссылаться на заполненный диапазон:

Функция СМЕЩ (OFFSET) выдает ссылку на диапазон, смещенный относительно исходного на заданное количество ячеек и имеет несколько аргументов:

·Ссылка — исходная ячейка, от которой идет отсчет — с нее начинается наш диапазон ($A$1)
·Смещение по строкам и столбцам — сдвиг начала диапазона относительно Ссылки — по нулям
·Высота — количество ячеек по вертикали, из которых состоит наш диапазон — определяем количество непустых ячеек в столбце А при помощи функции подсчета значений СЧЁТЗ($A:$A)
·Ширина — количество ячеек по горизонтали — в нашей таблице три столбца, поэтому = 3

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Изучаем функцию СМЕЩ

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

Функция СМЕЩ возвращает ссылку на диапазон с определенной отправной точкой с заданной высотой и шириной.

Что это значит? К примеру, у вас есть диапазон данных, который используется для построения диаграммы, но количество строк настолько велико, что диаграмма становиться нечитабельной для пользователей. Выходом из ситуации станет отображение только определенной части данных. Например, пользователям необходимо будет указать последний квартал или год и функция СМЕЩ отправит новый диапазон данных на построение диаграммы.

Синтаксис формулы СМЕЩ

Формула СМЕЩ выглядит следующим образом:

=СМЕЩ(ссылка; смещение по строкам; смещение по столбцам; высота; ширина)

  • Ссылка: отправная точка, эта ячейка, относительно которой необходимо смещаться
  • Смещение по строкам и столбцам: Указывает, на какое количество строк или столбцов необходимо сместиться от отправной точки. Может иметь положительные и отрицательные значения, а также равняться нулю.
  • Высота и ширина: Это размер диапазона, который вы хотите вернуть. К примеру, 4X3 вернет диапазон содержащий 4 строки в высоту и 3 столбца в ширину.

И конечно, все аргументы могут ссылаться на другие ячейки. Это означает, что вы можете написать формулу =СМЕЩ(A1;D1;D2;D3; D4), которая вернет диапазон

  • Смещенный от ячейки A1
  • На D1 строк и D2 столбцов
  • Размером D3 строчки и D4 столбца

На рисунке изображен пример работы формулы СМЕЩ для лучшего понимания.

Пример формулы СМЕЩ

Зачем использовать формулу СМЕЩ?

Почему просто нельзя явно указать название диапазона, например, A1:C4?

Вот пара причин почему:

  • Динамические диапазоны: Ссылка типа A1:C4 всегда будет возвращать диапазон A1:C4, т.е. она статична. Но иногда нам необходимо, чтобы диапазон был динамичным. Это требуется потому что данные могут меняться (с каждым месяцем добавляются новые строчки, запускаются новые продукты – добавляются колонки)
  • Мы не знаем точного адреса: Иногда мы не можем знать фактического адреса. Чаще всего нам известна только стартовая ячейка. В такой ситуации нам поможет формула СМЕЩ.

Интерактивное описание формулы СМЕЩ

Формула СМЕЩ довольно непростая для понимания, поэтому была создана интерактивная таблица, чтобы вы могли лучше понять, как она работает. Изменяя 5 аргументов формулы, Excel будет выделять диапазон, который вы выбрали. После нескольких минут баловства, вы поймете формулу лучше.

Практикуемся с формулой СМЕЩ – среднее значение последней недели

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

Читайте так же:
Как восстановить историю whatsapp на android

Лист сбора данных

Так как же мы будем считать средний показатель брака за неделю?

Если предположить, что данные находятся в диапазоне B2:B23, тогда необходимо записать =СРЗНАЧ(B2:B23).

Но нам необходимо, чтобы эта формула менялась ежедневно.

Используя формулу СМЕЩ мы можем один раз посчитать среднее значение и больше не возвращаться к этому вопросу.

Разберемся, как работает эта формула

  • Для того, чтобы посчитать среднее значение последней недели, мы должны вытянуть последние 7 строк со всего массива данных.
  • Вот откуда взялась формула СЧЁТЗ(B2:B300)-7, которая считает количество значений и затем, от полученной цифры отнимается 7.
  • Формула СМЕЩ перемещается с ячейки B2 на стартовую ячейку последней недели.
  • На гифке показано, как работает динамический диапазон.

Вам также могут быть интересны следующие статьи

26 комментариев

СПАСИБО!
Хоть кто-то нормально разъяснил как эта полезная функция работает.
СПАСИБО!

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

Спасибо. Только с Вами разобрался! ))

Ренат, а какой софт вы используете для записи видео с монитора и создания гиф анимации?

Camtasia Studio 7

Спасибо, большое.
Частенько вставала проблема динамических диапазонов. Теперь есть простое решение. Сайт Ваш очень понравился. Побольше бы статей в нем еще, было бы вообще прекрасно.

Ренат, а вот если нам необходимо посчитать не среднюю сумму за последнюю неделю, а сумму по первому и последнему дню, не поможете решить такую вот задачку?

Если в рамках последнего примера, то вот что пришло первое на ум:
=СУММ(СМЕЩ(B2;СЧЁТЗ(B2:B300)-7;0);СМЕЩ(B2;СЧЁТЗ(B2:B300)-1;0))

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

Спасибо. Вы гений!))

Ренат добрый день,
столкнулся с задачей, задать в Excel следующее условие:
если в ячейке A1 стоит «да», то в ячейке А2 должно стоять «нет», а если в ячейке А1 стоит «нет» то в ячейке А2 должен появиться выбор из списка «да» или «нет».
Есть ли решение без программирования в VBA ?

Добрый день, Родион. Без VBA на ум приходит только вариант с наложением двойного условия на ячейку A2 и на диапазон выпадающего списка. Чтобы список менял свое содержимое, в зависимости от значения ячейки A1. Большой недостаток, данного метода, что после использования выпадающего списка в ячейке A2, формулы слетают. Пример в прикрепленном файле с условиями на выпадающий список

Ребята, привет!
Может поможете — подскажете:)

Есть данные продаж — по строкам список магазинов, по столбцам — название товара, внутри соответственно количество проданного товара.
Необходимо данные предоставить по строкам — Магазин, товар, кол-во.
Коллеги показывают использование функции СМЕЩ.

Как на ваш взгляд — это наиболее рациональный подход?

Добрый день,
Думаю, использование функции СМЕЩ — хороший вариант, но в вашем случае, я бы использовал функцию ИНДЕКС

мне необходимо в отдельную таблицу добавлять данные из другой таблицы. Все данные как бы располагаются по горизонтали. Строка остается прежняя, а столбец смещается на 2 вперед. я задала функцию смещ. но она работает только в первой ячейке. А дальше только если я копирую формулу по горизонтали. Но итоговая таблица по вертикали. Как сделать формулу, чтобы при копировании по вертикали, исходная строка не смещалась? Но при этом менялся столбец? Я сделала так OFFSET(BB$274,0,2).

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

Ренат, спасибо большое за описание функции!
Не могли бы вы также пояснить метод, который был использован для создания интерактивной таблицы. В частности, меня интересует часть «=ЧСТРОК(диап_смещ I12)», которая возвращает «пусто», если ячейка вне диапазона, и «1», если внутри. Я первый раз вижу использование названия массива через пробел со ссылкой на ячейку, хотел бы понять, какой механизм за этим стоит. Спасибо большое.

Ренат, это одна из хитростей Excel. Функция ЧСТРОК позволяет сказать количество строк, которые находятся на пересечении двух массивов (в нашем случае массива и ячейки). Пробел указывает на то, что нам необходимо именно пересечение

С большим интересом прочитал вашу статью о формуле СМЕЩ.
Очень интересно и познавательно! Особенно понравилось
применение СМЕЩ для вычисления среднего значения последней недели.
Просто великолепно! Давно искал что-то подобное, простое
и изящное. Благодарю за ваш труд.
Скажите, а нельзя ли как-то переписать вашу формулу таким образом,
чтобы она при вычислениях учитывала (игнорировала-?) пустые ячейки?
Я попробовал применить вашу формулу к диапазону чисел, в котором
есть пустые ячейки.Применительно к примеру в вашей статье — это как если бы
отсутствовало, например, число 98 в ячейке В20. Получилась какая-то ерунда, формула
вроде бы посчитала, но выдала неверное значение. Как можно это исправить,
чтобы формула работала с диапазоном чисел, в котором есть пустые ячейки?
Мне лично ничего в голову не приходит, пробовал и так и сяк, ничего
не получается. Может быть, поможете решить эту проблему?

Добрый день, чтобы определить положение последнего элемента в диапазоне, необходим непрерывный массив (без пустых ячеек). Если все таки в диапазоне имеются пробелы, роль такого массива может сыграть столбец с датами. Тогда формула примет вид: =СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(A2:A300)-7;0;7;1))

Читайте так же:
Можно ли выпивать после лечения зуба

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

Пользуюсь вашей формулой уже 2 дня, прекрасно работает, ещё раз благодарю
за помощь.
В процессе работы с формулой возникла одна проблемка, связанная с особенностью
тех данных, на основании которых я строю таблицу. Я как-то упустил её из вида, когда
в первый раз обращался к вам за помощью. Может быть, поможете решить её сейчас?
Вы пишете:»…Для того, чтобы посчитать среднее значение последней недели,
мы должны вытянуть последние 7 строк со всего массива данных.
Вот откуда взялась формула СЧЁТЗ(B2:B300)-7, которая считает количество
значений и затем, от полученной цифры отнимается 7.» Т.е. формула в обоих
вариантах задает смещение на определенное количество строк, 7 в данном случае,
и уже для них рассчитывают СРЗНАЧ. В моем случае, при наличии пустых ячеек в диапазоне,
может возникнуть ситуация, когда все эти ячейки окажутся пустыми, т.к.
промежутки между данными, как выяснилось, могут быть в одних случаях 1 ячейка, а
в других 5,10, и даже 24 ячейки(!) могут оказаться пустыми. Столкнулся сегодня
с таким вариантом, сам не ожидал ничего подобного. Причем, все это постоянно меняется.
В таких условиях вести расчет в диапазоне, состоящем из определенного количества
строк, очень неудобно. Приходится все равно перелопачивать весь столбец вручную.
В этом случае гораздо лучше было бы считать диапазон, состоящий
не из определенного количества ячеек(7), как это сделано у вас, а диапазон, состоящий из
из определенного количества только лишь ЗАПОЛНЕННЫХ ячеек(7). Которые, с учетом пустых
ячеек между ними, могут занимать 7,10,20… любое количество ячеек, если считать их
подряд, от последней заполненной до седьмой, например, лежащей внутри диапазона В. Именно
такая формула мне необходима. Может быть, на базе вашей формулы, вы смогли бы создать
нечто подобное? Я был бы вам очень благодарен.
Применительно к примеру в вашей статье и с учетом решения для диапазона с пустыми
ячейками, которое вы мне уже подсказали, алгоритм действий мог бы выглядеть следующим
образом. СЧЕТЗ ведем по столбцу А с датами, поскольку он без пробелов. Затем из этого
столбца выбираем только те даты, для которых в столбце В есть заполненные ячейки. Выбираем
определенное количество таких ячеек из столбца В, например 7 последних, и уже для них рассчитываем
СРЗНАЧ. При это вся эта конструкция, естественно, должна работать так же, как и ваша формула,
если в ячейки А и В будут добавлены новые значения. Как-то так.
Но как именно все это выбрать и соотнести? При помощи ВПР, ЕСЛИ, работы с базами
данных, если столбец В обозначить как базу данных, или ещё каких-то функций и приемов?
Тут моих знаний не хватает, очень надеюсь на вашу помощь.

Здравствуйте, Ренат.
Помогите решить следующую проблему по использованию функции СМЕЩ. Есть таблица с двумя страницами. На первой с названием «расчет 2015» в диапазоне F5:F16 находятся данные по расходу эл.энергии в квт.час. за календарные промежутки времени, которые вносятся в диапазон E2:E16. На второй странице с названием «эл.тариф» идет расчет стоимости по сложным тарифам. В ячейку G3 вносится текущее значение =МАКС(‘расчет 2015’!F5:F16), а в ячейку G2 надо внести значение, которое является предыдущим к максимальному (т.е. за предыдущий период). Я составил формулу = =СМЕЩ(МАКС(‘расчет 2015’!F5:F16);-1;0;1;1)), но к сожалению результата нет. Программа говорит «ошибка в формуле». Сам расчет стоимости простой, но не удается перенести нужные данные по квт.часам.
Буду очень признателен, если сможете помочь.
Спасибо.

Владислав, если вам нужно найти следующую после максимальной величину, воспользуйтесь функцией НАИБОЛЬШИЙ, которая возвращает требуемый наибольший элемент в массиве. Подробнее про эту функцию можно прочитать в статье про функцию НАИБОЛЬШИЙ/ НАИМЕНЬШИЙ

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

Для этого сделал перечень авто и возможность, чтобы человек вручную по дням отмечал, какие из них будут доступны (т.к. надо чтобы можно было на сегодня и например на 3 дня вперед отметить доступные авто). После этого сделал еще одну таблицу в разрезе дней, куда переносятся доступные авто без пробелом и для каждого дня создал именованный диапазон (с помощью СМЕЩ, чтобы в конце не было пустых ячеек выпадающем списке).
Диапазоны каждого дня назвал д1, д2 … д31.
И теперь когда пытаюсь привязать эти диапазоны к Дате с помощью ДВССЫЛ, то выпадает ошибка в источнике (как я понимаю, т.к. ДВССЫЛ и СМЕЩ не совместимы?)
Что можно еще придумать, как можно сразу в источник прописать формулу со СМЕЩ, чтобы при выборе дня месяца — сразу выбирался перечень доступных машин этого дня.
Без СМЕЩ всё получается, но тогда пустые ячейки в перечне.
Буду очень благодарен за помощь.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector