Создать формулу, не учитывающую отфильтрованные строки

@AndreA SN · Регистрация: 26.08.2011

Студворк — интернет-сервис помощи студентам

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

Добавлено через 6 минут
формула должна быть типа
если не отфильтровано на листе1, то Лист1!В1=Лист2!В1

Добавлено через 1 минуту
Я знаю, что подобным свойством характеризуется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Но там нет простого приравнивания. Кроме того, она не работает с текстовыми данными

@Dinoxromniy · 12.03.2018, 09:46

Сообщение от AndreA SN

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

F5→выделить→только видимые ячейки, копировать, вставить.
С помощью формул вероятно тоже возможно, но тут нужно знать правило фильтрации строк.

Добавлено через 12 минут

Сообщение от AndreA SN

формула должна быть типа
если не отфильтровано на листе1, то Лист1!В1=Лист2!В1

Например, используйте функцию VBA (вставить код нужно обязательно в модуль в редакторе VBA - меню insert → module)

Visual Basic
1
2
3
4
5
6
7
Public Function IsTheRowHidden(ByVal rngRow As Range) As Boolean
    If rngRow.Rows.Item(1).EntireRow.Hidden Then
        IsTheRowHidden = True
    Else
        IsTheRowHidden = False
    End If
End Function

Функцию можно использовать прямо на рабочем листе например в связке с функцией "Если".

@AndreA SN · 12.03.2018, 09:46 **[ТС]**

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

@Dinoxromniy · 12.03.2018, 09:48

Сообщение от AndreA SN

Правил фильтрации, которые придется мне использовать при решении конкретной задачи, я не знаю.

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

@AndreA SN · 12.03.2018, 10:06 **[ТС]**

Функция - это хорошо. Но может есть формульное решение? У меня создалось впечатление, что со стандартными формулами Excel работает быстрее.

Добавлено через 16 минут
Сделал через функцию - скорость расчетов упала раз в 10...

Fairuza · 12.03.2018, 10:15

AndreA SN, без файла с примером разговор несколько абстрактный, не находите?

@AndreA SN · 12.03.2018, 10:55 **[ТС]**

Вот пример. Данные здесь абстрактны. Среди них есть текстовые, которые тоже нужно отображать. Формула работает с текстом некорректно: для моего случая текст из не скрытых строк НУЖНО отображать.
Функция работает корректно. Но на моем количестве данных - до 10-20 тыс строк - медленно.

@Step_UA · 12.03.2018, 13:08

Сообщение от AndreA SN

Функция работает корректно. Но на моем количестве данных - до 10-20 тыс строк - медленно.

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

shanemac51 · 12.03.2018, 13:15

я подобные задачи решаю обычно кодом
--добавляю рабочий столбец, куда от конца таблицы записываю результаты проверки, чтобы не потерять заголовочные строки
--пишу в него итог фильтрации или скрытия
--далее фильтрую по нему и копирую на другой лист

---
оказывается не я одна применяю дополнительный столбик

@AndreA SN · 12.03.2018, 14:49 **[ТС]**

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

@Dinoxromniy · 12.03.2018, 15:15

Сообщение от AndreA SN

Вообщем-то и я сделал дополнительный столбик...

AndreA SN, дополнительный столбик делать необязательно - сгодится любой столбик с цифрами (ну или дополнительный столбец с функцией строка, номером, или вообще любой цифрой кроме нуля). Если функция промежуточные итоги при суммировании одной этой ячейки дает ноль, значит эта строка скрыта. Можно использовать функцию если с условием "если равно нолю, то не показываем, если не равно - показываем".
Тогда формула будет (тэгом не оформляю) =ЕСЛИ(НЕ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;'исходные данные'!ссылка_на_ячейку_с_цифрой)=0);'исходные данные'!ссылка_на_отображаемую_ячейку;"")

@AleksSid · 12.03.2018, 17:03

Как вариант.

@AndreA SN · 12.03.2018, 18:43 **[ТС]**

Сообщение от Dinoxromniy

сгодится любой столбик с цифрами

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

Добавлено через 6 минут
AleksSid, а вот с этим можно поэкспериментировать...

@Dinoxromniy · 12.03.2018, 21:03

Сообщение от AndreA SN

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

Забавно. И как это сочетается с:

Сообщение от AndreA SN

вот с этим можно поэкспериментировать...

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

Сообщение от AndreA SN

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

В 2016 версии доступно 16384 столбцов, можете выбрать любой больше с номером более 10000 и вбить туда единицу в каждом последующем файле. Например кодом ниже:

Visual Basic
1
2
3
Public Sub insert_one()
ActiveSheet.Cells(1, 10001).Resize(ActiveSheet.UsedRange.Rows.Count, 1).Value = 1
End Sub

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

Сообщение от AndreA SN

на моем количестве данных - до 10-20 тыс строк

Для примера использовал ваш файл, строки скопировал в количестве 50 401.
Преобразовал неструктурированную таблицу в список по ctrl+L, имя оставил стандартное - "Таблица1"
Формула приняла совсем упрощенный вид (указана в стиле ссылок R1C1, так как ее в таком виде можно легко скопировать на все ячейки):

Code
1
=ЕСЛИ(НЕ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Таблица1[@])=0);'исходные данные'!RC;"")

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

Сообщение от AndreA SN

Но на моем количестве данных - до 10-20 тыс строк

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

@AndreA SN · 13.03.2018, 00:31 **[ТС]**

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

Сообщение от Dinoxromniy

полностью ли соответствует поставленная задача желаемой цели

на самом деле порождены желанием понять - что происходит?
Я здесь не утруждал никого спецификой моей работы. Попытался вычленить маленький вопрос и узнать - что же можно изменить в том, что я уже сделал?
Потому что изменить можно всегда. Заметьте: я не задаю здесь вопросы типа :"Сделайте мне обработчик, чтобы было на все случаи жизни". Я пишу его сам. Про все - не знаю, но большинство охватываю.
Я занимаюсь биометрическими обработками, корреляционным и дисперсионным анализом. Но не теоретически (хотя теория статистики тоже от меня терпит), а на практических данных, по определению и на практике поступающих ко мне в свальном порядке. Что мне придет завтра и в каком порядке - не знает даже мой клиент.
Анализ этих данных, изучение самой возможности анализа данных в различных взаимосвязях, - и есть предмет этой моей работы.
Встроенными методиками не пользуюсь.
Поверьте: 10-20 тыс. строк - это мизер в моей работе. Речь не идет о ситуации, когда я получил данные и с ними - работаю. Нет. Речь идет о ситуации, когда я едва успеваю просмотреть данные, а уже должен сделать выводы о тенденциях, взаимосвязях, рангах, корреляциях, выдать профили и т.п. И всё это - в телефонном режиме. Отсюда повышенные требования к скорости обработки и обязательному учету результатов фильтрации. В этом вале данных тип данных в том или ином столбце для меня важен с точки зрения - вычисляемые или невычисляемые в нём данные? Если невычисляемые - то можно ли сделать качественный дисперсионный анализ или нельзя? Всё остальное идет под отсев выбросов и количественные анализы.
В итоге у меня коэффициенты корреляции, дисперсии, их достоверности и статистика признаков в таблицы записываются оптом.
Программа сугубо специфическая. Но я действительно поставил себе задачу максимально упростить работу с ней, наделив ее минимальной "соображалкой" по таким вопросам.
Кто-то сейчас скажет, что не стоит замахиваться на профессиональные статкомплексы. Но вот тут я не соглашусь в принципе. Я много шарился по этим статистикам и прочему доброделу. Вот только на все компы моих клиентов их не поставишь. А excel есть везде. Да и люблю контролировать процесс полностью. А у нас и "у них" есть вещи, принципиально различающиеся в методике расчетов. Так что делаю всё ручкамипо нашим стандартным методикам.
Наработки родились не вчера. Последний вариант является примерно 10 версией с качественными изменениями.

@AleksSid · 13.03.2018, 06:14

AndreA SN, на результирующем листе можно обойтись и без доп. столбца, формула в ячейку A1.

Code
1
=ВПР(СТРОКА();ваш диапазон;СТОЛБЕЦ()+1;0)

.

Fairuza · 13.03.2018, 08:38

Сообщение от Dinoxromniy

F5→выделить→только видимые ячейки, копировать, вставить.

Чем это решение не устраивает?

@Dinoxromniy · 13.03.2018, 09:21

Сообщение от AndreA SN

Наработки родились не вчера. Последний вариант является примерно 10 версией с качественными изменениями.

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

Code
1
=ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;'исходные данные'!A1:GS1)>0;'исходные данные'!A1;"")

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

Соответственно, устраивает ли вас подобная формула?

Стремиться нужно к варианту, предложенному AleksSid - в нем пропускаются пустые строчки. Нужно поковыряться с формулой массива, чтобы не было пустых столбцов - но для начала хотелось бы понять, насколько такой вариант в принципе вас устраивает (ну и вопросы быстродействия с формулой массива).

@AndreA SN · 13.03.2018, 13:30 **[ТС]**

Сообщение от Fairuza

то решение не устраивает

необходимостью выделить - копировать - вставить... Ручки минимизируем в этом вопросе

Добавлено через 2 минуты

Сообщение от Dinoxromniy

Стремиться нужно к варианту,

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

Добавлено через 54 минуты
AleksSid, я не совсем уверен в результате. Функция ВПР подразумевает сортировку данных по возрастанию. Действительно хорошим выходом из ситуации является не сортировать данные, а сделать первым столбец с прогрессией простых чисел по возрастанию. Это - красивый на мой взгляд выход из ситуации, когда нужно использование ВПР на огромных массивах данных.

Сообщение от Dinoxromniy

руководствоваться предлагаю принципом наименьших усилий

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

Сообщение от Dinoxromniy

=ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;'исходные данные'!A1:GS1)>0;'исходные данные'!A1;"")

действительно оказался интересен в модификации

Code
1
=ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;'исходные данные'!R)>0;ЕСЛИ('исходные данные'!RC="";"";'исходные данные'!RC);"")

но при этом я взял параметр 103 - это счет заполненных ячеек. В этом случае даже если вообще нет числовых данных (а такое может быть в принципе), текстовая часть строки будет отображаться.
Далее - взял в обработку всю строку, а не диапазон ячеек одной строки. Посмотрим, как по быстродействию будет показывать.
Кроме того, пришлось ввести еще одну проверку ЕСЛИ, так как вместо пустышек с листа-исходника возвращало нули.
Вот этот вариант сейчас и апробирую.

Добавлено через 1 час 27 минут
не по теме
Fairuza, спасибо. Я никак не пойму - как форматировать формулы excel здесь

@AleksSid · 13.03.2018, 14:18

Сообщение от AndreA SN

AleksSid, я не совсем уверен в результате. Функция ВПР подразумевает сортировку данных по возрастанию.

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

Новые блоги и статьи Все статьи Все блоги /
Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .	Знаешь почему 90% людей редко бывают счастливыми? kumehtar 14.04.2026 Потому что они ждут. Ждут выходных, ждут отпуска, ждут удачного момента. . . а удачный момент так и не приходит.
Фиксация колонок в отчете СКД Maks 14.04.2026 Фиксация колонок в СКД отчета типа Таблица. Задача: зафиксировать три левых колонки в отчете. Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка) / / . . .	Настройки VS Code Loafer 13.04.2026 { "cmake. configureOnOpen": false, "diffEditor. ignoreTrimWhitespace": true, "editor. guides. bracketPairs": "active", "extensions. ignoreRecommendations": true, . . .	Оптимизация кода на разграничение прав доступа к элементам формы Maks 13.04.2026 Алгоритм из решения ниже реализован на нетиповом документе, разработанного в конфигурации КА2. Задачи, как таковой, поставлено не было, проделанное ниже исключительно моя инициатива. Было так:. . .	Контроль заполнения и очистка дат в зависимости от значения перечислений Maks 12.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "ПланированиеПерсонала", разработанного в конфигурации КА2. Задача: реализовать контроль корректности заполнения дат назначения. . .

@AndreA SN 1021 / 125 / 2 Регистрация: 26.08.2011 Сообщений: 1,219 Записей в блоге: 2

	Создать формулу, не учитывающую отфильтрованные строки 12.03.2018, 09:33. Показов 13571. Ответов 19 Метки нет (Все метки) Существует набор данных. Мне нужно скопировать этот набор (и цифровые и текстовые данные) на другой лист за исключением строк, скрытых фильтрацией или вручную. По возможности это должно быть сделано стандартными формулами excel Добавлено через 6 минут формула должна быть типа если не отфильтровано на листе1, то Лист1!В1=Лист2!В1 Добавлено через 1 минуту Я знаю, что подобным свойством характеризуется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ Но там нет простого приравнивания. Кроме того, она не работает с текстовыми данными 0

@AndreA SN 1021 / 125 / 2 Регистрация: 26.08.2011 Сообщений: 1,219 Записей в блоге: 2
	12.03.2018, 09:46 [ТС]
	Нет. Только формулами. Правил фильтрации, которые придется мне использовать при решении конкретной задачи, я не знаю. Нужно отследить факт фильтрации строки и не принять ее во внимание при копировании данных 0

@AndreA SN 1021 / 125 / 2 Регистрация: 26.08.2011 Сообщений: 1,219 Записей в блоге: 2
	12.03.2018, 10:06 [ТС]
	Функция - это хорошо. Но может есть формульное решение? У меня создалось впечатление, что со стандартными формулами Excel работает быстрее. Добавлено через 16 минут Сделал через функцию - скорость расчетов упала раз в 10... 0

Fairuza 6027 / 3221 / 719 Регистрация: 23.11.2010 Сообщений: 10,760
	12.03.2018, 10:15
	AndreA SN, без файла с примером разговор несколько абстрактный, не находите? 0

shanemac51 Модератор 12231 / 5078 / 814 Регистрация: 07.08.2010 Сообщений: 14,941 Записей в блоге: 4
	12.03.2018, 13:15
	я подобные задачи решаю обычно кодом --добавляю рабочий столбец, куда от конца таблицы записываю результаты проверки, чтобы не потерять заголовочные строки --пишу в него итог фильтрации или скрытия --далее фильтрую по нему и копирую на другой лист --- оказывается не я одна применяю дополнительный столбик 1

@AndreA SN 1021 / 125 / 2 Регистрация: 26.08.2011 Сообщений: 1,219 Записей в блоге: 2
	12.03.2018, 14:49 [ТС]
	Вообщем-то и я сделал дополнительный столбик... Но удобнее записывать его в начале массива данных, а не в конце. тогда он будет всегда доступен для фильтрации. Особенно если закрепить области. Но проблемы скорости это не решило... Думал - существует какое-то решение использованием формул excel... Пока буду так... 0