Рубрики
Вопрос-ответ

Основы сводных таблиц

Вопрос от Михаила пришел из нашей группы ВКонтакте.

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

они должны сохраняться либо другими словами по нажатию кнопки заполняется новая строка 2ой таблицы из ИТОГО первой таблицы.

Причем нужно избежать дублирования одного и того же дня несколько раз. 1 строка = 1 день.

Михаил, спасибо за Ваш вопрос.

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

Один лист — одна таблица. Если вы хотите эффективно, без лишних усилий работать в Microsoft Excel, возьмите себе на заметку это правило — оно очень упрощает дальнейшую работу. Применительно к Вашему вопрос, я предлагаю вынести вторую таблицу на отдельный лист.

В 99% случаев макросы — это зло! Задание звучит так, будто, вы хотите использовать функциональность макросов. Это возможно. Это просто. Но здесь не нужно. Если есть встроенная функциональность — пользуйтесь ей. Я предлагаю воспользоваться вставкой сводной таблицы.

РЕШЕНИЕ:

Пример таблицы Вы не скинули, поэтому я придумал свою (на ее месте может быть любая). Предположим, что в таблице с входными данными есть несколько колонок, а именно: дата, наименование, количество, цена и расчетная стоимость. В конце таблицы — итого. Для таблицы со входными данными я предпочел бы избежать итого, но если Вы настаиваете, мы оставим эту строку.

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

Выделите колонки с данными (именно колонки, а не диапазон, чтобы не менять его каждый раз, как вы внесли несколько строк). На ленте Insert выберите Pivot table в блоке Tables.

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

Наша сводная таблица начинает вырисовываться, но Excel вместо суммы, вставил количество ячеек. Поэтому справа нажимаем на перенесенную стоимость, в появившемся меню нажимаем Value field settings… и выбираем сумму. Здесь же можно изменить формат числа, для этого нажмите на кнопку Number Format.

Сводная таблица почти готова. Если присмотреться к результату, то мы видим две ненужных строки: Итого и (blank). Уберем их. Для этого нажмем на значок выпадающего меню на Row Labels и снимем галочку с полей (blank) и Итого. Ok.

Сводная таблица готова. Когда вы добавите данные за следующий день, вам надо будет обновить ее. Для этого нажмите на нее правой кнопкой и выберите Refresh.

Готовый пример Вы можете посмотреть здесь.

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

Рубрики
Вопрос-ответ

Перенос по словам и текстовые поля

Вопрос от Романа пришел по электронной почте.

Подскажите пожалуйста, как мне сделать чтобы строка с текстом на уходила в право, а располагалась под синей строкой?

Спасибо большое за вопрос. Я могу предложить вам два варианта, как решить эту проблему. Один — быстрый и простой, второй — чуть подольше, но зато потом удобнее работать с документом.

Вариант первый — объединение и перенос по словам

Выделите ячейки A33:D33 и нажмите объединение ячеек (я дальше буду приводить этот пример на своей таблице с вашего позволения). Для этого на ленте Home в блоке Alignment выберите Merge Accross (для этого надо нажать на треугольник выпадающего списка, в котором написано Merge and Center).

Выделенные ячейки объединятся. Теперь не снимая выделения включите режим переноса по словам. Это кнопка Wrap Text прямо над выпадающим списком объединения ячеек.

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

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

Вариант второй. Текстовые блоки.

Под вашей таблицей вставьте текстовый блок (для этого на ленте Insert в блоке Text выберите Text Box. Чтобы он был «привязан» к краям ячейки, при определении его размеров удерживайте клавишу Alt на клавиатуре. После этого вставьте в это поле текст, который вам нужен.

Проблема в том, что у этого текстового блока печатается рамка, что обычно бывает не нужно. Чтобы этого избежать, выделите текстовый блок (при этом курсор внутри видно не будет) — для этого кликните мышью на его рамку, и вызовите его свойства (Ctrl + 1 или кликнуть правой кнопкой и выбрать свойства).

В появившемся окне выберите блок Line color слева и выберите вариант No line. После этого нажимайте на кнопку Close. Все, блок с комментарием готов.

Читайте также как оптимально распечатать документ, как распечатать документ с комментариями, а также как сделать профессионально выглядящий отчет.

Рубрики
Вопрос-ответ

Как распечатать документ Excel с комментариями

Этот вопрос пришел с нашей странички ВКонтакте.

Приветствую, уважаемые участники:)Подскажите,пожалуйста,как распечатать таблицу вместе с комментариями.

В Microsoft Excel есть встроенная стандартная функциональность позволяющая печатать документы одним из способов:

(1) так же, как они отражаются на экране, и
(2) внизу страницы в виде списка комментариев с указанием на ячейки, к которым эти комментарии написаны.

Для того чтобы напечатать документ с комментариями на ленте Page Layout в блоке Page setup нажать на уголок. В появившемся окне на листе Sheet в выпадающем списке Comments выбрать At end of sheet или As displayed on sheet — как надо.

Важное замечание: если комментарий отражается на экране только при наведении на него курсора, то на бумаге будет распечатан только уголок. Чтобы распечатать комментарий, необходимо нажать на ячейку с комментарием правой кнопкой и выбрать Show comment (Показать комментарий).

Читайте также как оптимально распечатать документ, как сделать перенос по словам и для чего нужны текстовые поля, а также как сделать профессионально выглядящий отчет.

Рубрики
Вопрос-ответ

Как оптимально распечатать документ

Вопрос пришел в группе «Microsoft Excel — это просто!» ВКонтакте.

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

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

Перед тем как печатать документ, есть несколько вещей, на которые стоит обратить внимание.

(1) Сложно переоценить пользу такого инструмента, как Предварительный просмотр, который покажет, как именно будет выглядеть документ на бумаге. Единственное, почему документ на бумаге может выглядеть иначе — это настройки принтера.

(2) В версиях Microsoft Office Excel, начиная с 2007 есть функция «Разметка страницы». Чтобы его включить нажмите на ленте на вкладку View, дальше на кнопку Page layout в блоке Workbook views. Эта функция также позволяет просмотреть документ в том виде, как он будет печататься. Разница в том, что в этом режиме документ можно обрабатывать, заранее видя, как документ будет выглядеть на бумаге. Как показывает практика, иногда распечатка может немного отличаться от того, как выглядит документ на экране (особенно, если в нем есть текстовые поля).

(3) При предварительном просмотре документа много пустых страниц — попробуйте установить области печати. Для этого выделите область, которая должна быть распечатана и выберите на вкладке Page layout в блоке Page setup на кнопку Print area => Set print area.

(4) Документ не помещается на страницу? Есть несколько вариантов, что можно с этим сделать: уменьшить ширину колонок или уменьшить масштаб документа. Для того чтобы уменьшить масштаб, на вкладке Page layout выберите ширину и высоту документа в страницах. Документ автоматически станет меньше настолько, чтобы поместиться на это количество страниц. Вы также можете установить только максимальную ширину или только максимальную высоту документа.

Читайте также о том, как распечатать документ с комментариями, как сделать перенос по словам и для чего нужны текстовые поля, а также как сделать профессионально выглядящий отчет.

Рубрики
Вопрос-ответ

Вычислить значения функций при условии

Помогите вычислить значения выражений a и b, используя логические функции

вот уравнения ( http://xmages.net/storage/10/1/0/4/a/upload/bfa479fc.png )

заранее благодарю!

Вместо ответа просто приложу файл. На мой взгляд, ответ исчерпывающий.

Другие вопросы вы можете задать в комментариях или по адресу edward@youcanexcel.ru.

Рубрики
Вопрос-ответ

Материалы, купленные в прошлом году по проектам

Сегодня я получил свое первое письмо с вопросом. С удовольствием на него отвечу.

Уважаемый Эдуард,

Подскажите, пожалуйста, встречались ли в Вашей практике аналогичные ситуации:

Задача:

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

Допусти мы знаем, что на конец 2010 года осталось следующее кол-во материалов, из числа закупленных в 2010 году.

Таблица 1

МатериалКол-воСредневзвешенная цена
100110010
100220015
1003150100

Списание материалов в производство в 2011 году представлено в таблице 2.

Примечание:

1) Важно сохранить привязку к проекту, т.е. подбить total не пойдет

2) Материалов – сотни тысяч, линеек — соответственно тоже. Формула должна быть надежной, так как вручную это пересчитать невозможно.

Таблица 2

В примере выше (Таблица 2) цифры в столбе «Из них закуплено в 2010 году» подставлены вручную. Надо придумать формулу, чтобы при каждом списании материалов, кол-во списываемых материалов сравнивалось с имеющимся количеством на начало года (таблица 1) и учитывало предыдущие списания в текущем году.

Заранее благодарю за помощь.

С уважением, Евгения

Евгения,

Я бы реализовал это следующим образом.

(1) Подтянуть количество материалов по состоянию на конец прошлого года (лучше это сделать с проверкой на ошибку, например, с помощью формулы IFERROR). Формула VLOOKUP не будет работать, так как номера материалов представлены в разных форматах. Подробнее о формуле VLOOKUP читайте в этой статье.

(2) Предварительно отсортировав документы по дате (или по номеру документа, если бы он был), рассчитать количество данного материала, потраченного с начала года с помощью формулы SUMIFS. Подробнее о формуле SUMIFS читайте в этой статье.

(3) Рассчитать количество материалов, оставшихся из закупленных в прошлом году.

(4) Рассчитать количество материалов потраченных в этой линейке из оставшихся от закупленных в прошлом году (из третьего пункта).

Расчет смотрите в приложенном файле.

Если будут еще вопросы, пожалуйста, пишите письмо на адрес edward@youcanexcel.ru или оставляйте комментарий внизу страницы.

Рубрики
Ноу-хау

Как быстро удалить тысячи строк из громадной таблицы по фильтру

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

Файл с исходной таблицей вы можете скачать отсюда, чтобы попробовать сделать тоже самое или провести свой эксперимент.

В качестве эксперимента, статья написана в виде нескольких скриншотов. Надеюсь, вам понравилось. Присылайте обратную связь на электронную почту edward@youcanexcel.ru

Рубрики
Ноу-хау

Продвинутый Excel: Почему я перестал пользоваться функцией VLOOKUP (ВПР)

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

  1. Это просто неудобно. VLOOKUP (ВПР), наверное, единственная формула, которая ссылается не на конкретную ячейку, в которой заинтересован пользователь, а на массив и выбирает нужную колонку с помощью номера столбца, который передается как отдельный параметр.
  2. Столбец, в котором мы ищем должен быть левее, чем столбец с искомым значением
  3. Копирование формулы в соседний столбец не меняет столбец, по которому происходит поиск. Приходится делать одну из двух вещей: либо менять столбец вручную, либо встраивать в формулу проверку на столбец или ссылку на номер этого столбца.
  4. Вставленный в массиве столбец может «сломать» формулу. Когда вы вставляете внутри массива столбец, формула автоматически не изменяет номер столбца, из которого вы берете значение. А представьте, что вы работаете с чужим файлом и просто не знаете о том, что где-то есть ссылка на этот массив с помощью функции VLOOKUP (ВПР)
  5. Это формула, которая может значительно увеличивать объем расчетов там, где это совершенно не нужно. Представьте, что вы работаете с массивом с 50 колонками. Значение, по которому вы ищете находится в крайней левой колонке, а значение, которое вам нужно – в правой. Изменение любой ячейки из 48 никак не влияющих на значение столбцов заставит вашу формулу пересчитаться. Зачем?

И что теперь?
Действительно. Я не призываю вас вместо VLOOKUP (ВПР) пользоваться Ctrl + C, Alt + Tab, Ctrl + F, Ctrl + V, Enter, Esc, Ctrl + C, Alt + Tab, Tab, Ctrl + V (и это если вы знаете цену клавиатуре и не пользуетесь мышью). Ни в коем случае!

Я предлагаю вам пользоваться сочетанием функций INDEX (ИНДЕКС) + MATCH (ПОИСКПОЗ). Работает это так.

INDEX (массивоткудавзять, [номерстроки], [номерстолбца])

Формула берет значение, которое находится в строке номерстроки и в столбце номерстолбца в массиве массивоткудавзять.

Если пропустить значение номер_строки (просто ставится две запятых или точки с запятой подряд, в зависимости от ваших настроек), то программа автоматически возьмет значение из первой строки.

Номер столбца можно вообще опустить (закрыть скобку после номера строки), тогда программа возьмет значение из первого столбца массива.

MATCH (чтоискать, гдеискать, как_искать)

что_искать – ссылка на ячейку или значение, которое будем искать.

где_искать – массив данных, в котором ищем (самая левая колонка в случае с VLOOKUP (ВПР))

как_искать — -1, 0 или 1. Обычно используем 0 – точное совпадение (отлично работает в несортированных массивах). -1 – поиск первого значения, которое меньше искомого (массив данных должен быть отсортирован по ключу по убыванию). 1 – поиск первого значения, которое больше искомого (массив должен быть отсортирован по возрастанию).

Конкретный пример.

Читаем формулу изнутри наружу, как обычно. В этом примере формула определяет номер строки, соответствующей значению из колонки A:A в диапазоне D:D, а потом с помощью функции INDEX выбирает значение с этим номером строки из колонки O:O.

См. также: Почему я пользуюсь ссылками на колонки, а не на конкретные диапазоны

И еще один пример

Одно пояснение: обратите внимание, как меняется формула при копировании влево. Больше не надо менять номер столбца, если данные организованы с периодами в столбцах.

Рубрики
Ноу-хау

Как Excel пересчитывает книгу и почему надо избегать волатильных функций

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

Как Excel пересчитывает книгу

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

Когда мы с вами создаем таблицу с формулами, при записи каждой формулы каждой ячейке также присваивается несколько дополнительных атрибутов, кроме прочего это: (1) значение, (2) связи — то есть от каких ячеек она зависит. Эти связи можно посмотреть с помощью функциональности Trace precendents.

Для чего нужны эти связи?

Предположим, у нас есть формула =B2*C2 в ячейке D3 (я поместил ее в отдельную строку только для того, чтобы было лучше видно связи).

Значение этой формулы зависит от значений ячеек B2 и C2. Поэтому пересчитывать ее имеет смысл только тогда, когда эти значения меняются. До тех пор, пока из значения неизменны и формула в ячейке D3 не меняется, нет необходимости ее пересчитывать.

Эта система связей существует в Excel для того, чтобы избежать множества ненужных вычислений и значительно ускорить расчеты.

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

Microsoft приводит следующий список функций (он менялся до 1997 года, но мы проигнорируем старые версии Excel):

NOW (ТДАТА)
TODAY (СЕГОДНЯ)
RAND (СЛЧИС)

RANDBETWEEN (СЛУЧМЕЖДУ) (Miscrosoft ее не упоминает, но она волатильная)
OFFSET (СМЕЩ)
INDIRECT (ДВССЫЛ)
CELL (ЯЧЕЙКА)
INFO (ИНФОРМ)

Большинство этих функций волатильны по собственной природе. Пожалуй, единственное, что удручает — это функция OFFSET, которая на самом деле вполне предсказуемо зависима от нескольких значений — то есть ее вполне можно было сделать и неволатильной. Надеемся, что однажды она станет нормальной функцией, которой можно будет полноценно пользоваться.

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

Если Вы часто работаете с большими файлами, вам могут быть интересна статья о работе с ними.

Рубрики
Ноу-хау

Окно контрольного значения для отслеживания результатов

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

Для этого в Microsoft Excel есть готовая функциональность, которая называется Окно контрольного значения (Watch window). Чтобы ей воспользоваться необходимо выполнить несколько простых шагов:

  1. На ленте выбрать Формулы => Окно контрольного значения
  1. Выберите ячейки, в которых находятся результаты расчета
  2. Нажмите кнопку Добавить контрольное значение в появившемся окне и подтвердите выбранные ячейки, нажав Ok.

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

Вы можете скачать пример ниже. Откройте окно контрольного значения (если уже не открыто) и попробуйте менять значения в желтых ячейках и следите за значениями в окне контрольного значения.