Графики и диаграммы в Excel
Как построить график в Excel
Предположим, что Вы готовитесь к выступлению о результатах деятельности предприятия. Ваша задача – представить материал в наиболее наглядном и показательном виде. Не секрет, что лучше всего с этой задачей можно справиться при помощи графиков и диаграмм.
Этим мы и займемся.
К примеру, мы имеем дело с отчетом по продажам за 3 года следующего вида:
Информация читается довольно сложно. Для презентации лучше преобразовать ее в
график.
график.
Построим график и посмотрим тренд продаж по видам товаров.
За построение графиков и диаграмм в Excel отвечает «Мастер диаграмм», который вызывается через функцию "Вставки"-"Диаграмма" главного меню:
или соответствующей кнопочкой стандартной панели (обведена красным):
Жмем «Далее» и видим окно, где нужно заполнить диапазон диаграммы и выбрать направление диаграммы (строки или столбцы). Выбираем диапазон диаграммы путем нажатия на кнопку, обведенную красным:
Сюда должны войти данные, участвующие в тренде. В нашем случае, это будет поле, где указано количество продаж без итоговой суммы, шапки и периодов.
Для выбора диапазона нажимаем на левую верхнюю ячейку выделяемого диапазона и протягиваем до нижней правой ячейки. Нажимаем на кнопку, выделенную красным, и возвращаемся в мастер диаграмм:
Дальше выбираем направление таблицы. В нашем случае данные о продажах расположены по столбцам, поэтому мы выбираем "Ряды" в столбцах:
Для того, чтобы подписать оси таблицы и наименование товаров, зайдем в закладку "Ряд".
В окошке "Ряд" выбираем ряд, которй собираемся переименовать, в поле "Имя" пишем нужное наименование и проверяем выбранный диапазон в поле "значение" для каждого наименования.
Для того, чтобы подписать ось "x", нажимаем на соответствующую кнопочку (обведена синим) и выделяем периоды:
Возвращаемся в "Мастер диаграмм" и получаем:
Нажимаем "Далее" и оформляем полученную таблицу.
Здесь есть возможность дать заголовок полученному графику, подписать оси X и Y и пр.
Следующим шагом нужно выбрать размещение готового графика. Его можно поместить на том же листе, где расположена базовая таблица, либо на отдельном листе во всю страницу:
Выбираем способ размещения и нажимаем "Готово".
Как построить диаграмму в Excel
Предположим, что Вы готовитесь к выступлению о результатах деятельности предприятия. Ваша задача – представить материал в наиболее наглядном и показательном виде. Не секрет, что лучше всего с этой задачей можно справиться при помощи графиков и диаграмм.
диаграмм.
Этим мы и займемся.
В предыдущем уроке мы научились строить графики.
А сейчас займемся построением диаграммы, для того, чтобы наглядно показать, какую долю составляют продажи отдельных товаров в общей сумме.
Возьмем, к примеру, таблицу со следующими данными:
За построение графиков и диаграмм в Excel отвечает «Мастер диаграмм», который вызывается через функцию "Вставки"-"Диаграмма" главного меню:
или соответствующей кнопочкой стандартной панели (обведена красным):
Выберем из предложенного «Круговую» диаграмму и ее «Вид»:
Жмем «Далее» и видим окно, где нужно заполнить диапазон диаграммы и выбрать направление диаграммы (строки или столбцы). Выбираем диапазон диаграммы путем нажатия на кнопку, обведенную красным:
Сюда должна войти информация по количеству проданных единиц товара без шапки и наименований товаров.
Как построить обновляемый график точки безубыточности в Excel
Итак, перед Вами стоит задача построить график точки безубыточности (или любой другой) так, чтобы при добавлении новых данных в таблицу, на основе которой создан график, обновлялся и сам график.
В предыдущих уроках мы научились строить графики и диаграммы, однако забыли отметить довольно-таки важную функцию "Мастера диаграмм":
При внесении любых изменений в имеющуюся таблицу меняются и данные графика.Используя данное свойство "Мастера диаграмм" можно построить график с учетом будущих значений. По мере добавления данных в таблицу, в таком графике будут появляться новые данные.
Для примера построим обновляемую гистограмму точки безубыточности на основе ниже представленной таблицы. Как видим, в таблице представлены данные за 2 года и есть готовые столбцы, которые будут заполняться по мере появления информации:
Вызываем «Мастер диаграмм» через функцию "Вставки"-"Диаграмма" главного меню:
или соответствующую кнопочку стандартной панели (обведена красным):
Выберем из предложенного «Гистограмму» и ее «Вид»:
Жмем «Далее» и видим окно, где нужно заполнить диапазон диаграммы и выбрать направление диаграммы (строки или столбцы). Выбираем диапазон диаграммы путем нажатия на кнопку, обведенную красным:
В качестве диапазона диаграммы выбираем значения безубыточного объема.
Для выбора диапазона нажимаем на кнопку, выделенную красным, потом на крайнюю левую ячейку выделяемого диапазона и протягиваем до крайней правой. Нажимаем на кнопку, выделенную красным, и возвращаемся в мастер диаграмм:
Дальше выбираем направление таблицы. В нашем случае данные о безубыточном объеме расположены в строке, поэтому мы выбираем "Ряды" в строках:
Для того, чтобы подписать значения оси X, а именно периоды, заходим в закладку "Ряд" и нажимаем на кнопку поля "Подписи оси X" (обведена красным):
Выбираем поля, где отмечены периоды, путем протягивания слева направо соответствующих полей:
и возвращаемся в "Мастер диаграмм". Как видим, ось Х теперь имеет значения периодов. В поле "Имя" напишем наименование изучаемого показателя ("Безубыточный объем"):
Нажмем "Далее" и приведем готовый график в нужный нам вид. Например, поменяем заголовок таблицы (в закладке "Заголовок") и добавим значения (в закладке "Подписи данных"):
Следующим шагом нужно выбрать размещение готового графика. Его можно поместить на том же листе, где расположена базовая таблица, либо на отдельном листе во всю страницу:
Выбираем способ размещения и нажимаем "Готово".
Гистограмма готова!
Как изменить готовый график
При желании можно менять параметры готовой гистограммы, нажав на таблицу правой клавишей мыши. Например, при желании можно легко превратить гистограммы в график или диаграмму, а также поменять исходные данные:
Также об изменении параметров таблицы читайте в статье о построении графиков.
Итак, мы научились строить графики, диаграммы и гистограммы. Только Вам решать, какой из видов графиков использовать в том или ином случае.
Для сравнения фактической и безубыточной выручки более показательно использовать график:
Функции в Excel
Функция ВПР в EXCEL
Функция ВПР позволяет найти данные в исходной таблице и вывести их в любой ячейке новой таблицы.
новой таблицы.
Основные условия работы данной функции:
- Наличие одинаковых элементов в сопоставляемых таблицах (например, код товара, фамилия сотрудника и т.д.).
- Сопоставляемые данные в исходной таблице должны быть отсортированы по возрастанию.
Например:
Как мы видим, данные в исходной таблице расположены по алфавиту. Данные же в новой таблице могут располагаться в любом порядке и не обязательно полным списком.
Функция ВПР поможет найти сумму по каждому сотруднику и перенесет ее в графу новой таблицы рядом с фамилией.
Итак, активизируем нужную ячейку, например, рядом с фамилией «Васильев» и вызываем функцию:
Нажимаем ОК. Всплывает такое окошко:
В графу «Искомое значение» добавляем фамилию, по которой будет производиться сопоставление:
В графу «Таблица» добавляем весь диапазон исходной таблицы:
В графе «Номер столбца» пишем «2», т.к. хотим перенести в новую таблицу сумму по данному сотруднику, которая находится во втором столбце выделенного диапазона исходной таблицы.
Нажимаем ОК и видим, что функцию ВПР нашла сумму по Васильеву из исходной таблицы.
Для того, чтобы вставить полученную формулу в следующую ячейку, нужно закрепить в формуле область искомой таблицы с помощью абсолютной ссылки:
После чего можно копировать и вставлять эту формулу до конца списка. Суммы по всем фамилиям будут найдены.
Таким образом, функция ВПР может существенно облегчить работу по поиску и сопоставлению информации.
Единственный существенный минус этой формулы – необходимость сортировки по возрастанию исходных данных.
Чаще всего на практике мы встречаемся с тем, что нам нужно сопоставить разрозненные данные из исходной таблицы с такими же разрозненными данными их новой таблицы. В этом случае могу порекомендовать сочетание функций ИНДЕКС и ПОИСКПОЗ.
Логический оператор "ЕСЛИ". Как составить в Excel список студентов, не допущенных к экзаменам
Предположим, что Вы работаете в деканате института и вам нужно срочно составить список студентов допущенных к экзаменам. На руках вы имеете данные о сдаче зачетов.
Ваша задача - допустить к экзаменам только тех студентов, которые сдали все зачеты. Если хотя бы один зачет не сдан, студент не должен быть допущен.
Если хотя бы один зачет не сдан, студент не должен быть допущен.
На практике список длинный, вручную заполнять его долго. К тому же можно допустить ошибки. Т.е. перед вами стоит задача автоматизировать процесс.
Для осуществления данной операции будем использовать логический оператор "ЕСЛИ", который можно найти в логических функциях:
Однако, прежде чем использовать его, нужно знать, что данный оператор работает только с цифрами. Он никак не реагирует на текст.
Что будем делать?
Будем переводить текст в цифры! Если мы примем "зачет" за "1", а "незачет" - за "0", то мы сможем при помощи логического оператора ЕСЛИ найти сумму значений, отличную от количества зачетов (в нашем случае - трех).
Начнем с того, что будем переводить слова в цифры
Для того, чтобы быстро провести эту операцию, воспользуемся автофильтром.
Добавим столбцы по количеству зачетов (в нашем случае - три) и установим автофильтр:
Отсортируем первый столбец с помощью автофильтра по "зачету" (нужно нажать на стрелочку в нижнем правом углу ячейки "1 зачет" и выбрать среди вариантов "зачет"), поставим единицу в первую ячейку столбца, где слова будут преобразованы в числа, скопируем эту ячейку и вставим во все остальные ячейки этого столбца.
Ту же самую операцию проведем с вариантом "нет" 1-го зачета. Поставим "0".
Чтобы вернуться к полному списку, нужно нажать "все" в списке.
По такому же принципу проставим все цифры и просуммируем полученные значения:
Ну, и теперь кульминационный момент.
Приступим к использованию оператора "если"
Активизируем первую ячейку столбца "допущен к экзаменам" и находим оператор "если" в логических функциях:
В появившемся окне заполняем строки. Логическое выражение получается следующим: если значение ячейки H2 равно "3", то выдаваемое значение будет выглядеть как "да", в противном случае - "нет". Как видим, справа от логического выражения высветилось значение "ЛОЖЬ", т.к. значение ячейки H2 равно "2", а не "3":
Нажимаем "ОК" и протягиваем полученную формулу до конца списка (ставим курсор мыши на правый нижний угол ячейки и после появления крестика, нажав на него левой клавишей мыши, протягиваем до конца списка, не отпуская руку).
Готово! Теперь можно с помощью автофильтра отсортировать допущенных (выбрав "да") и недопущенных (выбрав "нет") студентов и сделать два отдельных списка.
Как вставить отфильтрованные значения на новый лист
Для того, чтобы перенести список на новый лист, необходимо выделить всю таблицу вместе с шапкой по серому полю, и, держа курсор мыши на выделенном поле, выбрать в меню, вызываемом правой клавишей мыши, "Копировать".
Далее нужно открыть новый лист и в любую ячейку столбца A вставить таблицу через "Вставку".
Следует отметить, что для сортировки указанных данных можно было обойтись и без оператора "если".
Достаточно было использовать условие автофильтра для сортировки суммированных значений. Для этого нужно нажать на стрелочку в нижнем правом углу ячейки "сумма" и выбрать "Условие..." В зависимости от цели, нужно выбрать среди команд "равно" (для допущенных студентов) или "не равно" (для недопущенных), среди значений - "3":
Результат получился тот же без одного лишнего шага. Лишний шаг был сделан, чтобы продемонстрировать возможности Excel.
Как автоматизировать табель учета рабочего времени в Excel и работать с ним
Как известно, Табель учета рабочего времени - это главный документ, в соответствии с которым начисляется заработная плата. Т.е. документ этот очень важный и допускать ошибки в нем крайне нежелательно. Мало того, что нужно внимательно занести данные о явках, больничных, отгулах, работе в выходные дни, нужно еще и посчитать общее количество отработанных дней и часов для каждого работника.
Сегодня мы будем автоматизировать в Excel Табель учета рабочего времени, а именно вторую его страницу. Заведем формулы в ячейки, где считаются итоговые значения. Таким образом, при внесении изменений, итоговые данные будут пересчитываться автоматически.
Главная проблема, с которой мы столкнемся, - это специальные коды, которые используются для обозначения дней. Так день явки обозначается как "Я" или "01", больничный - "Б" или "19", работа в выходной день - "РП" или "03" и т.д. (подробно см. 1 лист Табеля учета рабочего времени)
Каким же образом можно сосчитать буквы или цифры в текстовом формате?
Для того, чтобы сложить значения, необходимо присвоить всем явочным дням значение единицы, а неявочным - нуля.
Для решения этой задачи, вновь воспользуемся логическим оператором "ЕСЛИ", который имеется в функциях Excel (Функции (обведено красным) => Другие функции => Логические => ЕСЛИ).
Логический оператор "ЕСЛИ" поможет нам преобразовать буквенные или числовые выражения в текстовом формате в цифры.
Однако, размещаться эти цифры будут не в поле Табеля учета рабочего времени, а за его пределами. Необходимо выделить для этого поле, равное по размерам полю учета рабочих дней и часов Табеля, в любом месте страницы. Именно там мы и будем колдовать.
Начнем в того, что закрепим поля, чтобы нам было удобно работать (оставим в закрепленном поле несколько столбцов учета рабочих дней, так как на них мы будем ссылаться).
Ну, а теперь справа от всех полей Табеля начнем заводить формулы с помощью оператора "ЕСЛИ". Выбираем любую ячейку на уровне соответствующего поля таблицы и вызываем оператор.
Заполняем поля оператора.
Смысл операции заключается в том, чтобы присвоить текстовому формату "01" (либо "Я") значение равное "1". Итак, логическое выражение будет выглядеть следующим образом:
если адрес преобразуемой ячейки (в нашем случае D8) = "01", то значение ячейки AV8 будет считаться равным "1"
если адрес преобразуемой ячейки (в нашем случае D8) = "01", то значение ячейки AV8 будет считаться равным "1"
Прописываем данное выражение в полях оператора. Значение, которому присваивается число (в нашем случае "01") обязательно нужно взять в кавычки:
Поле Значение_если_ложь можно оставить пустым, либо записать в него "0". Мы оставим его пустым.
Теперь только после появления в ячейке D8 значения "01" в ячейке AV8 появится единица. В противном случае мы увидим надпись "ложь" (либо ничего, в случае, если бы мы поставили "0" в поле оператора Значение_если_ложь). Например:
Текстовый формат ячейки в Excel
Стоит обратить внимание на один существенный момент. Если вы вводите в ячейку значение "01", а в ней появляется "1", значит, формат ячейки является числовым, а не текстовым. Его нужно поменять. Иначе формула работать не будет.
Выделяем нужную ячейку, вызываем правой кнопкой мыши меню и выбираем "Формат ячеек". В закладке "Число" выбираем "текстовый" формат.
После смены формата нужно еще раз ввести значение в ячейку.
Если нужно сменить формат сразу во всех ячейках поля, выделить нужно все эти поля и поменять формат одновременно во всех с помощью той же функции.
На заметку: если после смены формата формулы не работают, значит, в ваших параметрах обновление стоит в ручном режиме.В этом случае необходимо обновить ячейку с формулой вручную (щелкнуть по ней два раза и нажать "enter") либо нажать кнопку F9, которая одновременно обновляет весь лист Excel.
Если вы хотите настроить автоматическое обновление страницы (а это просто необходимо сделать в нашем случае, иначе каждый раз придется вручную обновлять все ячейки), зайдите в "Сервис" => "Параметры" => "Вычисления" и выберите вариант "автоматически":
Итак, мы ввели в ячейку одно условие. Но как быть со всем остальным списком обозначения дней?
Все довольно-таки просто! В строке состояния копируем все выражение кроме знака "=",
ставим ";" перед последней скобкой выражения и вставляем после него скопированное выражение:
Исправляем во втором выражении "01" на "03" (работа в выходной день):
Готово! Смысл этого выражения будет звучать так:
Если значение ячейки D8 соответствует “01”, то значение ячейки AV8 нужно считать за 1, если нет, то действует новое условие: [если значение ячейки D8 соответствует «03», то значение ячейки AV8 нужно считать за «1»].
И так со всеми последующими условиями.
Если значение ячейки D8 соответствует “01”, то значение ячейки AV8 нужно считать за 1, если нет, то действует новое условие: [если значение ячейки D8 соответствует «03», то значение ячейки AV8 нужно считать за «1»].
И так со всеми последующими условиями.
Теперь добавим любой код дня неявки. Скопируем второе выражение, сразу захватив ";":
и вставим опять же перед всеми скобками:
Исправляем "03" на "16" (отпуск за свой счет), и "1" на "0", чтобы этот день не учитывался как отработанный:
Вот и всё! Все остальные коды проставляем аналогичным образом.
Учтем, что логический оператор "ЕСЛИ" позволяет использовать не более 7-ми аргументов, поэтому если количество используемых вами кодов больше 7-ми, можно проставить коды только для явочных дней. Все остальные коды Excel в любом случае посчитает как "0".
Копируем полученную формулу и вставляем во все ячейки, которые соответствуют ячейкам Табеля учета рабочего времени:
Теперь можно считать итоговые значения. Выделяем ячейку с итоговой суммой за первую половину месяца и с помощью автосуммы складываем значения, полученные в поле справа от Табеля учета рабочего времени:
То же самое делаем со второй половиной месяца.
Далее считаем количество часов за каждую половину месяца. Можно сделать это с помощью автосуммы, т.к. в ячейках, где указаны часы, применен числовой формат:
Однако на практике можно столкнуться с тем, что при работе с табелем (копирование, вставка, вырезание ячеек) происходит сбой формата ячеек. Поэтому отметим одну особенность подсчета суммы в Excel.
Для того, чтобы сосчитать значения в любом формате нужно пользоваться ручным суммированием. Автосумма считает только значения в числовом формате.
Используем в нашем табеле эту особенность:
Для этого активизируем ячейку, в которой хотим создать формулу, и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем (выделяем левой клавишей мыши) соответствующие ячейки в следующем порядке:
= [Количество дней(часов) за 1 половину месяца] + [Количество дней(часов) за 2 половину месяца] ENTER
При желании можно автоматизировать столбец "Количество неявок". Для этого вне поля Табеля учета рабочего времени выделим ячейку, в которой будем проставлять количество календарных дней в текущем месяце.
С помощью абсолютной ссылки посчитаем дни неявки:
и часы неявки по формуле:
= [Количество дней в месяце] * [Количество часов в день] - [Отработанное количество часов] ENTER
Все полученные формулы копируем и вставляем в соответствующие ячейки табеля до конца списка сотрудников.
Все, что можно было автоматизировать, мы автоматизировали. Теперь можно скрыть столбцы, которые находятся справа от Табеля учета рабочего времени.
Табель с числовыми кодами готов (СКАЧАТЬ)! Пользуйтесь на здоровье.
Для создания табеля с буквенным кодами (СКАЧАТЬ) нужно заменить соответствующие цифровые коды на буквенные.
Если вы найдете способ автоматизировать оставшиеся поля, напишите мне по адресу: ngt@inbox.ru
Остался один маленький нюанс. В случае, если количество сотрудников превышает число строк готового табеля, необходимо скопировать готовый лист и добавить столько его копий в файл, сколько необходимо для внесения всех сотрудников.
Как скопировать лист и вставить в существующий файл
Активизируем нужный нам лист (внизу страницы нажимаем на него левой клавишей мыши), вызываем меню правой клавишей мыши и выбираем «Переместить/скопировать»:
Выбираем лист, перед которым нужно поместить копируемый лист и стмечаем галочкой квадратик «создавать копию»:
Нажимаем ОК и получаем новый лист, полностью идентичный копируемому. Созданная копия помечается цифрой 2, заключенной в скобки - (2).
Полученный лист можно переименовать. Таких листов можно сделать неограниченное количество.
Выбираем контрагентов, сумма задолженности которых больше судебных издержек. Логический оператор "СУММЕСЛИ"
Многие организации работают сегодня с отсрочкой платежа. Почти каждая из них рано или поздно сталкивается с задержками оплаты. Зачастую дело доходит до суда.
При этом задолженность по оплате может составлять как существенную сумму, так и мизерную. Естественно, нет смысла подавать в суд на неплательщика, сумма задолженности которого меньше чем судебные издержки.
Сегодня мы составим список должников, выявим тех, на кого есть смысл подавать в суд и посчитаем сумму, на которую мы можем рассчитывать в случае положительного исхода, с учетом пени за просроченный период.
Для этого мы будем использовать
логический оператор «СУММЕСЛИ» в Excel
Начнем с того, что составим либо сформируем в бухгалтерской программе список должников следующего вида:
Используя данные о дате отгрузки, определим крайний срок оплаты исходя из предоставленной отсрочки. Прибавим количество дней отсрочки к дате отгрузки.
Для того, чтобы прибавить число к дате в Excel
необходимо произвести следующие действия:
следующие действия:
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= ДАТА + ЧИСЛО ENTER
Если дата не получилась, проверим формат ячейки и удостоверимся в том, что формат установлен в режиме «дата».
Получаем:
Теперь рассчитаем количество просроченных дней. Для этого вычтем из текущей даты дату крайнего срока оплаты.
Для того, чтобы в Excel вычесть из одной даты другую
необходимо произвести следующие действия:
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= ДАТА(1) – ДАТА(2) ENTER
Если число не получилось, опять же проверим формат ячейки и удостоверимся в том, что формат установлен в режиме «числовой».
В нашем случае мы будем ссылаться на одну и ту же ячейку текущей даты, поэтому воспользуемся абсолютной ссылкой
Протянем формулу до конца листа и получим:
Ну, а теперь рассчитаем пеню за период
Расчет пени за период просрочки
Рассмотрим два случая:
- исходя из 0,1% за день просрочки
- исходя из ставки рефинансирования на день расчета (возьмем 10%)
Рассчитаем пеню в Excel:
1. 0,1% от суммы задолженности за 1 день просрочки
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= 0,1% * Сумма задолженности * Количество просроченных дней ENTER
2. Ставка рефинансирования на день расчета (в примере - 10%)
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= 10% * Сумма задолженности / 365 * Количество просроченных дней ENTER
Сложим полученную пеню и сумму задолженности
и приступим к вычленению тех контрагентов, на которых есть смысл подавать в суд.
Логический оператор «СУММЕСЛИ»
Предположим, что судебные издержки составляют 950 рублей на одного клиента. Для того, чтобы узнать общую сумму, на которую мы можем рассчитывать после положительного решения суда без учета задолженности контрагентов, имеющих задолженность меньше 950 рублей с учетом пени, используем логический оператор «суммесли».
Активизируем ячейку, в которой хотим получить сумму, и находим в функциях (обведено красным) «математические» и выбираем «суммесли».
Посмотрим, что мы имеем:
Давайте разбираться.
Окошко «Диапазон» нужно для того, чтобы выбрать область значений, среди которых будет производиться выборка. В нашем случае это будут все значения последнего столбика кроме итоговой суммы.
Окошко «Критерий» служит для того, чтобы задать условие выборки. В нашем случае условие будет: >950
И, наконец, окошко «Диапазон_суммирования» нужен для задания значений, сумму которых мы хотим получить с учетом условия. В нашем случае «Диапазон» и «Диапазон_суммирования» будут совпадать, т.к. мы задаем числовое условие, имеющее отношение только к последнему столбику.
После заполнения полей получаем:
В результате наших расчетов получаем общую сумму, планируемую к получению, за исключением сумм задолженностей, которые не превышают 950 рублей.
Для демонстрации работы логического оператора «суммесли» проведем еще одну выборку.
В первом случае «Диапазон» и «Диапазон_суммирования» совпали. Рассмотрим случай, когда в данных окошках указываются разные диапазоны значения.
Например, мы хотим выяснить сумму задолженности только по «Клиенту 1»
В этом случае в графе «Диапазон» мы проставляем значения первого столбика, а в графе «Критерий» вручную пишем «Клиент 1», либо выбираем любую ячейку, содержащую это значение. В графе «Диапазон_суммирования» выбираем последний столбик, т.к. хотим выбрать задолженность по Клиенту 1 из общей суммы.
Получаем:
Как видите, логический оператор «суммесли» многофункционален и при правильном подходе может пригодиться при решении различных задач.
Как установить текущую дату в Excel с помощью функции "СЕГОДНЯ"
Функция "СЕГОДНЯ" позволяет установить текущую дату, которая будет меняться каждый день без каких-либо усилий с Вашей стороны.
Для того, чтобы установить ее, нужно нажать на кнопочку функций и выбрать "другие функции":
Находим в списке функцию "СЕГОДНЯ":
И нажимаем два раза "ОК".
Вот и всё! Дата установлена. Данная функция выводит дату в числовом формате, что позволяет производить вычисления с ее использованием.
Взаимодействие с Word
Как внести изменения в готовый прайс-лист
Закрепление областей в Excel
Начнем с того, что закрепим области, чтобы нам было удобно работать.
Делается это очень просто: активизируем ячейку, которая находится слева и внизу от закрепляемой области заходим в "Окно" главного меню в верху страницы и выбираем "Закрепить области":
Таким образом мы выделили ячейку со значением "10", чтобы закрепить шапку и наименования товара. Так можно закрепить любые области в любом месте страницы.
Если нам потребуется снять закрепление после проведения работ над документом, зайдем опять же в "Окно" и выберем "Снять закрепление областей".
А теперь приступим непосредственно к внесению изменений в прайс-лист.
С какими проблемами мы можем столкнуться в процессе? Например:
1. Старый прайс-лист сделан в Word.
2. В связи с изменением наименований нужно произвести много одинаковых изменений.
3. Заводим формулы, а они не считаются.
Начнем по порядку.
Импорт в Excel
Для того, чтобы произвести импорт документа из Word в Excel открываем оба документа, выделяем все поля таблицы в Word, удерживая левую кнопку мыши, копируем выделенную область, стоя на черном поле:
и вставляем выделенную область в Excel.
Следующим шагом форматируем таблицу, расширяя нужные столбцы для более удобного чтения.
C копированием таблицы мы справились, теперь приступим к внесению изменений.
Поиск и замена значений в Excel
Предположим, что наш производитель кардинально изменил наименование товара. Если раньше все игрушки назывались "куклы", то теперь пришли в новым наименованием "пупсы". Неужели мы будем заходить в каждую ячейку и менять все значения? Конечно же нет!
Неужели мы будем заходить в каждую ячейку и менять все значения? Конечно же нет!
Мы воспользуемся функцией замены, которую предлагает нам Excel.
Для этого мы выделим область, где будет происходить замена и вызовем эту функцию.
Вызвать функцию поиска и замены в Excel можно тремя способами:
1. Одновременным нажатием SHIFT и F5;
2. "Правка" в главном меню -> "Найти"
3. Бинокликом в меню правки вверху страницы.
Если Вы не видите у себя биноклика, его можно добавить в панель и он всегда будет там виден. Т.е. Вы можете самостоятельно выбрать нужные Вам функции (см. как это сделать).
Итак, выделяем область замены, вызываем функцию правки, заходим в закладку "Заменить", пишем в строке "Найти" старое значение, в строке "Заменить на" - новое и нажимаем на кнопочку "заменить все" :
Вот мы и избавили себя от долгой и нудной работы! 
Поиск и устранение причин, по которым Excel отказывается считать
Ну, а теперь давайте искать причины, по которым Excel отказывается считать.
Их несколько:
1. Внутри ячейки помимо цифр есть текст;
2. Задан неверный формат ячейки;
3. Документ скопирован из Word и имеет принудительный пробел между разрядами;
4. Вместо (,) копейки отделены от рублей (.) (или наоборот в зависимости от настроек).
5. В Параметрах Excel задан стиль ссылок R1C1 (т.е. и строки, и столбцы обозначаются цифрами).
Идем по пунктам.
1. Необходимо удалить из ячейки текcт и все посторонние знаки кроме цифр. Если Вам нужно указать валюту расчета, это можно сделать, применив "денежный" или "финансовый" формат.
2. Для того, чтобы задать правильный формат ячеек, нужно вызвать "Формат ячеек" и выбрать в закладке "Число" "числовой", "денежный" или "финансовый" формат.
Если после смены формата, формула по прежнему не считает, попробуйте обновить ячейку, щелкнув по ней два раза.
Если и это не помогло, разбираемся дальше.
3. Если мы знаем, что документ скопирован из Word и цифры разделены принудительными пробелами, нам нужно избавиться от этих пробелов. Чтобы не заходить в каждую ячейку и не удалять их вручную воспользуемся правкой.
Выделяем нужную область, вызываем "Правку" и в поле "Найти" пишем пробел, а поле "Заменить на" очищаем от всяких значений. После нажатия кнопочки "заменить все" все пробелы должны исчезнуть.
4. Ту же самую операцию проводим и в том случае, если нужно поменять все точки на запятые или наоборот. В поле "Найти" пишем ".", в поле "Заменить на" пишем "," и нажимаем кнопочку "заменить все".
ВАЖНО ДЛЯ 2-4 ПУНКТОВ: активизируйте для замены только ту область, где хотите произвести изменения, иначе Вы рискуете изменить весь текст на странице.
После проведения этих мероприятий, формулы должны считаться!
Этим мы и займемся. Сделаем наценку 10% на старую цену нашего прайс-листа.
Формула будет такой:
Старая цена + 10% = Новая цена
Могу предложить два варианты заведения формулы добавления процента к числу в Excel.
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
1 вариант:
= СТАРАЯ ЦЕНА + 10% * СТАРАЯ ЦЕНА ENTER
2 вариант:
= СТАРАЯ ЦЕНА * (1 + наценка/100) ENTER
или
= СТАРАЯ ЦЕНА * 1,1 ENTER
Итак, получаем.
Теперь нам остается только перенести новые цены в колонку со старыми. Однако, если мы это сделаем простым копированием-вставкой, у нас ничего не получится.. вернее получится абракадабра. Почему? Да потому, что мы скопируем не значения, а формулы. Эти формулы будут ссылаться на текст.
Чтобы скопировать значения, воспользуемся специальной вставкой и вставим значения:
Обратите внимание, что цена за упаковку пересчиталась автоматически.
Нам остается только удалить столбец "Новая цена" и наш новый прайс-лист готов!
Как перенести таблицу из Excel в Word без границ
Перенести таблицу из Excel в Word совсем без ячеек не получится, т.к. Excel - это таблица.
Вопрос в другом: будут ли видны ячейки при печати.
Если в Excel не нарисованы границы, то они не видны и при печати страницы. Соответственно при копировании и вставке в Word они тоже не будут видны при печати. Хотя на странице Word они все равно будут обозначены:
Eсли же границы обозначены в Excel, то есть два варианта:
варианта:1. Убрать границы в самом Excel
через "Формат ячеек" (вызывается правой клавишей мыши) -> "Граница"
Выделяем всю нашу таблицу, вызываем "Границу" и нажимаем "нет":
При копировании и вставке в Word границы при печати видны не будут.
2. Убрать границы в Word
Это немного дольше, но тоже несложно.
Находим в главном меню "Таблицу" -> "Нарисовать таблицу" и видим панель форматирования таблиц:
В типе границы выбираем "Без границы":
Видим карандашик, с помощью которого проводим по всем имеющимся границам:
При печати их видно не будет, хотя на листе Word они все равно будут обозначены.
Как добавить дополнительные кнопки в меню. Команды Excel
Предположим, что Вы знаете, что в комплект Excel входит определенная функция, но не видите ее в меню вверху страницы. Где же она хранится и как ее добавить?
Заходим в "Вид" в главном меню, выбираем "Панель инструментов" и в самом низу выбираем кнопочку "Настройка..."
Вот тут-то и хранятся все инструменты!
В закладке "Команды" можно найти любую полезную кнопочку и добавить ее путем перетягивания в ваше верхнее меню.
Нажмите левой клавишей мыши на нужную Вам функцию. Появится крестик. Не отпуская кнопки перетяните функцию в верхнюю часть экрана и добавьте к другим кнопочкам, отпустив клавишу мыши.
Работа с листами в Excel
Работа с листами в Excel. Коэффициенты эффективности работы предприятия в Excel
Перед нами стоит задача рассчитать несколько основных коэффициентов, позволяющих оценить эффективность деятельности компании в динамике.
Для расчета коэффициентов придется использовать данные из разных отчетов (баланс: форма № 1 и форма № 2), которые находятся в разных файлах.
Для начала переместим нужные нам листы в один файл, а затем сделаем сводную таблицу на новом листе.
Предположим, что у нас есть файл с Формой № 1 Баланса, где хранятся балансы за все периоды. Наша задача скопировать нужный нам лист и перенести в новый файл.
Копирование листа из одного файла в новый файл
Активизируем нужный нам лист (внизу страницы нажимаем на него левой клавишей мыши), вызываем меню правой клавишей мыши и выбираем «Переместить/скопировать»:
Нажимаем ENTER и получаем новый файл, содержащий нужный нам лист. В этот лист мы будем помещать все данные о балансах за все периоды, и на основе этих данных рассчитывать коэффициенты в динамике.
помещать все данные о балансах за все периоды, и на основе этих данных рассчитывать коэффициенты в динамике.
Переименуем лист для удобства работы в «форма1». Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):
и переименуем в «форма1»:
Лист готов. Таблицу можно продолжить, добавляя значения следующих периодов.
Учитывая то, что для расчета коэффициентов нам нужны значения выручки за период, следующим шагом мы добавим в файл лист формы № 2 баланса (либо данные по выручке за период из других источников).
Копирование/перемещение листа из одного файла в другой
Для того, чтобы перенести лист из одного файла в другой, необходимо активизировать нужный лист (внизу страницы нажать на него левой клавишей мыши), вызвать меню правой клавишей мыши и выбирать «Переместить/скопировать»:
Отмечаем галочкой квадратик «создать копию», если хотим, чтобы переносимый лист остался в первоначальном файле (если нужно просто перенести лист, галочку не ставим), и в окошке «в книгу» выбираем имя файла, в который переносим лист (в нашем случае koefficienty.xls):
Выбираем лист, перед которым хотим разместить переносимый лист, либо помещаем лист в конце после всех имеющихся в файле листов:
Переименуем лист для удобства работы. Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):
По аналогии с листом "Форма1" мы будем добавлять в этот лист данные за все следующие периоды.
Ну, а теперь добавим новый лист в наш файл, на котором мы будем производить расчеты коэффициентов эффективности деятельности компании.
Делается это очень просто. Вызываем "Вставку" в главном меню и выбираем "Лист":
Подготовим таблицу для занесения данных и возьмем для примера расчет Коэффицента оборачиваемости товарных запасов:
Как видим, один из показателей нужно взять из Формы № 1 (Среднегодовое значение товарных запасов), а другой - из Формы № 2 (Себестоимость проданных товаров).
Для того, чтобы сделать формулу, ссылающуюся на разные листы, произведем следующие шаги:
- активизируем нужную ячейки и пишем в ней знак "="
- выделяем ячейку со значением себестоимости на листе "Форма2"
- пишем знак деления - "/"
- открываем первую скобку "(", в которой будем рассчитывать среднегодовое значение товарных запасов), и вторую скобку "(", в которую заключим сумму значений товарных запасов за период
- открываем лист "Форма1" и добавляем после двух открытых скобок сумму товарных запасов на 5 дат
- закрываем первую скобку ")" и делим сумму товарных запасов на 5 дат на "5" (вычисляем среднее значение)
- закрываем вторую скобку ")" и нажимаем ENTER
Получаем следующее выражение:
Для расчета периода, в течение которого запасы находятся на складе используем обратную формулу:
С заведением формулы мы закончили. А теперь предположим, что у нас не два коэффифиента, а гораздо больше. Неужели нам каждый раз придется заводить все эти сложные формулы?
Решение есть!
Как скопировать формулы, ссылающиеся на разные листы
Для начала скопируем готовые формулы, для чего активизируем все ячейки с формулами и правой кнопкой мыши вызовем "Копировать"
и вставим их в соседний столбец, выделив верхнюю ячейку столбца и выбрав "Вставить" в меню, вызванном правой кнопкой мыши:
Как сделать массовую замену формул
Осталась самая малость. Скопированные формулы повторяют имеющиеся, но ссылаются не на те ячейки, которые нам нужны. Это происходит из-за того, что в одном листе (Форма2) для расчета показателя за год используется всего одна ячейка, а в другом листе (Форма1) - 5 ячеек.
Т.е. в части ссылок на лист "Форма2" ссылки при копировании автоматически идут на нужные ячейки, а в листе "Форма1" нам придется передвинуть формулы на 4 ячейки (причем речь идет о буквенных обозначениях, цифровые меняться не будут).
Предлагаю сделать это "оптом" с помощью функции "Поиска и замены значений". Для этого проверим ячейки, на которые ссылается формула и уточним, на какие ячейки она должна ссылаться.
В нашем случае, второй столбик после копирования формулы ссылается в листе "Форма1" на ячейки J, K, L, M, N, в то время как для правильного результата должны ссылаться на M, N, O, P, Q. Как видим, две буквы повторяются. Их мы оставим. Значит, нам останется поменять J, K, L на O, P, Q.
А дальше все проще простого. Выделяем нужный столбец по серому полю (обязательно, иначе мы, не желая того, можем поменять значения других ячеек), вызываем функцию "поиска и замены" и меняем J на O, K на P, L на Q и т.д.
Так же будем действовать и при заполнении данных для следующих периодов. Таким образом, мы избавим себя от сложной работы, требующей пристального внимания.
На всякий случай проверяйте хотя бы одну ячейку из столбца на правильность занесения формул.
Форматирование в Excel
Как создать таблицу в Excel
По сути своей Excel - это и есть таблица, состоящая из ячеек. Нам остается только оформить нужный нам фрагмент. Активизируем нужные ячейки (выделяем их левой кнопкой мыши при удержании первой ячейки) и применяем к ним форматирование.
Для оформления таблиц мы будем использовать:
1. Изменение ширины и высоты ячеек.
(то же самое в Excel 2007-2010)
Для изменения ширины и высоты ячеек проще всего использовать серые поля по краям ячеек:
Поставьте курсор на границу между ячейками на сером поле и удерживая левую кпопку мыши потяните в сторону, после чего отпустите.
Для того, чтобы задать размер сразу нескольким столбцам или строкам, активизируйте нужные строки/столбцы, выделив их по серому полю и произведите выше описанную операцию над любой из выделенных ячеек серого поля.
2. Окно диалога "Формат ячеек", которое вызывается двуми способами: правой клавишей мыши через меню (что удобнее, т.к. всегда под рукой) или в главном меню через закладку "Формат".
При вызове "Формата ячеек" выпадает окно с закладками "Число", "Выравнивание", "Шрифт", "Граница", "Вид", "Защита".
Нам в первую очередь понадобятся закладки "Выравнивание" и "Граница".
При вызове "Формата ячеек" выпадает окно с закладками "Число", "Выравнивание", "Шрифт", "Граница", "Вид", "Защита".
Нам в первую очередь понадобятся закладки "Выравнивание" и "Граница".
Вкладка "Выравнивание" снабжена основными инструментами для изменения текста внутри ячеек:
- выравнивание текста не только по горизонтали (что можно сделать и через основное меню вверху страницы), но и по вертикали внутри ячейки
- перенос по словам
- объединение ячеек
- ориентация текста по вертикали и под углом
Новая версия программы (см. Excel 2007-2010) позволяет делать выравнивание по вертикали с использованием кнопки, расположенной в Главном меню. Как перевернуть таблицу в Excel
Предположим, у Вас есть готовая таблица следующего вида:
Вы хотите перевернуть ее, т.е. сделать так, чтобы шапка таблицы была расположена не по горизонтали, а по вертикали.
Для того, чтобы осуществить это действие, необходимо скопировать таблицу:
Активизировать любую свободную ячейку, вызвать меню правой кнопкой мыши и выбрать «специальную вставку»:
Поставить галочку в окошке «транспонировать»:
и нажать «ОК». Получаем:
.
Как подготовить прайс. Вычисления в Excel
Любой прайс-лист - это прежде всего таблица, наполненная данными.
Как правило, прайс-лист подразумевает перечисление товаров с указанием их стоимости. Дополнительно могут быть указаны различные показатели: параметры, кол-во в упаковке, штрих-код и пр.
Рассмотрим случай, когда дополнительно указывается количество мест в упаковке и стоимость всей упаковки.
Наполняем один столбец данными о товарах, во втором указываем количество товаров в упаковке, в третьем - цену товаров, и расширяем столбцы так, чтобы вся информация поместилась.
Осталось добавить столбец с указанием стоимости упаковки.
У нас есть два пути: посчитать все на калькуляторе и сделать кучу ошибок или доверить все расчеты Excel.
Конечно же мы выберем второй путь! Будем добавлять формулы.
Итак, для того, чтобы получить цену за упаковку, нам нужно перемножить "количество товаров в упаковке" и "цену товара".
и "цену товара".
Как это сделать?
Активизируем ячейку, в которой хотим создать формулу, и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем (выделяем левой клавишей мыши) соответствующие ячейки в следующем порядке:
= КОЛИЧЕСТВО ТОВАРОВ В УПАКОВКЕ * ЦЕНА ТОВАРА ENTER
Проверить формулу можно, оставаясь в ячейке и нажав на строку состояния:
Если всё правильно, можно смело копировать готовую формулу и вставлять ее в следующие ячейки. Для этого нужно поставить курсор на левый нижний край ячейки с формулой. Появится крестик. Нажмите на этот крестик левой клавишей мышки и тяните не отпуская пальца вниз до конца списка товаров. Отпускаем палец. Получилось!
Сам прайс-лист мы сделали, осталось его оформить. Какие шаги нам осталось сделать?
1. Нумерация строк
Для того чтобы пронумеровать строки нашего прайс-листа, прежде всего нужно добавить столбец перед наименованием товаров.
Ну, а теперь вручную нумеруем первую и вторую строки, после чего активизируем обе ячейки и ставим курсор на нижний левый угол второй ячейки. Должен появиться крестик. Нажимаем на этот крестик левой кнопкой мыши и, удерживая ее, тянем до конца списка.
Что примечательно, такой список можно тянуть с любой цифры. Т.е. если Ваш список прервется и продолжится дальше в другом месте, Вы легко можете его продолжить.
Не забывайте, что для проведения этой операции обязательно нужны как минимум две следующие друг за другом цифры.
2. Форматирование таблицы
Ну вот, наша таблица практически готова. Осталось ее отформатировать: сделать границы, выравнивание и перенос слов.
После форматирования получаем:
Здесь можно обратить внимание на два момента (отмечены красными стрелочками):
- Изменения внутри ячейки. Принудительный перенос.
Бывают случаи, когда автоматический перенос происходит не в том месте, в котором Вам бы хотелось, либо вы допустили ошибку/описку. Чтобы исправить это досадное недоразумение, поступим так.
Для того, чтобы сделать изменения в уже заполненной ячейке, нужно активизировать нужную ячейку и
- либо щелкнуть по ней два раза (курсор появится внутри ячейки)
- либо зайти в строку состояния (находится над серым полем, которое делит страницу на ячейки) путем нажатия на неё.
- либо щелкнуть по ней два раза (курсор появится внутри ячейки)
- либо зайти в строку состояния (находится над серым полем, которое делит страницу на ячейки) путем нажатия на неё.
Ячейка становится активной, и в ней можно производить любые изменения.
Для того, чтобы сделать перенос в нужном нам месте, нужно поставить побольше пробелов перед словом, которое хотим перенести, либо поставить курсор перед словом, которое хотим перенести, нажать кнопку Alt и затем, удерживая ее, - кнопку Enter.
- Визуальное разделение групп разрядов чисел
Для того, чтобы разделить число на разряды для более удобного их чтения (сравните: 10000 или 10 000) в окне диалога "Формат ячеек" (вызывается правой кнопкой мышки) существует закладка "Число". Выбираем формат "Числовой" и ставим галочку в поле "Разделитель групп разрядов".
3. Добавление текста. Объединение ячеек
Осталась самая малость: озаглавить наш прайс-лист.
Мы же хотим, чтобы все было красиво? Тогда поместим название посередине.
Для этого нам нужно объединить ячейки над таблицей.
Это можно сделать с помощью выставления галочки в строке "объединение ячеек" через окно диалога "Формат ячеек" (вызывается правой кнопкой мыши)->"Выравнивание" либо с помощью иконки "объединение ячеек" в верхнем меню "форматирования":
Для начала добавляем строки над таблицей.
А дальше действуем следующим образом: активизируем объединяемые ячейки и нажимаем на иконку "Объединить и поместить в центре".
Теперь можем писать там любой текст.
Наш прайс-лист готов! Ну и последний штрих. Давайте сделаем так, чтобы при печати шапка была видна на каждом листе (конечно же, если их много).
Вызываем через главное меню "Файл" -> "Параметры страницы", выбираем вкладку "Лист" и находим надпись "Печатать на каждой странице"
Нам нужно сделать сквозную шапку, значит, мы выбираем сквозные строки. Нажимаем на кнопочку:
после чего выпадает такое окошко,
в которое мы добавляем нашу шапку путем нажатия на серое поле рядом с ней. Если все сделали правильно, то в белом поле появляются координаты шапки. Появились? Смело нажимаем на кнопочку и возвращаемся во вкладку "Лист". В поле "сквозные строки" должны появиться координаты нашей шапки.
Теперь на каждой страничке нашего прайс-листа будет видна шапка, и нашему клиенту будет приятно с ним работать!
Осталось распечатать его так, чтобы не истратить всю пачку бумаги, положенную в принтер.
Начнем с того, что сделаем просмотр страницы через "Файл" -> "Предварительный просмотр". Именно эта функция дает нам возможность увидеть количество страниц нашего документа и раздвинуть поля вручную. Для этого нужно нажать на кнопку "Поля" (если полей не видно) и нажимая левой кнопки мышки на высветившиеся поля, удерживая кнопку, можем перетаскивать их, как нам заблагорассудится. Тем самым можем расширить имеющийся лист и поместить на него столбцы или строки, которые не поместились при автоматически заданных полях.
После игры с полями, зайдем в закладку "Параметры страницы" (через "Файл" в главном меню) и попробуем поменять страницу на альбомную, либо установим масштаб печати. Если мы хотим, чтобы страница помещалась на один лист в ширину, то проставляем соответствующее значение. Что касается высоты, можете поставить любое максимальное количество. Система не распечатает больше страниц, чем есть в документе.
Добавляем строку или столбец.
Для добавления новых строк или столбцов в Excel 97-2003 существуют:
- вкладка "Вставка" в главном меню вверху страницы
либо
- окно диалога "Добавить ячейки", вызываемое правой кнопкой мыши.
Главное правило вставки строк и столбцов: новые строки появляются НАД активизированной строкой, новые столбцы - СЛЕВА от активизированного столбца.
Удаляем строку или столбец
Для удаления строк или столбцов необходимо:
- выделить удаляемые столбцы или строки, нажав на первую ячейку серого поля, и удерживая протянуть до последнего удаляемого столбца/строки:
- не убирая курсор с выделенной области вызвать меню правой кнопкой мыши и выбрать "Удалить".
Как перенести столбец (строку) в другое место таблицы (вырезать-вставить) в Excel 2007-2010-2013
Предположим, что мы хотим поставить штрих-код перед наименованием товара.
Далее выделяем по серому полю столбец, ПЕРЕД которым хотим вставить нужную информацию:
Удерживая курсор на выделенной области, вызываем меню правой клавишей мыши и выбираем «Вставить вырезанные ячейки»:
Получаем:
Скрываем и открываем строку или столбец
Для того, чтобы скрыть строки или столбцы, необходимо:
- выделить скрываемые столбцы или строки, нажав на первую ячейку серого поля, и удерживая протянуть до последнего скрываемого столбца/строки:
- не убирая курсор с выделенной области вызвать меню правой кнопкой мыши и выбрать "Скрыть".
Для того, чтобы открыть скрытые строки или столбцы, необходимо:
- выделить соседние столбцы или строки с двух сторон и, не убирая курсор с выделенной области, вызвать меню правой кнопкой мыши и выбрать "Отобразить":
Скрываем и открываем несколько строк или столбцов. Группировка в Excel
Предположим, что у нас есть таблица следующего вида, содержащая данные за ряд лет.
Как правило, такие таблицы содержат много деталей и работать с ними не очень удобно.
Скроем информацию, не актуальную на данный момент, с условием, что скрытая информация будет доступна одним нажатием кнопки мыши.
Для этого воспользуемся группировкой данных.
Выделим скрываемую область, найдем в меню "Данные" - "Группа и структура" - "Группировать":
В результате этой операции на сером поле над скрываемым диапазоном таблицы появляется линия со знаком "-":
Нажмем на знак "-":
Как видим, нужные данные скрыты, а над таблицей появился значок "+", при нажатии на который мы вернемся к первоначальному виду таблицы.
Таким образом, функция группировки данных помогает нам скрывать и возвращать нужные данные одним движением руки.
Аналогично можно сгруппировать и данные по строкам.
Как посчитать в Excel наценку и маржу
Для наглядности создаем таблицу и наполняем ее значениями.
Пусть цена товара будет равна 110 рублей, а себестоимость 80 рублей.
| Цена | Себестоимость | Наценка, % | Маржа, % |
| 110 | 80 |
Из блока "В помощь коммерсанту" мы знаем, что:
Наценка = (Цена продажи - Себестоимость) / Себестоимость * 100
Маржа = (Цена продажи - Себестоимость) / Цена продажи * 100
Итак, приступаем к заведению формул в Excel.
Для начала обратим внимание на имеющиеся у нас формулы и отметим, что при занесении формул в Excel действуют все основные правила математики из школьного курса, в т.ч. взятие выражения в скобки.
1. Считаем наценку:
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= ( ЦЕНА - СЕБЕСТОИМОСТЬ ) / СЕБЕСТОИМОСТЬ * 100 ENTER
Если Вы все сделали правильно, то должно получиться 37,5.
Проверить формулу можно, оставаясь в ячейке и нажав на строку состояния:
Система подсвечивает разным цветом соответствующие ячейки.
2. Таким же образом считаем маржу:
Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= ( ЦЕНА - СЕБЕСТОИМОСТЬ ) / ЦЕНА * 100 ENTER
Если Вы все сделали правильно, то должно получиться 27,27.
Что же это такое?! В ячейке "маржа" у меня получилась целая абракадабра.
Как с этим бороться?
Да очень просто!
Для удаления лишних цифр после запятой (и не только) в окне диалога "Формат ячеек" существует закладка "Число". Выбираем нужное число десятичных знаков и всё готово!
Для расчетов всегда выбираем форматы "Числовой", "Денежный" или "Финансовый", иначе расчет будет произведен некорректно либо не произведен вовсе.
Как разделить одновременно несколько ячеек на 1000 в Excel
Предположим, у Вас есть большая таблица, значения в которой выражены в миллионах рублей. Например:
Вам же нужно предоставить эти данные в тысячах рублей. Как сделать так, чтобы не заходить в каждую ячейку и не делить ее на 1000?
Выделяем нужную область и используем для этих целей "Формат ячеек":
В закладке "Число" выбираем (все форматы) и находим в окошке "Тип" выделенный формат:
Далее в окошке "Тип" ставим курсор после первого нуля перед запятой и делаем пробел. Как видим, число, указанное в образце, преобразилось:
Нажимаем ОК и получаем желаемый результат:
Стоит отметить, что Excel читает такие числа в их первоначальном виде, т.е. в миллионах, что видно в строке формул, поэтому если Вам нужно использовать такие числа в расчетах, не забудьте делить их на 1000, если расчеты производятся в тысячах рублей:
Если же Вам нужно именно разделить значения на 1000, то следует воспользоваться функцией "Специальная вставка".
Для этого, в любой ячейке рядом с таблицей пишем 1000, заходим в эту ячейку и копируем ее.
Выделяем весь диапазон с числами, которые нужно разделить на 1000 и вызываем "Специальную вставку", нажав на правую клавишу мыши.
Выбираем "разделить":
и нажимаем ОК. Готово!
Сетка в Excel
Excel представляет собой таблицу, разбитую на ячейки:
Иногда возникает необходимость убрать сетку и получить чистый лист:
Делается это очень просто.
Есть два способа:
Первый способ
В главном меню выбираем "Сервис" -> "Параметры...":
Находим закладку "Вид" и ставим (либо убираем) галочку в окошке "сетка":
Второй способ
Выделяем нужную область и выбираем заливку белого цвета:
При печати листа Excel сетка не видна в любом случае. При переносе в Word сетка появится, даже если она убрана в Excel.Как установить границу листа в Excel
Предположим, что мы подготовили документ в Excel и хотим вывести его на печать. Часто мы сталкиваемся с тем, что размер подготовленного документа не соответствует формату А4.
Рассмотрим основные пути изменения границ листа. Их три:
- Размещение документа на заданном количестве листов через "Параметры страницы"
- Передвижение границ страницы через функцию "Предварительного просмотра"
- Фиксирование границ страницы с помощью "Разметки страницы"
Размещение документа на заданном количестве листов через «Параметры страницы»
Функция «Параметры страницы» вызывается через «Файл» главного меню:
Как видим, «Параметры страницы» имеют 4 закладки: Страницы, Поля, Колонтитулы, Лист.
Нам понадобятся «Страницы» и «Поля».
Первое, что можно сделать – это изменить расположение страницы с «Книжного» формата на «Альбомный».
Нажимаем ОК и просматриваем страницу.
Если она все еще не поместилась на листе, идем дальше.
Для этого выбираем поле «разместить не более чем на … стр. в ширину и … стр. в высоту». Отмечаем нужной цифрой данные поля, выбирая их с помощью стрелочек, или заводим вручную.
Чаще всего бывает нужно, чтобы страница располагалась на одной странице в ширину. В этом случае нужно проставить 1 страницу в ширину и проставить в высоту любое максимальное количество страниц. Excel автоматически разобьет документ на страницы.
Здесь можно изменять поля страницы с помощью стрелочек (обведены кружками), выбирая нужные цифры, а также центрировать документ на странице, отметив поля «горизонтально» и «вертикально».
Как передвинуть границу листа в Excel
Предположим, что мы подготовили документ в Excel и хотим вывести его на печать. Часто мы сталкиваемся с тем, что размер подготовленного документа не соответствует формату А4.
Рассмотрим основные пути изменения границ листа. Их три:
- Размещение документа на заданном количестве листов через "Параметры страницы"
- Передвижение границ страницы через функцию "Предварительного просмотра"
- Фиксирование границ страницы с помощью "Разметки страницы"
Предварительный просмотр страницы. Передвижение границ страницы
Прежде чем печатать любой документ желательно сделать его предварительный просмотр. Таким образом, мы будем уверены, что документ полностью поместится на странице.
Вызвать предварительный просмотр страницы можно двумя способами:
1. Нажать на соответствующую иконку на стандартной панели инструментов:
2. Через «Файл» главного меню:
Функция просмотра позволяет увидеть, на сколько страниц помещается документ. Это видно в левом нижнем углу страницы:
Отдельно стоит остановиться на закладке «Поля», т.к. все остальные закладки повторяются в других функциях.
повторяются в других функциях.
Возможность же передвижения границ листа с помощью «перетаскивания» есть только здесь.
Как это сделать.
Вызываем «Предварительный просмотр» и видим подобную картину:
Если поля не обозначены, нажимаем на соответствующую кнопку.
Разметка страницы. Фиксирование границ листа
Предположим, что у нас есть документ, который мы хотим распечатать с двух сторон, при этом информация, содержащаяся на каждом листе, строго зафиксирована. Можно, конечно, подобрать границу листа вручную путем добавления (удаления) лишних строк.
Однако для этой цели в Excel существует специальная функция – разметка страницы.
Установить ее можно двумя способами:
- через «Предварительный просмотр»
- через «Вид» главного меню
Итак, перед нами документ, границы страниц которого мы хотим зафиксировать:
Нам нужно отделить табличную часть от блока с подписями.
Для того, чтобы это сделать, нужно поставить курсор на синюю границу, нажать на левую клавишу мыши и «перетащить» границу в нужное место:
Если часть документа будет находиться за пределами синей границы, то эта часть не будет видна при печати.
Например:
Для того, чтобы устранить эту проблему, нужно просто переместить синюю границу на край документа.
Внимание: Разметка страницы не будет работать в том случае, если в «Параметрах страницы» установлено размещение страницы не более чем на 1 страницу. Если это так, то следует выбрать натуральную величину страницы.
Комментариев нет:
Отправить комментарий