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

Расчеты со временем

Данный вопрос пришел на форму обратной связи. Вы тоже можете задать их здесь.

Здравствуйте! я столкнулась с незнакомой мне таблицей учета рабочего времени. На предприятии рабочий день с 10:00 до 19:00. в таблицу вноситя время прибытия и убытия сотрудников, есть столбцы «опоздание», «переработка» и «переработка с учетом опозданий». Посоветуйте пожалуйста, как нужно задать формулы для расчетов в этих столбцах! Прикрепляю к письму образец табеля.

Файлы с вопросом и моим вариантом решения можно скачать здесь.

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

Все решение построено на использовании функции IF (ЕСЛИ) и простом вычитании. О функции IF (ЕСЛИ) читайте здесь.

Пожалуйста, обратите внимание, что время хранится в программе Excel, как дробь. Например, если ввести в ячейку время 12:00 и перевести ее потом в обычный формат, то получится 0.5. Что это нам дает? Все элементарно — если время воспринимается, как число, значит с ним можно производить вычисления. Например, если из 08:00 вычесть 03:30, то получится 04:30. И этим действительно удобно пользоваться.

Есть только одна проблема. Хоть время и хранится в ячейке, как дробь, отрицательное время Excel отобразить не может. Именно поэтому и пришлось обратиться к помощи функции IF (ЕСЛИ).

Из того, о чем я не писал на сайте, я использовал только формулу TIME (ВРЕМЯ). Она возвращает время в обсчитываемом формате. Работает совершенно идентично формуле DATE (ДАТА). Про нее можете почитать здесь.

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

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

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

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

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

они должны сохраняться либо другими словами по нажатию кнопки заполняется новая строка 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 или оставляйте комментарий внизу страницы.