Возможности геолокации в Excel
Дошли руки сделать видео с возможностями геолокации в 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. В меню специальной вставки, в разделе "Операции" выбираете "Умножить" --> нажимаете ОК.
Вуаля, и за пару секунд диапазон превращается в настоящие числа, с которыми можно делать любые математические операции.
Суть решения заключается в том что математическая операция умножения на единицу возвращает то же самое число, которое находится в ячейке, параллельно, сохраняя значение ячейки в число.
Популярное
- Что такое MAT (Moving Annual Total)?
- "Зеленые треугольники" или "Число сохранено как текст"
- Функция =ВПР с единицей
- Функция =ТЕКСТ - Форматы даты
- Возможности геолокации в Excel
- Постулат №1: Сохраняйся перед и после каждого серьезного изменения
- Фильтрация данных сводной таблицы
- Постулаты Excel
- Парсер для stat.gov.kz - реестр юридических лиц
- Функция =ТРАНСП
Последнее
- Парсер для stat.gov.kz - реестр юридических лиц
- Что такое MAT (Moving Annual Total)?
- Постулат №1: Сохраняйся перед и после каждого серьезного изменения
- Постулаты Excel
- Функция =ТРАНСП
- Возможности геолокации в Excel
- Фильтрация данных сводной таблицы
- Функция =ТЕКСТ - Форматы даты
- Функция =ВПР с единицей
- "Зеленые треугольники" или "Число сохранено как текст"