Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск значений в диапазоне ячеек расположенных по диагонали - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений в диапазоне ячеек расположенных по диагонали (Формулы/Formulas)
Поиск значений в диапазоне ячеек расположенных по диагонали
Kaktus86307 Дата: Вторник, 12.01.2021, 00:06 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Уважаемые знатоки!
Помогите, пожалуйста,
произвести поиск значений в диапазоне ячеек расположенных по диагонали.
Все подробности в приложенном файле-примере:
16_1.xlsx
К сообщению приложен файл: 16_1.xlsx (35.8 Kb)
 
Ответить
СообщениеУважаемые знатоки!
Помогите, пожалуйста,
произвести поиск значений в диапазоне ячеек расположенных по диагонали.
Все подробности в приложенном файле-примере:
16_1.xlsx

Автор - Kaktus86307
Дата добавления - 12.01.2021 в 00:06
Светлый Дата: Вторник, 12.01.2021, 12:50 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
Вот такое решение. Строка и столбец в группе и подкрашена УФ сама ячейка в группе.
К сообщению приложен файл: 16_1-1.xlsx (40.5 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
Вот такое решение. Строка и столбец в группе и подкрашена УФ сама ячейка в группе.

Автор - Светлый
Дата добавления - 12.01.2021 в 12:50
Kaktus86307 Дата: Среда, 13.01.2021, 00:55 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Светлый, спасибо!
Но, если честно, я немного не понял.
И непонятно, как продолжать таблицу?

Да, к тому же, если нужна нумерация столбцов,
то могу освободить пару строк (3-ю и 4-ю) в заголовках колонок, где можно будет проставить нумерацию.
Возможно, это облегчит задачу, если она решаема.


Сообщение отредактировал Kaktus86307 - Среда, 13.01.2021, 00:56
 
Ответить
СообщениеСветлый, спасибо!
Но, если честно, я немного не понял.
И непонятно, как продолжать таблицу?

Да, к тому же, если нужна нумерация столбцов,
то могу освободить пару строк (3-ю и 4-ю) в заголовках колонок, где можно будет проставить нумерацию.
Возможно, это облегчит задачу, если она решаема.

Автор - Kaktus86307
Дата добавления - 13.01.2021 в 00:55
Светлый Дата: Среда, 13.01.2021, 10:02 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
И непонятно, как продолжать таблицу?
Если вправо, то копируете формулы, настраиваете на другую группу. Для разного наклона диагоналей своя формула.
Если хотите вниз продолжить, то формулу придётся сильно переделать.
Что тогда брать за образец поиска? Значение из самой нижней строки группы?


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
И непонятно, как продолжать таблицу?
Если вправо, то копируете формулы, настраиваете на другую группу. Для разного наклона диагоналей своя формула.
Если хотите вниз продолжить, то формулу придётся сильно переделать.
Что тогда брать за образец поиска? Значение из самой нижней строки группы?

Автор - Светлый
Дата добавления - 13.01.2021 в 10:02
Kaktus86307 Дата: Среда, 13.01.2021, 13:35 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Цитата Светлый, 13.01.2021 в 10:02, в сообщении № 4 ()
Что тогда брать за образец поиска? Значение из самой нижней строки группы?

Нет.
Я имел в виду, что берём ячейку в Колонке 1 и по диагонали-лесенке-вверх от неё ищем её значение (то есть для каждой ячейки в Колонке 1, - получается своя диагональ).
Найдя первое совпадение, вычисляем, на сколько строк оно выше искомого (то есть разница строк между найденным и исходным).
Заносим эту разницу в Колонку 3 (в ячейку соответствующую той, значение которой мы ищем).
В Колонку 5, заносим общее количество найденных значений в этой диагонали-лесенке (если N=0, то ищем до верха таблицы (т.е. до 5 строки), а если N=число, - на указанное число ячеек вверх от исходной. Значение N для Колонки 1 прописывается в ячейку DT1, N для Колонки 2 прописывается в ячейку DT2).
Такие же вычисления производим для каждой ячейки из Колонки 1 и заносим результат в соответствующие ячейки Колонки 3 и Колонки 5. Это для трёх желтых Колонок. Можно сделать не с 5 строки, а хотя-бы с 25.
Для синих Колонок (то есть для Колонка 2, Колонка 4, Колонка 6), - всё то же самое, только для диагонали-влево.

Колонка 1 и Колонка 2 будут пополняться новыми данными, то есть таблица будет расти и для Колонок 3, 4, 5, 6 нужно иметь возможность протягивать формулы вниз.
Если сложно реализовать протяжку формулы вправо, то, в принципе, могу вручную каждый столбец переправить.
Главное, чтобы поиск производился вплоть до верха таблицы и формулы протягивались вниз.

P.S.: А несколько ячеек в нижней строке я только для примера заполнил, чтобы был понятен принцип и видна диагональ-лесенка. Остальные ячейки не заполнял, так как слишком долго было бы вручную это просчитывать.

P.S.2: поправил нумерацию текстового описания в файле-примере и добавил туда нумерацию столбцов (на всякий случай).
Файл прилагаю.
К сообщению приложен файл: 6057973.xlsx (36.3 Kb)


Сообщение отредактировал Kaktus86307 - Среда, 13.01.2021, 13:40
 
Ответить
Сообщение
Цитата Светлый, 13.01.2021 в 10:02, в сообщении № 4 ()
Что тогда брать за образец поиска? Значение из самой нижней строки группы?

Нет.
Я имел в виду, что берём ячейку в Колонке 1 и по диагонали-лесенке-вверх от неё ищем её значение (то есть для каждой ячейки в Колонке 1, - получается своя диагональ).
Найдя первое совпадение, вычисляем, на сколько строк оно выше искомого (то есть разница строк между найденным и исходным).
Заносим эту разницу в Колонку 3 (в ячейку соответствующую той, значение которой мы ищем).
В Колонку 5, заносим общее количество найденных значений в этой диагонали-лесенке (если N=0, то ищем до верха таблицы (т.е. до 5 строки), а если N=число, - на указанное число ячеек вверх от исходной. Значение N для Колонки 1 прописывается в ячейку DT1, N для Колонки 2 прописывается в ячейку DT2).
Такие же вычисления производим для каждой ячейки из Колонки 1 и заносим результат в соответствующие ячейки Колонки 3 и Колонки 5. Это для трёх желтых Колонок. Можно сделать не с 5 строки, а хотя-бы с 25.
Для синих Колонок (то есть для Колонка 2, Колонка 4, Колонка 6), - всё то же самое, только для диагонали-влево.

Колонка 1 и Колонка 2 будут пополняться новыми данными, то есть таблица будет расти и для Колонок 3, 4, 5, 6 нужно иметь возможность протягивать формулы вниз.
Если сложно реализовать протяжку формулы вправо, то, в принципе, могу вручную каждый столбец переправить.
Главное, чтобы поиск производился вплоть до верха таблицы и формулы протягивались вниз.

P.S.: А несколько ячеек в нижней строке я только для примера заполнил, чтобы был понятен принцип и видна диагональ-лесенка. Остальные ячейки не заполнял, так как слишком долго было бы вручную это просчитывать.

P.S.2: поправил нумерацию текстового описания в файле-примере и добавил туда нумерацию столбцов (на всякий случай).
Файл прилагаю.

Автор - Kaktus86307
Дата добавления - 13.01.2021 в 13:35
Светлый Дата: Четверг, 14.01.2021, 14:54 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Исправил первый файл.
Формулы из первой строки можно протянуть вниз, сколько надо.
В каждой колонке своя формула. Протягивается вниз и вправо в пределах колонки.
Для изменения количества столбцов в колонках, в формулах придётся исправлять константы и диапазоны. Сами формулы будут работать.
К сообщению приложен файл: 16_1-2.xlsx (48.4 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеИсправил первый файл.
Формулы из первой строки можно протянуть вниз, сколько надо.
В каждой колонке своя формула. Протягивается вниз и вправо в пределах колонки.
Для изменения количества столбцов в колонках, в формулах придётся исправлять константы и диапазоны. Сами формулы будут работать.

Автор - Светлый
Дата добавления - 14.01.2021 в 14:54
Kaktus8 Дата: Пятница, 15.01.2021, 09:45 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Ух-ты!
Да, Вы - гений, честное слово!
Сомневался, что это возможно!
Всё работает именно так, как нужно. Спасибо, большое! yes

Правда, при переносе формул в другой файл возник затык: поиск по правой диагонали перенёсся без проблем,
а по левой, - никак. Ищет по диагонали сдвинутой на единичку вправо. Всё перепроверил 10 раз и никак не могу понять почему так происходит.
Копирую один в один.
Попробовал на втором файле-примере (который выкладывал позже), - всё нормально, а на рабочем, - никак пока не получается.
Масштабирование еще не успел проверить.

Цитата Светлый, 14.01.2021 в 14:54, в сообщении № 6 ()
в формулах придётся исправлять константы и диапазоны

С диапазонами всё, вроде, понятно. А вот какие константы за что отвечают?


Сообщение отредактировал Kaktus8 - Пятница, 15.01.2021, 10:17
 
Ответить
СообщениеУх-ты!
Да, Вы - гений, честное слово!
Сомневался, что это возможно!
Всё работает именно так, как нужно. Спасибо, большое! yes

Правда, при переносе формул в другой файл возник затык: поиск по правой диагонали перенёсся без проблем,
а по левой, - никак. Ищет по диагонали сдвинутой на единичку вправо. Всё перепроверил 10 раз и никак не могу понять почему так происходит.
Копирую один в один.
Попробовал на втором файле-примере (который выкладывал позже), - всё нормально, а на рабочем, - никак пока не получается.
Масштабирование еще не успел проверить.

Цитата Светлый, 14.01.2021 в 14:54, в сообщении № 6 ()
в формулах придётся исправлять константы и диапазоны

С диапазонами всё, вроде, понятно. А вот какие константы за что отвечают?

Автор - Kaktus8
Дата добавления - 15.01.2021 в 09:45
Kaktus8 Дата: Пятница, 15.01.2021, 14:11 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Выяснил, когда возникает ошибка.
Если колонки с формулами сдвинуть правее от исходных данных, то возникает ошибка,
причём чем дальше они сдвинуты вправо, тем больший сдвиг в возникает в диагоналях.
Вот, в файле-примере, скопировал два раза (для оценки сдвига) Колонку 3 и Колонку 4 правее (за пределы исходной таблицы), - здесь наглядно видно сдвиг. Тут в первой копии колонок, сдвиг равен 1, а в скопированных ещё правее, достигает уже 7.
Файл прилагаю.
Масштабирование работает, но на сдвинутых вправо колонках ещё не проверил.

P.S.: забыл сказать, что в начале писал под новым ником, а теперь под старым (восстановленным).
К сообщению приложен файл: 16_1-3.xlsx (74.8 Kb)


Сообщение отредактировал Kaktus8 - Пятница, 15.01.2021, 14:18
 
Ответить
СообщениеВыяснил, когда возникает ошибка.
Если колонки с формулами сдвинуть правее от исходных данных, то возникает ошибка,
причём чем дальше они сдвинуты вправо, тем больший сдвиг в возникает в диагоналях.
Вот, в файле-примере, скопировал два раза (для оценки сдвига) Колонку 3 и Колонку 4 правее (за пределы исходной таблицы), - здесь наглядно видно сдвиг. Тут в первой копии колонок, сдвиг равен 1, а в скопированных ещё правее, достигает уже 7.
Файл прилагаю.
Масштабирование работает, но на сдвинутых вправо колонках ещё не проверил.

P.S.: забыл сказать, что в начале писал под новым ником, а теперь под старым (восстановленным).

Автор - Kaktus8
Дата добавления - 15.01.2021 в 14:11
Светлый Дата: Понедельник, 18.01.2021, 19:57 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
В зелёных ячейках универсальные формулы. Своя формула для каждой колонки.
Для проверки колонки увеличил с 20 до 21 столбца.
К сообщению приложен файл: 16_1-4.xlsx (79.8 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ зелёных ячейках универсальные формулы. Своя формула для каждой колонки.
Для проверки колонки увеличил с 20 до 21 столбца.

Автор - Светлый
Дата добавления - 18.01.2021 в 19:57
Kaktus8 Дата: Суббота, 23.01.2021, 23:49 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо, большое!
Всё получилось, - вставил в рабочий файл. Формула получилась очень удобной для использования.
Правда, в большой таблице возникает очень сильная загрузка процессора. Считает по 10 минут после каждого действия в таблице.
А возможен вариант, когда поиск по диагоналям производится не до верха таблицы, а в пределах N-строк вверх от текущей ячейки (где N можно менять)?
Возможно, в этом случае, удастся подобрать компромиссный вариант между производительностью и глубиной поиска.

И ещё, для подсчёта количества, тоже бы формулу поправить, чтобы можно было двигать куда угодно.


Сообщение отредактировал Kaktus8 - Суббота, 23.01.2021, 23:50
 
Ответить
СообщениеСпасибо, большое!
Всё получилось, - вставил в рабочий файл. Формула получилась очень удобной для использования.
Правда, в большой таблице возникает очень сильная загрузка процессора. Считает по 10 минут после каждого действия в таблице.
А возможен вариант, когда поиск по диагоналям производится не до верха таблицы, а в пределах N-строк вверх от текущей ячейки (где N можно менять)?
Возможно, в этом случае, удастся подобрать компромиссный вариант между производительностью и глубиной поиска.

И ещё, для подсчёта количества, тоже бы формулу поправить, чтобы можно было двигать куда угодно.

Автор - Kaktus8
Дата добавления - 23.01.2021 в 23:49
Светлый Дата: Воскресенье, 24.01.2021, 22:20 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Формулу можно исправить до такой:
Код
=МИН(ЕСЛИ(ИНДЕКС($D$1:$X5;Ч(ИНДЕКС(СТРОКА()-СТРОКА(ДВССЫЛ("1:"&МИН($DV$3;СТРОКА()-5)));));Ч(ИНДЕКС(ОСТАТ(СТРОКА(ДВССЫЛ("1:"&МИН($DV$3;СТРОКА()-5)))+СТОЛБЕЦ()-СТОЛБЕЦ($DW6);СЧЁТ($D$5:$X$5))+1;)))=D6;СТРОКА(ДВССЫЛ("1:"&МИН($DV$3;СТРОКА()-5)))))
Изменилась только эта часть
Код
"1:"&МИН($DV$3;СТРОКА()-5)
было
Код
"1:"&СТРОКА()-5


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Воскресенье, 24.01.2021, 22:23
 
Ответить
СообщениеФормулу можно исправить до такой:
Код
=МИН(ЕСЛИ(ИНДЕКС($D$1:$X5;Ч(ИНДЕКС(СТРОКА()-СТРОКА(ДВССЫЛ("1:"&МИН($DV$3;СТРОКА()-5)));));Ч(ИНДЕКС(ОСТАТ(СТРОКА(ДВССЫЛ("1:"&МИН($DV$3;СТРОКА()-5)))+СТОЛБЕЦ()-СТОЛБЕЦ($DW6);СЧЁТ($D$5:$X$5))+1;)))=D6;СТРОКА(ДВССЫЛ("1:"&МИН($DV$3;СТРОКА()-5)))))
Изменилась только эта часть
Код
"1:"&МИН($DV$3;СТРОКА()-5)
было
Код
"1:"&СТРОКА()-5

Автор - Светлый
Дата добавления - 24.01.2021 в 22:20
Kaktus8 Дата: Понедельник, 25.01.2021, 21:52 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо!
Всё стало шевелиться намного быстрее!
Только, если возможно, прошу:
1) Если число не найдено, то в ячейку прописывается 0 (ноль).
Можно ли сделать так, чтобы вместо нуля прописывалось значение N+1 (то есть количество строк указанное в ячейке $DV$3 + единица).
2) Подскажите, пожалуйста, как аналогичным образом переделать формулу (это тот же самый поиск, но только по вертикали)
Код
{=СТРОКА($B85)-МАКС((ЕСЛИ($X$5:$X84=IK$4;СТРОКА($X$5:$X84))))}
чтобы она искала в пределах N строк вверх от текущей ячейки и если 0, то прописывала значение N+1 (в общем, то же самое, что и для Вашей формулы).


Сообщение отредактировал Kaktus8 - Понедельник, 25.01.2021, 21:53
 
Ответить
СообщениеСпасибо!
Всё стало шевелиться намного быстрее!
Только, если возможно, прошу:
1) Если число не найдено, то в ячейку прописывается 0 (ноль).
Можно ли сделать так, чтобы вместо нуля прописывалось значение N+1 (то есть количество строк указанное в ячейке $DV$3 + единица).
2) Подскажите, пожалуйста, как аналогичным образом переделать формулу (это тот же самый поиск, но только по вертикали)
Код
{=СТРОКА($B85)-МАКС((ЕСЛИ($X$5:$X84=IK$4;СТРОКА($X$5:$X84))))}
чтобы она искала в пределах N строк вверх от текущей ячейки и если 0, то прописывала значение N+1 (в общем, то же самое, что и для Вашей формулы).

Автор - Kaktus8
Дата добавления - 25.01.2021 в 21:52
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений в диапазоне ячеек расположенных по диагонали (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!