Ответы 20/10/17 (Excel_Everyday)
tDotsВопрос 1
В Power Point можно вставить таблицу или диаграмму из Excel как связанный объект. Тогда при обновлении данных в Excel автоматически будет меняться и таблица/диаграмма в PP. Для этого нужно при вставке в PP нажать Ctrl+Alt+V (специальная вставка) и выбрать пункт "Связать".
Вопрос 2
Видимо так происходит потому, что дата во внешнем источнике уже разбита на эти три столбца. Есть несколько вариантов. Либо настраивать импорт из источника с помощью мастера импорта данных Excel (пример работы импорта в этих уроках: раз, два, три) и там задавать разделители так, чтобы дата не билась на столбцы; либо вставлять в три столбца, как Вы делаете это сейчас, а потом собирать формулой в одну дату в четвертом столбце. Пример такой формулы - тут.
Вопрос 3
Подписи на Оси X можно поставить любые. Для этого нужно активировать диаграмму, найти на вкладке "Конструктор" кнопку "Выбрать данные", там найти "Подписи горизонтальной оси", нажать "Изменить" и выбрать нужный диапазон (в котором содержатся те подписи, что Вам нужны на графике). Если же нужно, чтобы на графике были значения для точек 1,2,3,4,5, а подписи видны только для 1,2,5 - то нужно в исходных данных для подписей 3,4 применить числовой формат с кодом ";;;". Тогда на Оси Х эти подписи будут скрыты, хотя точки данных для них на графике останутся.
Вопрос 4
Вопрос, конечно, лаконичный. Попробуем угадать, что имелось в виду. Связать таблицы можно, например, по ключевому полю с помощью функции "Отношения" на вкладке "Данные". Урок - здесь и вот здесь.
Вопрос 5
Для этого необходимо, чтобы ваши данные с 1 января по 30 сентября и с 1 октября по 31 декабря были представлены разными рядами данных. Нужно, чтобы они располагались в разных столбцах таблицы при построении. Тогда на одной временной оси будет 2 графика, каждому можно задать свой цвет и для каждого построить линию тренда. Единственный момент - линия тренда будет для каждого графика распространяться на всю длину временной шкалы. Пример таблицы и графика - ниже.
Вопрос 6
В зависимости от того, что именно нужно получить на выходе, можно использовать разные инструменты.
Собирать данные из трех разных листов и подкладывать друг под друга можно, например, макросом. Для удобства преобразуйте ваши данные на листах в "умные таблицы" (Ctrl+T), создайте итоговый лист, в первой строке сделайте шапку, как в исходных таблицах и запускайте такой макрос, когда нужно собрать данные:
Sub СборЛистов()
Range(Cells(2, 1), Cells(Rows.Count, 1000)).ClearContents
Range("Таблица1").Copy
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial Paste:=xlPasteValues
Range("Таблица2").Copy
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial Paste:=xlPasteValues
Range("Таблица3").Copy
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial Paste:=xlPasteValues
End Sub
Макрос запускайте на листе, где собираете данные. "Таблица1" - это имена Ваших "умных таблиц". Для удобства можете на сводном листе сделать кнопку и повесить на нее макрос, как показано тут.
Если Вам нужен не просто копипаст, то, возможно, Вам подойдет инструмент "Консолидация" на вкладке "Данные". Мы обязательно сделаем про него отдельный урок, или даже большую статью.
Ну и если у Вас версия Excel 2010, 2013 или, еще лучше, 2016 - можете посмотреть в сторону надстройки Power Query.
Это к вопросу "чем" лучше воспользоваться. Если будут вопросы "как" этим пользоваться - пишите.
Вопрос 7
Нам и самим хотелось бы решить эту проблему, но, к сожалению, Срезы для "Умных таблиц" появились только в версии Excel 2013 и в более ранних версиях недоступны (и подключить их никак не получится). В Excel 2010 срезы можно использовать только в сводных таблицах.
Самое лучшее решение в данной ситуации - использовать везде самую свежую версию офиса. Мы, кстати, иногда их разыгрываем на нашем канале.
Вопрос 8
Можно воспользоваться формулой массива (вводить через Ctrl+Shift+Enter). В умную таблицу "Отчет" в каждый столбец вставляем формулу (для каждого столбца отдельно, она автоматом протянется на все строки):
=ЕСЛИОШИБКА(ИНДЕКС(База[Столбец1];НАИМЕНЬШИЙ(ЕСЛИ(База[Критерий]=1;СТРОКА(База[Критерий])-1);СТРОКА([@Отчет1])-1));"")
где:
База[Столбец1] - столбец из базы, который нужно копировать в текущий столбец отчета;
База[Критерий] - ваш столбец с критериями (0,1,2,3);
[@Отчет1] - ссылка на любой столбец таблицы "Отчет";
"-1" - вычитание, чтобы получить нужный номер строки. Если Ваши таблицы начинаются с первой строки листа, то вычитаете 1, если с третьей - то 3 и т.д.
После ввода формулы в таблице отчет всегда будут находиться только те значения, которые в Базе имеют "1" в поле критерия. Остальные строки будут пустыми.
Вопрос 9
Для начала нужно проставить порядковые номера покупок для каждого клиента. Для этого сортируете свои 2 колонки сначала по клиенту, а потом по дате по возрастанию (от старых к новым). Настраиваемой сортировке учили здесь.
После сортировки в соседней колонке пишете формулу:
=ЕСЛИ(A2=A1;C1+1;1), где А - столбец с клиентом, а С - столбец с формулой, А1:С1 - шапка.
Вы получите порядковые номера покупок для клиентов. Дальше по этим 3 колонкам строите сводную таблицу. В столбцы переносите колонку С, в строки - колонку А, а в значения - колонку В (и ставите формат даты). Получите на выходе примерно следующее:
Вопрос 10
Ответили вот в этом уроке.
Вопрос 11
Достаточно выбрать проблемные ячейки и включить настройку "Перенести текст"
Правда, придется выравнять высоту строк после этого, так как она автоматически увеличится, чтобы было видно все содержимое ячейки.
Другой вариант - заполнить чем-то соседнюю ячейку справа. Тогда текст также не будет выходить за границы.
Вопрос 12
Можно, например, воспользоваться классической ВПР. Сравнение двух списков как раз показывали в этом уроке
Вопрос 13
Мы уже давно перешли на видео в своих уроках, а до этого на все гифки давали ссылки для просмотра онлайн. Что касается Вашей проблемы, то чем Вы открываете гифки после скачивания? Стандартный просмотрщик изображений в Win7 не умеет отображать Gif. Попробуйте в браузере открывать, или использовать другое ПО (например, Fast Stone Image Viewer).
Вопрос 14
Вам поможет функция СУММЕСЛИМН. Про нее был урок тут.
Конкретно в вашем случае для Товара1 она будет выглядеть так:
=СУММЕСЛИМН(Sheet1!$C$2:$C$9;Sheet1!$A$2:$A$9;Sheet2!$A$1;Sheet1!$B$2:$B$9;Sheet2!A3), где:
Sheet1!$C$2:$C$9 - диапазон Товара1 (объем заказа), для каждого товара придется указать свой диапазон;
Sheet1!$A$2:$A$9 - диапазон дат;
Sheet2!$A$1 - ячейка, куда вводите дату;
Sheet1!$B$2:$B$9 - диапазон контрагента;
Sheet2!A3 - ячейка с именем/названием контрагента.
Копируете формулу во все нужные ячейки, обязательно меняя диапазон с товарами и ячейку с контрагентом. При изменении ячейки с датой для указанного в формуле контрагента будет считаться объем указанного в формуле товара.
Вопрос 15
Можно воспользоваться функцией СУММЕСЛИ (урок - здесь), а в качестве критерия использовать номер недели (как его добавить - смотрите здесь). Нужно будет добавить колонку с номером в прошлогодние данные и в итоговую таблицу, в которой в строк соответствующей недели формула СУММЕСЛИ будет подводить итог для этой недели за прошлый год.
Вопрос 16
Да, конечно. Все возможности условного форматирования раскрываются, когда начинаете использовать формулы, а не только набор стандартных инструментов. Выделяете столбец, который необходимо раскрашивать. Выбираете "Условное форматирование" - "Создать правило" - "Использовать формулу для определения форматируемых ячеек".
В открывшемся окне вводите формулу для срабатывания правила. Сначала вводите равно (как и в обычной формуле), а потом условие. И вот в условии указываете ячейки столбца с данными (например, на рисунке создано правило, по которому в выделенном столбце будут окрашены все ячейки, в которых значение в той же строке в столбце С равно 10).
После ввода формулы жмете "Формат..." и выбираете, как именно хотите раскрашивать ячейки.
Главное - не запутаться с закреплением ячеек. Например, ссылка на строку в большинстве случаев должна быть не закреплена, а вот столбец обычно можно закрепить (знак $ перед буквой столбца). Примеров формул в условном форматировании у нас много: тут, тут, тут или ищите тег #Условное_форматирование.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: