ArtemTabolin

ArtemTabolin

пикабушник
пол: мужской
поставил 7871 плюс и 1 минус
проголосовал за 0 редактирований
1995 рейтинг 574 подписчика 118 комментариев 6 постов 5 в "горячем"
21

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

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

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

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

К: В смысле?

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

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

Занавес...

426

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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


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

Excel At Excel вып.3: Собираем данные с разных листов  Miscrosoft Excel, 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, Excel, Двссыл, Одинаковые данные, Разные листы, Длиннопост

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


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


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

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

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

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

Байки из Excelя: "Он поздоровался со мной"

По просьбе пикабушников разбавляю посты про "фишки" в эксель байкой. Опять же, связанной с экселем.

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

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

Так вот, разослал я им в первый раз этот файл. Прибегает один парень: "Это огонь! Ты сам сделал?"

Я говорю: "Сам!", и переполняюсь от гордости

Парень: "Ого! Я просто обалдел, когда..."

Фишек там классных было сделано много и вот я предвкушаю, что он скажет что-нибудь типа "сам отправил данные", а тут вдруг:

"...он поздоровался со мной"...

Из всего что было сделано в файле юзера впечатлило больше всего, что при входе в файл он выводил окошко "Здравствуйте, Иван Иванов!":)))

313

Excelling at Excel вып.2: Циклы в Excel без VBA

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Немного теории. Циклом называется конструкция, которая некоторое (определяемое) количество раз выполняет заданные действия. Например, Вам нужно перебрать некий массив данных и выделить в нем пустые поля. В программировании это реализуется при помощи циклов. В VBA наиболее частым вариантом является конструкция For i = 0 to n … Next i.

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

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Также отдельно имелись сметы по каждому такому проекту с детализацией статей затрат и с указанием исполнителя по каждой из статей с указанием доли участия. По каждой из статей могло быть до 4 исполнителей.

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Задача: свести это в одну таблицу для последующей обработки через ту же сводную таблицу. То есть требовалось получить вот такое представление:

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

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

В нашем примере получалось три цикла (в порядке от младшего к старшему): тип исполнителя (цикл 1), статья затрат (цикл 2), проект (цикл 3). Алгоритм выглядит примерно так:

Цикл 3 (проект)

Цикл 2 (статья)

Цикл 1 (тип исполнителя)

Конец цикла 1

Конец цикла 2

Конец цикла 3

Так бы примерно выглядела бы и структура кода VBA для реализации этих трех циклов, но в самом Excel так сделать нельзя. Что же делать?

Давайте еще раз обратимся к сути цикла: это повторение какого либо действия определенное количество раз. Теперь рассмотрим это на примере одного цикла – цикла 1 (тип исполнителя).

Допустим, у нас 4 возможных типа исполнителя. Они у нас на отдельном листе «Тип исполнителя». Соответственно, нам надо перебрать все эти четыре значения по одному. Как? Во-первых, мы должны определить, что их именно 4. Для этого воспользуемся функцией COUNTA (СЧЁТА).

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

ВАЖНО! Не забудем вычесть заголовок.

Во-вторых, нам надо оформить перебор значений от 1 до 4. Вернее, до значения полученного из COUNTA (СЧЁТА). Это именно столько «шагов» должен сделать наш цикл.

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

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Получаем бесконечное повторение от 1 до 4. Теперь нам остается получить значение на каждому «шагу» цикла. Это можно сделать при помощи функции OFFSET (СМЕЩ), в которой значения столбца А мы будем использовать в качестве второго параметра (смещение по строкам).

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Теперь добавим второй цикл – цикл 2 (статья). Подход такой же за исключением одного «НО»: переключать значение мы будем не сразу после предыдущего как в цикле 1, а по достижении максимального значения в цикле 1 (тип исполнителя). Для этого нам нужно формула, описывающая такую логику:


«Если значение типа исполнителя равно количество типов, то

если предыдущее значение статьи равно количеству статей, то 1,

если не равно, то предыдущее значение + 1,

если не равно, то предыдущее значение».


Вот так это выглядит в экселе:

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Цикл 3 (проект) оформляется схожим образом с циклом 2 (статья). Но «триггером» для переключения на новое значение будет уже два условия одновременно: максимальное значение количества статей и максимальное количество типов исполнителей. В формуле выполнение этих двух условий мы оформим через функцию AND (И) равную TRUE (ИСТИНА).

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Осталось только добавить формулы СМЕЩ в ячейки с данными.

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Необходимо не забыть «остановить» цикл. В противном случае вы получите то, что ниже:

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

Чтобы этого избежать в формуле в столбце А мы специально вставили в одном из возможных исходов значение «» (пусто), чтобы этим самым «остановить» бесконечный цикл. Теперь при протягивании формулы будут выводиться пустые ячейки. В формул остальных ячеек (в т.ч. со СМЕЩ) следует добавить:

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост

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

Excelling at Excel вып.2: Циклы в Excel без VBA Excel, Excelling at Excel, Цикл, Без макросов, Длиннопост
Показать полностью 11
403

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Многие из вас встречались с этой полезной функцией MS Excel ВПР (VLOOKUP). Это, безусловно, очень полезный инструмент агрегации и трансформации данных. Но, к сожалению, данная функция имеет ряд ограничений. Ниже мы рассмотрим несколько трюков, которые позволят нам преодолеть эти ограничения.


Первое ограничение функции ВПР - это обязательный параметр "номер_столбца" ("col_index_num").

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Он определяет какие данные функция ВПР вернет в результате. Использование в качестве номера столбца в функции числа может негативно сказаться на удобстве последующего использования и изменения формул и расчетов. Например, добавление одной колонки в искомом массиве "сломает" формулу, т.к. параметр "номер_столбца" в ней остается неизменным.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост
Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Как видно из примера выше, вместо данных из столбца Data функция вернула данные из столбца Class, т.к. он теперь стал вторым относительно столбца Name.


Также нужно иметь в виду, что если через ВПР агрегируется несколько элементов данных, то в случае внесения корректировок в массив данных потребуется вносить ручные правки во все параметры "номер_столбца".


Чтобы избежать этих трудозатратных манипуляций, можно использовать функцию ПОИСКПОЗ (MATCH). Она позволяет определить позицию искомого поля в массиве поиска.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

где бы оно не находилось...

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Дополнительным бонусом от использования функции ПОИСКПОЗ в данном случае будет еще и легко изменяемое искомое поле данных. Нужно просто поменять первый параметр "искомое_значение" ("lookup_value") функции ПОИСКПОЗ (в приведенном примере это ячейка В1).

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Второе ограничение функции ВПР  - обязательное требование о расположении в массиве поиска столбца с искомыми значениями строго слева от столбцов с данными. Если столбец с искомыми значениями (столбец Name в примере) не первый слева направо, то функция ВПР не вернет никаких данных из столбцов с данными (столбцы Class Data в примере).

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Чтобы обойти это ограничение, надо воспользоваться функцией СМЕЩ (OFFSET) вместо функции ВПР. Данная функция состоит из трех обязательных параметров: ссылка (reference), смещение по строкам (rows), смещение по столбцам (cols). СМЕЩ возвращает значение ячейки, расположенной в X строках (смещение по строкам) и в Y столбцах (смещение по столбцам) от указанной ячейки (ссылка). Но чтобы получить максимальный эффект от этой функции, ее следует использовать вместе с двумя функциями ПОИСКПОЗ вместо параметров смещение по строкам и смещение по столбцам.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

ВАЖНО! Пожалуйста, не забывайте, что функция ПОИСКПОЗ возвращает порядковый номер ячейки с искомым значением в массиве, когда как в функции СМЕЩ смещение по строкам и смещение по столбцам - это количество шагов от начальной ячейки. Т.е. необходимо в обязательном порядке вычесть 1 из полученных функциями ПОИСКПОЗ результатов.

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

Итак, получаем формулу следующего вида:

СМЕЩ(начальная ячейка; ПОИСКПОЗ(искомое значение; массив поиска; тип сопоставления) - 1; ПОИСКПОЗ(искомое значение; массив поиска; тип сопоставления) - 1)

В результате мы имеем довольно-таки гибкое решение, не зависящее от позиции столбцов в массиве данных. Также имея это решение, мы можем легко переключаться с одного столбца данных на другой.


Для того, чтобы сделать эту формулу супергибкой и чтобы легко менять не только данные с результатами (Class и Data в примере), но и искомые данные (Name в примере), нужно поместить внутрь первой функции ПОИСКПОЗ (вместо искомое значение) еще одну функцию СМЕЩ с функцией ПОИСКПОЗ внутри. Такая формула будет самостоятельно определять столбец с искомыми значениями на основании значения ячейки А1. Таким образом, модифицированная формула будет иметь следующий вид:

СМЕЩ(начальная ячейка; ПОИСКПОЗ(искомое значение; СМЕЩ(начальная ячейка; 0, ПОИСКПОЗ(искомое значение; массив поиска; тип сопоставления) - 1); массив поиска; тип сопоставления) - 1; ПОИСКПОЗ(искомое значение, массив поиска, тип сопоставления) - 1)

Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост
Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР Excel, Функция, Впр, Поискпоз, Смещ, Excelling at Excel, Длиннопост

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

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

Готовы принять вызов и засветиться в рекламе? Тогда поехали!

Готовы принять вызов и засветиться в рекламе? Тогда поехали!

Признайтесь, вы хоть раз, но заходили на Авито. Возможно, продавали старые книги, детские вещи или старинные, но совсем ненужные вам вазы или статуэтки. Когда звезды сходятся, покупка или продажа выходит крайне удачной. Как у наших героев.


1. @MorGott

Почти открыл свой магазин на Авито из детских вещей, из которых вырос его ребенок.


2. @Little.Bit

Привел с Авито третьего в их с женой уютное семейное гнездышко, и теперь они счастливы вместе.


3. @MadTillDead

Собралась с силами и продала на Авито все, что напоминало ей о бывшем.


4. @Real20071

Его жена доказала, что в декрете тоже есть заработок. Причем на любимом деле и Авито.


Своим удачным опытом они поделились в коротких роликах. Теперь ваша очередь!

Снимите видео об успешном опыте продажи, покупки или обмена на Авито, отправьте его нам и получите шанс показать свой ролик всей стране. Представьте, вы можете попасть в рекламу Авито! А еще выиграть один из пяти смартфонов Honor 20 PRO или квадрокоптер. Ну что, готовы принять вызов? Смотрите правила, подробности и ролики для вдохновения тут.

Отличная работа, все прочитано!