Дошли руки сделать видео с возможностями геолокации в Excel.

На видео пример размещения рекламных конструкций для вымышленного рекламного агентства.

Кто сталкивался с проблемой невозможности применения автофильтра для полей сводной таблицы? (по умолчанию фильтр работает только для первого столбца, а на все остальные столбцы фильтр не включается).

Выход есть!

1. Перед началом сводной таблицы вставьте один свободный столбец.
2. Далее выделите заголовки столбцов сводной таблицы и одну пустую ячейку в свободном столбце, который мы вставили.
3. Нажмите Ctrl+Shift+L
4. Наслаждайтесь.

Фильтр работает, как в обычной таблице, включая фильтрацию по цвету ячейки и фильтрацию по числовым фильтрам.

 Друзья, как часто из даты в Excel в формате 17.10.2015 вам бывает необходимо получить отдельно день недели, месяц или год этой даты?

Я видел множество людей, которые как только не изощрялись, чтобы найти составные части даты.

Составные части дат могут применяться по разному: от ВПР до сводных таблиц.

Для выделения составных частей из определенной даты есть прекрасная функция ТЕКСТ.

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

Предположим, что в ячейке A1 есть значение даты - 17.10.2015.

Формула =ТЕКСТ(A1;"значение из таблицы ниже")

возвращает всевозможные составные части даты и их комбинации.

К примеру, формула =ТЕКСТ(A1;"ММММ") возвращает "октябрь", а формула =ТЕКСТ(A1;"ММММ, ГГГГ") возвращает "октябрь, 2015".

P.S. значения из таблицы чувствительны к регистру.

Надеюсь этот простой инструмент работы с датами упростит вашу повседневную работу в Excel.

Источник таблицы - официальный сайт microsoft по поддержке продуктов Office - support.office.com

Как часто вы используете функцию ВПР?

Я уверен, что большинство из вас использует ее ежедневно и знают, что представляет из себя эта функция.

А как часто вы делаете рейтинги? Студентов, сотрудников, магазинов, да чего угодно?

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

Напомню, что ВПР имеет четыре аргумента:

1. Искомое значение - здесь все понятно - мы указываем значение или ячейку, которую хотим найти в таблице;
2. Таблица - здесь тоже все понятно - мы указываем таблицу, где мы будем искать "Искомое значение";
3. Номер столбца - значение или ячейка со значением номера столбца в "Таблице", значение которой нам нужно получить;
4. Интервальный просмотр - вот тут-то, господа, и возникают непонятки.

Мой друг, который когда-то учил меня этой функции, когда я спросил его что ставить 1(ИСТИНА) или 0 (ЛОЖЬ), ответил мне: "Ставь ноль и не парься. Все ставят ноль, и ты поставь, и все заработает".

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

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

Но сколько вы всего упускаете, не используя единицу?

Интервальный просмотр чаще всего в бизнесе необходим для рейтингов, диапазонов цен, сегментов возрастов и т.д.

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

Я объясню интервальный просмотр на примере рейтинга студентов, которые написали контрольную работу:

Итак есть 10 студентов, которые написали контрольную работу по 100-бальной шкале.

1. 100
2. 90
3. 80
4. 70
5. 60
6. 50
7. 40
8. 30
9. 20
10. 10

Также у нас есть градация по буквенной оценке полученного балла:

0-50 - E
51-70 - D
71-80 - C
81-90 - B
91-100 - A

Теперь оцените студента буквенной оценкой. Легко не правда ли? А если записей будет не 10 а 200 тысяч? Вот тут-то и поможет ВПР с единицей)).

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

0 - E
51 - D
71 - C
81- B
91- A

А теперь используем ВПР: 

1. Искомое значение - это ячейка, в которой записаны 100 балов первого студента;
2. Таблица - наша измененная таблица градации (не забудьте закрепить границы);
3. Номер столбца - в данном случае - 2;
4. Интервальный просмотр - и здесь смело можем ставить единицу.

Два щелчка мышью...и 200 000 строк нам не страшны.

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

Попробуйте повторить для закрепления.

Формула помогает проранжировать цены на товар, сравнить участников соревнования. А я же использую формулу для автообновляемых рейтингов, конечно в связке с другими формулами (ДВССЫЛ, ИНДЕКС, ПОИСКПОЗ и др), но об этом позже.

Как часто вы видите на листе Excel зеленые треугольники в ячейках?

Что с ними делать?

Есть два основных случая неожиданного появления этих треугольников:

1. К примеру, 10 предыдущих ячеек используют для расчета своих значений одну формулу, а 11-ая ячейка вычислена по другой формуле. Такую формулу выбивающуюся из общего порядка Excel подсвечивает зеленым треугольником. Но сегодня поговорим о другом случае.

2. Случай, по которому сегодня я дам решение выделяет ячейку зеленым треугольником из-за ошибки "Число сохранено как текст". Эта ошибка зачастую появляется при экспорте данных из других источников (SQL-таблицы или данные из Интернета). Такие ячейки Excel воспринимает не как цифры образующее число, а как последовательность символов, которые образуют текст. Соответственно математические операции с такими ячейками невозможны.

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

Не исправляйте эту ошибку подобным способом.

В случае, если диапазон таких ячеек огромен (300000 строк и более) Excel зависнет серьезно и надолго.

Эффективный способ решения проблемы следующий:

1. Любую одну пустую ячейку на листе заполняете цифрой 1;
2. Копируете эту ячейку удобным для вас способом (я предпочитаю Ctrl+C);
3. Выделяете диапазон ячеек с зелеными треугольникам, который вы хотите преобразовать в числа;
4. Запускаете функцию специальной вставки удобным для вас способом (я предпочитаю Alt+Ctrl+V);
5. В меню специальной вставки, в разделе "Операции" выбираете "Умножить" --> нажимаете ОК.

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

Суть решения заключается в том что математическая операция умножения на единицу возвращает то же самое число, которое находится в ячейке, параллельно, сохраняя значение ячейки в число.