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

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

 

Разбор только Формулы №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Скачать

Получить Значение по ИНДЕКС у из массива констант внутри формулы

Автор:
Получить Значение по ИНДЕКС у из массива констант внутри формулы
Получить Значение по ИНДЕКС у из массива констант внутри формулы

Основная формула:

=ИНДЕКС({«пн»:»вт»:»ср»:»чт»:»пт»:»сб»:»вс»};C7)

Возвращает значение элемента в массиве, выбранном с помощью индекса строки.

Ищем что находится по третьему индексу в массиве.

Получить Значение по ИНДЕКС у из массива констант внутри формулы

Суммируем текст. СУММ текста  как число с функциями Ч, ИНДЕКС , ЕСЛИ и ПОИСКПОЗ

Автор:

Суммируем текст. СУММ текста  как число с функциями Ч, ИНДЕКС , ЕСЛИ и ПОИСКПОЗ

Перевод текстовых значений в числа и суммировать результат, вы можете использовать СУММ ИНДЕКС Ч ЕСЛИ ПОИСКПОЗ. В показанном примере, формула в K8-это:

{=СУММ(ИНДЕКС($O$8:$O$11;Ч(ЕСЛИ(1;ПОИСКПОЗ(F8:J8;$N$8:$N$11;0)))))}

  1. «Оценка» это диапазон $N$8:$N$11

      2.  «Балл» —  диапазон $O$8:$O$11.

      3. Диапазон для поиска и суммирования F8:J8

Примечание:

это формула массива, и должно вводиться с помощью

Control + Shift + ввод.

Как эта формула работает

В основе этой формулы используется функции ИНДЕКС и ПОИСКПОЗ , для преобразования текстовых значений в числа из таблицы подстановки. Например, перевести «Зачет» на соответствующий Балл, мы будем использовать:

=Индекс(Балл;ПОИСКПОЗ(«Зачет»;Оценка;0))

В ячейке : М12

=ИНДЕКС($O$8:$O$11;ПОИСКПОЗ(«Зачет»;$N$8:$N$11;0))

Формула возвращает 5.

Однако есть сложность так как мы хотим преобразовывать и суммировать ряд текстовых значений а не одно «Зачет» в Столбце N в Баллы столбца O. Нам нужен индекс, чтобы вернуть более одного результата. Формула такая:

=СУММ($O$8:$O$11;ПОИСКПОЗ(F8:J8;$N$8:$N$11;0))

После ПОИСКПОЗ, у нас есть массив из 5 элементов:

Однако, если вы попробуете это, то функция ИНДЕКС вернет не тот результат суммы который мы хотели бы увидеть. Для получения нормального результата необходимо использовать ПОИСКПОЗ  в функции Ч и с функцией  ЕСЛИ :

Ч(ЕСЛИ(1,ПОИСКПОЗ(F8:J8;$N$8:$N$11;0)))

Файл Суммируем текст. СУММ текста  как число с функциями Ч, ИНДЕКС , ЕСЛИ и ПОИСКПОЗ DEVSAP

Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

Автор:

Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

=ИНДЕКС(данные ПОИСКПОЗ(критерий 1;столбец;1);ПОИСКПОЗ(критерий 2;строк;1))

Связанные формулы

Двусторонний поиск с ВПР

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

В показанном примере, формула k6 является:

=ИНДЕКС($D$6:$H$10;ПОИСКПОЗ($K$4;$D$5:$H$5);ПОИСКПОЗ($K$5;$C$6:$C$10))

Заметим, что эта формула делает и «приблизительное соответствие», поэтому значения строки и столбца должны быть отсортированы.

Как эта формула работает

Ядром этой формулы является индекс, который является просто получение значения от $D$6:$H$10 («данные») на основе номера строки и номера столбца.

=ИНДЕКС($D$6:$H$10;столбец;строка)

Чтобы получить номера строк и столбцов, мы используем ПОИСКПОЗ, настроенные на приблизительное соответствие, установив 3 аргумент 1 (истина):

ПОИСКПОЗ($K$5;$C$6:$C$10) // получаем количество строк
ПОИСКПОЗ($K$4;$D$5:$H$5) // получаем номер столбца

В примере, ПОИСКПОЗ вернет 60, Когда Столбц С = «3», а, строка d «D» = «4» .

В итоге, формула сводится к:

=Индекс($D$6:$H$10, 3, 4) = 60

 

 

Файл Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС ) DEVSAP