ArtemT1987

ArtemT1987

Пикабушник
Дата рождения: 23 февраля 1987
поставил 71793 плюса и 64 минуса
отредактировал 0 постов
проголосовал за 0 редактирований
Награды:
5 лет на Пикабу
2056 рейтинг 533 подписчика 23 подписки 11 постов 5 в горячем

Обновление: видео

Скажите, кто догадался сделать это фичу с видосом в новом полноэкранном режиме аля тик-ток?? Нет теперь полосы прокрутки, все глючит. Верните взад все, пожалуйста!!!

@admin

Excel At Excel вып.4: Универсальные ссылки на список

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

Лист с элементами:

Excel At Excel вып.4: Универсальные ссылки на список Microsoft Excel, Список, Длиннопост

Лист с расчетами:

Excel At Excel вып.4: Универсальные ссылки на список Microsoft Excel, Список, Длиннопост

Каждый знает, как сделать лист с расчетами: ссылками вручную. Но если таких элементов 30 или 40? Это будет колоссальная трата времени. При этом при добавлении новых элементов в список на листе со списком, необходимо будет добавлять элементы на листе с расчетами, при этом соблюдая порядок.

Для автоматизированного решения можно воспользоваться уже знакомыми нам по прошлым выпускам Excelling at Excel (Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР) функциями OFFSET (СМЕЩ) и MATCH (ПОИСКПОЗ).

Логика решения такова: элементы на листе Элементы и на листе Расчеты идут в одинаковом порядке, соответственно, элемент под номером n идет в след за элементом n - 1. Определив положение элемента n - 1 в исходном списке, легко получаем наименование элемента n.

В выпуске, посвященным циклам (Excelling at Excel вып.2: Циклы в Excel без VBA), мы решали эту задачи при помощи дополнительного столбца с порядковыми номерами элементов.

Сейчас мы обойдемся без вспомогательных столбцов.

Шаг 1. В первую строку листа с расчетами вставляем ссылку на ячейку с первым элементом из списка:

=Элементы!А2

Excel At Excel вып.4: Универсальные ссылки на список Microsoft Excel, Список, Длиннопост

Шаг 2. В ячейке, где должен быть второй элемент, пишем следующую формулу:

=OFFSET(Элементы!$A$1;MATCH(A1;Элементы!$A:$A;0);0)

Excel At Excel вып.4: Универсальные ссылки на список Microsoft Excel, Список, Длиннопост

Формула определяет позицию (функция MATCH) предыдущего элемента в списке (А1) на листе Элементы, а затем смещается (функция OFFSET) на соответствующее количество строк и, тем самым, получает наименование следующего элемента.

Шаг 3. Копируем строки сколько это необходимо.

Excel At Excel вып.4: Универсальные ссылки на список Microsoft Excel, Список, Длиннопост

ВАЖНО! Решение имеет несколько ограничений:

1. Название элементов в списке НЕ ПОВТОРЯЕТСЯ;

2. Количество строк между элементами на листе Расчеты ОДИНАКОВО.

Показать полностью 4

Кожа на мебели липнет после покраски

Ищу совета у пикабу. Решил освежить стулья из кожи. Выбрал для этого спрей-краску Saphir Tenax.

Кожа на мебели липнет после покраски Кожа, Покраска, Помощь, Липкость, Длиннопост

Обезжирил фейри поверхности и покрасил. Получилось, что часть стульев приекрасно высохла и краска легла отлично. На другой части краска легла плохо: либо липнет, либо можно пальцем соскаблить...

Купил еще краски и фирменного растворителя для снятия нелегшей краски

Кожа на мебели липнет после покраски Кожа, Покраска, Помощь, Липкость, Длиннопост

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

Показать полностью 2

Тур сайт Travelata: разбираемся с ценами

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


Есть такой сайт подбора туров - travelata.ru. Активно продвигает себя по интернету. По сути своей представляет собой агрегатор предложений туров от туроператоров. На главной странице сразу же заявляют, что продают туры, цитирую, "по ценам туроператоров или ниже с круглосуточной поддержкой до и после покупки".

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Прежде чем перейти к сути обмана, кратко расскажу принцип работы туротрасли. На случай, если кто не в курсе. Итак, есть туроператор. Он формирует пакетный тур: бронирует билеты, номер в гостинице, организует трансфер и т.д. Этот пакетный тур он продает 2мя путями: либо сам (через офисы продаж или сайт), либо через агентство. Собственно, турагентство это организация, которое продает туры туроператора за агентское вознаграждение. При чем по правилам тур рынка комиссия агентства уже включена в стоимость тура. То есть по идее и туроператор, и любое турагентство, у которого есть договор с этим туроператором, может продать Вам тур по одной и той же цене, установленной туроператором. Продав тур, агентство просто вычитает положенную ей комиссию от базовой стоимости тура, а остальное отправляет оператору. Таким образом, если агентство не намерено на Вас нажиться, то тур оно должно Вам отдать строго по той же цене, что и на сайте оператора, если не дешевле, пожертвовав ради Вас частью своей комиссии. Комиссии эти, к слову, сейчас редко выше 10% (ставка зависит от тура, оператора и объема продаж агентства), поэтому будьте благосклонны к агентствам, если они не дают Вам скидку больше 5%. Они отказались от половины своего заработка ради Вас!


Но вернемся к нашему кейсу. Итак, travelata не является туроператором, а является турагентством, а значит продает туры туроператоров за комиссию. Причем, если принять во внимание, что у агентств с высокими объемами продаж должна быть и комиссия побольше, то данный магазин туров должен иметь весьма неплохую комиссию. Соответственно, обоснованно счел не имеющим смысла им дополнительно накручивать стоимость туров для увеличения собственной маржи. Я ошибался. Смотрим...

Запросим любой отель. Я запросил случайно выбранный Sherwood Prize в Турции с 01.06.2020 на 7-14 ночей, 2 взрослых

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

ссылка запроса: https://travelata.ru/turkey/resorts/antalya/hotels/sherwood-...


Цена за 7 ночей без питания - 38 635 р. Тур предлагает Интурист.

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Проверим эту цену на проверенном настоящем агрегаторе туров (в том смысле, что он, действительно, качает данные с баз данных операторов) - sletat.ru.

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

ссыль: https://sletat.ru/search?callback=search&country=119&amp...


Видим тот же самый тур за 38 168 р. И тут вроде бы следовало завершить наше расследование, но особенность этого сайта, что цена с топливным сбором указывается в среднем столбце. И там мы видим 50 606 р. Поэтому для точности сравнения отсортируем по Интуристу и видим этот же тур за 39 025 р.

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Проверим на сайте туроператора.

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Действительно, на travelata дешевле. Даже дешевле, чем у туроператора. На этом следовало бы, наверное, похвалить travelata за честность и завершить расследование, но знание о таком инструменте, как товары-маркеры, нам это не позволяет. О чем речь? О том, что на некоторые массовые товары, цену которых покупатель держит в голове (например, пакет молока, яйца), магазин может держать на уровне или ниже своих конкурентов, а вот на остальные продукты этот магазин ставит цены выше, чем у конкурентов. Покупатель, посмотрев цены товаров-маркеров, решит, что данный магазин дешевле конкурента и будет в нем покупать, не подозревая, что покупает дороже.


Соответственно, проверим, не является ли самый дешевый тур в выдаче тем самым "товаром-маркером", на который специально сделана скидка. Для этого возьмем в выдаче travelata самые дешевые предложения других операторов.

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост
Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

БиблиоГлобус - 45 100 р. Тез Тур - 48 913 р. Туи - 48 696 р. (скидка 13%).

Смотрим на sletat.ru

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост
Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост
Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Соответственно, БиблиоГлобус - 45 100 против 45 556; Тез Тур - 48 913 против 49 345; Туи - 48 696 против 49 129.

Интересная вещь:

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Так 48 913 или 48 914?:)


Вернемся. Проверим цены у туроператоров.

Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост
Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост
Тур сайт Travelata: разбираемся с ценами Туризм, Турагентство, Travelataru, Длиннопост

Итак, travelata, действительно, дает цены ниже, чем у туроператоров. Уважение и похвала этой компании за честный подход!

Всем спасибо!


P.S. Если тема зайдет, могу запилить еще посты про туротрасль.

Показать полностью 15

Самое странное занятие на выходных... или распил молотков

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

Самое странное занятие на выходных... или распил молотков Выходные, Занятия, Странности

Догадаетесь зачем? Ответ в комментарии.

Показать полностью 1

Случай из собеседования

Случай из сегодняшнего собеседования (я - я, к - кандидатка):

К: Я прекрасно владею экселем.

Я: Да? Ок. Какая самая сложная или крутая функция, которую Вы использовали?

К: В смысле?

Я: Ну функции в экселе. Логические, текстовые, математические.

К: А, ну плюс, минус, умножить...

Занавес...

Домашняя колбаса из курицы

1. Отвариваем куриное мясо без кожи 2 часа

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

2. Вынимаем кости и делим на мелкие куски

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица
Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

3. Прокручиваем мясо в мясорубке с зубчиком чеснока и специями по вкусу

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица
Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

4. Добавляем пол свеклы и прокручиваем 4-5 раз до достижения равномерного цвета

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

5. В бульон из-под курицы добавляем желатин (30 г)

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

6. Смешиваем фарш с бульоном до однородной массы

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

7. Укладываем в формочки и на холод на сутки

Домашняя колбаса из курицы Домашняя колбаса, Длиннопост, Рецепт, Кулинария, Колбаса, Курица

Всем приятного аппетита!

Показать полностью 9

Excel At Excel вып.3: Собираем данные с разных листов 

Excel At Excel вып.3: Собираем данные с разных листов  Miscrosoft Excel, Microsoft Excel, ДВС, Одинаковые данные, Разные листы, Длиннопост

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Задача: сделать сводную таблицу с данными всех 22 таблиц

Одна из таблиц:

Excel At Excel вып.3: Собираем данные с разных листов  Miscrosoft Excel, Microsoft Excel, ДВС, Одинаковые данные, Разные листы, Длиннопост

Итак, решение. Есть три варианта решения данной задачи. Первый - использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй - посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант - это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.


Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие "проблемы":

1) как заставить общую таблицу "переключаться" с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?


Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 "Циклы в Excel без VBA". Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.

Excel At Excel вып.3: Собираем данные с разных листов  Miscrosoft Excel, Microsoft Excel, ДВС, Одинаковые данные, Разные листы, Длиннопост

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для "линковки" каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр - ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, "А1" в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:


INDIRECT("'"&A2&"'!B:B")


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


'Название листа'!A1


Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для "автоматизации" формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:


" ' " & A1 & " '!B:B "

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


ВАЖНО! Обратите внимание на кавычки и конкатенацию ("склеивание") при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.


Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.


COUNTA(INDIRECT("'"&A2&"'!B:B"))-1


Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один - количество обществ, второй - количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 "Циклы в Excel без VBA", пишем формулу первого цикла:


=IF(A2="";"";IF(COUNTIF($A$2:A2;A2)=OFFSET('Список обществ'!$E$2;A2-1;0);IF(A2+1>COUNTA('Список обществ'!A:A)-1;"";A2+1);A2))


Затем пишем формулу второго цикла:


IF(A2="";"";COUNTIF($A$1:A2;A2))


Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Excel At Excel вып.3: Собираем данные с разных листов  Miscrosoft Excel, Microsoft Excel, ДВС, Одинаковые данные, Разные листы, Длиннопост

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).


=IF(A2="";"";INDIRECT(OFFSET('Список обществ'!$A$2;A2-1;0)&"!"&"E"&B2+1))


OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 - номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.

Вот, что получается:

Показать полностью 3
Отличная работа, все прочитано!