Переход по листам с помощью функции ГИПЕРССЫЛКА() и дополнительных функции ПОДСТАВИТЬ() ЯЧЕЙКА() ДВССЫЛ() СТРОКА() ИНДЕКС() ПОИСКПОЗ()

Настоящая лаборатория порно здесь. Переходите и наслаждайтесь.
Автор:
Переход по листам с помощью функции ГИПЕРССЫЛКА() и дополнительных функции ПОДСТАВИТЬ() ЯЧЕЙКА() ДВССЫЛ() СТРОКА() ИНДЕКС() ПОИСКПОЗ()
Переход по листам с помощью функции ГИПЕРССЫЛКА() и дополнительных функции ПОДСТАВИТЬ() ЯЧЕЙКА() ДВССЫЛ() СТРОКА() ИНДЕКС() ПОИСКПОЗ()

 

Разбор только Формулы №2

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-3)&»!A1″));»‘»;»»);СТРОКА()-3)

Задача:

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

Используемые функции:

ГИПЕРССЫЛКА(), ПОДСТАВИТЬ(), ЯЧЕЙКА(), ДВССЫЛ(), СТРОКА(), ИНДЕКС(), ПОИСКПОЗ(),

Решение :

В первую очередь нам надо сформировать правильный адрес для гиперссылки.

ГИПЕРССЫЛКА(адрес;[имя])

адрес гиперссылки будет получен с помощью функции ЯЧЕЙКА(«тип сведений» ; «ссылка») и тип сведений выбираем «адрес» а для формирования  ссылки нам понадобится функция ДВССЫЛ() с значением такого вида «Лист»&(СТРОКА()-2)&»!A1″

объединяем текст «Лист» с формулой СТРОКА() которая определяет номер строки собственно ячейки, дальше объединяем текст  «!A1» и получаем ссылку на ячейку.

Будет Ошибка!! Если присмотрится в ссылке есть одинарная кавычка после Лист4‘ которую надо убрать ‘  для получения корректного адреса.

убрать одинарную кавычку нам поможет функция ПОДСТАВИТЬ()

ПОДСТАВИТЬ(текст в котором ищем: найти символ: заменить символ на пусто)

получается что то на подобии:

ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-2)&»!A1″));»‘»;»»)

и задача практически решена.

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

Получится вот так:

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-3)&»!A1″));»‘»;»»);«Имя гиперссылки»)

Так же можно добавить и другие варианты наименования ссылки.

используя функцию СТРОКА() получаем номер строки из которого надо вычесть количество строк до нужный диапазон.

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ЯЧЕЙКА(«адрес»;ДВССЫЛ(«Лист»&(СТРОКА()-2)&»!A1″));»‘»;»»);СТРОКА()-2)

Скачайте файл с образцом решения для разбора.

 

 

 

 

Файл Переход по листам с помощью функции ГИПЕРССЫЛКА() и дополнительных функции ПОДСТАВИТЬ() ЯЧЕЙКА() ДВССЫЛ() СТРОКА() ИНДЕКС() ПОИСКПОЗ()Скачать

Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ

Автор:
Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ
Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ
=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)
 
=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)

Задача: Найти недостающие записи при сравнении двух списков. 

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

ЕСЛИ(), СЧЕТЕСЛИ(), СТРОКА(), ИНДЕКС() и НАИМЕНЬШИЙ()

Логика решения:

В первую очередь определяем количество не совпадений в столбцах C и D  используя функцию СЧЕТЕСЛИ() все что не найдено будет с значением 0.

=СЧЁТЕСЛИ($C$5:$C$10;D5

все не найденные позиции как раз нам и нужны

Второе действие надо определить строку отсутствующей (то-есть с 0) записи в списке 

Для этого используем логическую функцию ЕСЛИ() и функцию  СТРОКА()

Логическое выражение  

СЧЁТЕСЛИ($C$5:$C$10;D5)=0

Нам надо найти все 0 и проставить номера строк  не найденных элементов из списка, при истинности логического выражения мы задаем действие которое определяет строку не найденного элемента. При истине ссылка на ячейку с функцией СТРОКА(D7) .

=СТРОКА(D7)

 

да и если ЛОЖЬ тогда пишем «есть в списке 1» 

Полностью формула выглядит так

=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)

Для формирования нужного нам списка отсутствующих элементов мы будем использовать замечательные функции НАИМЕНЬШИЙ() и 

ИНДЕКС()

Так как у нас есть номера строк или цифры в столбике E то мы можем использовать диапазон $E$5:$E$10 таблички или списка найти номер строки наименьшего числа с индексу из столбца G (номер строки определяется от первой строки)

G5- это номер индекса который нам надо вывести то есть от первого до последнего нужного элемента 1 до 10.

(таким образом  можно создавать ТОП продаж или ещё что то интересное) 

да и надо обязательна вычесть количество строк (4) до первой строки списка для  правильного подсчета.

НАИМЕНЬШИЙ($E$5:$E$10;G5)-4

После определения строки нужной нам позиции мы используем функцию ИНДЕКС() для вывода не номера строки а наименование не достающего элемента.

Указываем диапазон $D$5:$D$10 для поиска и номер строки НАИМЕНЬШИЙ($E$5:$E$10;G5)-4  элемента для формирования нового списка недостающих элементов.

Полностью формула выглядит так:

=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)

 

Вы можете скачать файл с готовым решением.

 

Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ EXCEL DEVSAPxlsxСкачать