Марина

Сохранено 09.01.20, 8:25 из УРОКИ EXCEL

4 Формулы Excel Функции - основа Excel. С их помощью производятся самые различные вычисления: от простого суммирования до моделирования случайных выборок. В уроке рассмотрен... Подробнее »

Текст из видео:

00:03: что такое цель без формул печатный станок и не более поэтому переходим к сердцевине xxi вычислительным функциям эта тема настоящего урока функцию в excel довольно много несколько сотен часть из них похожи или даже дублируются тем не менее вычислительные возможности excel довольно большие если сюда добавить различные настройки и вэба то средствами excel можно решить почти любую задачу встречающиеся на практике в excel как и в
00:30: простом калькуляторе предусмотрены математические операторы плюс минус умножить явились и даже возведение в степень это еще не полноценная функция однако с их помощью производится внушительная часть от всех вычислений для того чтобы произвести расчет то есть вести в некоторую ячейку формулу и получить результат первым делом нужно поставить знак равно после этого нужно указать числа и математические функции или операторы которые с этими числами проведут соответствующие манипуляции ну
01:01: например равно 2 плюс 2 или 2 умножить на 3 или 9 делить на три этапа к чистой воды калькулятор а если вместо чисел делать ссылки на ячейки to excel сделать привязку формулы к исходным данным например вот так есть два числа и есть их сумма меняем
01:32: эти числа и как видим сумма автоматически меняется подобный расчет на калькуляторе уже невозможен а сейчас продолжим заполняя сформирован в предыдущих уроках таблицу параллельно повторяя и расширяя пройденный материал удалим все лишнее так как мы имеем данные о ценах и о количестве то напрашивается расчет стоимости подпишем столбец стоимость в рублях
02:01: введём формулу для расчета ставим знак равно далее с помощью мышки или кнопок на клавиатуре указываем ссылки цена умножить на количество и нажимаем кнопочку ввода так как формула для расчета остальных ячеек идентична введены меняются только ссылки то нет никакой необходимости повторять проделанные действия для каждой ячейки достаточно скопировать формулу вниз до конца таблицы для этого воспользуемся известной комбинации
02:30: горячих клавиш kontrol ц control в эту же операцию часто делаю с помощью так называемого протягивания выделяем заполненную формулу ячейку далее подводим курсор к правому нижнему углу чтобы курсор превратился в такой крестик нажимаем левую кнопку мыши и просто начинаем тянуть вниз подобное мы уже проделывали с данными когда вводили коды в таблицу протягивание равносильно копированию поэтому
03:01: нет никакой разницы либо мы копируем либо мы протягиваем есть полезный фокус который позволяет заполнить то есть протянуть формулы до конца таблицы в мгновение ока сотрем то что мы только что скопировали снова выделяем активируем ячейку с формулой далее вновь подводим курсор к правому нижнему углу где маркер и два раза нажимаем левую кнопку мыши этот трюк особенно ярко проявляется когда количество строк более сотни а еще ярче когда более 10000
03:31: тут нолик получился лишнее удаляем его все ссылки в этих формулах имеют относительный характер то есть при копировании смещаются вслед за формулы это легко увидеть активируем первую ячейку и нажимаю f2 для того чтобы наглядно увидеть куда ссылается ячейка выделяем второй ячейку вновь нажимаем и v2 третий и так далее то есть видно что вслед за формулой поехали и ссылки они так и называются относительные ссылки
04:00: есть еще так называемые абсолютные ссылки когда ссылка остается на месте независимо от того куда копируют ссылающиеся на нее формулу но об этом чуть позже использование математического оператора как примере выше предполагает ссылку на каждую отдельно взятую ячейку если потребуется сложить или умножить большое количество данных то использование оператора будет равносильно использование калькулятора что очень долго и неудобно полноценная функция excel позволяют многократно иногда тысячекратно ускорить
04:30: вычислительный процесс так как ссылка может указывать сразу на целый диапазон данных основной операции которые подвергаются числовые данные являются суммирование на простое суммирование приходится я думаю более половины всех вычислений в excel если сюда добавить еще несколько основных функций то их вполне хватит для любых несложных расчетов это замечательно так как начинающему пользователю нет никакой необходимости изучать талмуд и с описанием всевозможных формул excel остановимся на суммирование данных с помощью специальной функцией такая
05:00: функция называется сумму оно используется везде где только можно придумать ну например для подсчёта итоговой строке таблицы которую мы в прошлом уроке только обозначили но не заполнили самое время это сделать сумма цен не имеет смысла поэтому оставим эту ячейку пустой сумму по количеству в конкретно нашем случае строго говоря тоже не имеет смысла так как тетрадки с папками не правильно складывать однако в некоторых аналитических целях такую операцию проводят поэтому просуммируем этот
05:30: столбец вызвать нужную функцию можно тремя способами первое воспользоваться библиотекой на ленте заходим во вкладку формулы и перед нами открывается библиотека у вас она имеет немножко другой вид кнопки у вас больше мне же пришлось сжать экран для записи поэтому у меня эти кнопки несколько меньше все функции разделены на группы и для того чтобы обратиться к нужной функции первую очередь нужно открыть раскрывающийся список соответствующей категории и найти нужную нам функцию мы ищем функцию sum
06:00: открываем ее и вот появляется такое диалоговое окно где нужно указать диапазон для суммирования мы к нему еще вернемся так как сейчас рассмотрим второй способ года функции это наиболее часто используемый способ с помощью команды вставка функции команда вызывается вот этой кнопкой нажимаем ее открывается диалоговое окно здесь находится все функции excel почему тварин предпочтительной потому что не нужно знать в какой категории находится искомая функция достаточно с помощью фильтра указать
06:30: полный алфавитный перечень и по алфавиту отыскать нужную нам функцию на букву с начинается она вот два раза нажимаем и открывается диалоговое окно которое мы только что видели здесь нужно указать диапазон для суммирования можно ничего не нажимая просто выделить прямо в листе excel вот такой диапазон либо поступить немножко по-другому можно нажать вот на эту кнопочку диалоговое окно сжимается и снова мы указываем диапазон для
07:00: суммирования нажимаем вот и еще раз вот происходит суммирование выделенного диапазона и последний способ ведения формулы ячеек в excel это ручной набор я этим способом пользуюсь наиболее часто и другими рекомендую так как получается гораздо быстрее итак нажимаем кнопочку равно далее нужно ввести название функции многих это может испугать но ничего страшного здесь нет достаточно знать хотя бы прими одно название функции набираем первую букву
07:30: s открывается список функций которые начинаются на букву с у вас он наполовину скрыт поэтому я немножко перемещусь вот так и сделаю еще раз сумм вот открылся список функций на букву с их довольно много и выбирать отсюда тоже не очень удобно поэтому мы вводим вторую букву у список гораздо сократился и нам остается выбрать только нужную функцию как видим суммирующих функций здесь несколько нас интересуют первое дальше либо с помощью мыши два раза нажимаем на эту
08:00: кнопочку либо гораздо быстрее использовать кнопочку так мы выбираем функцию точнее мы якобы активируем теперь нам остается только с помощью мыши или клавиатуры указать диапазон данных для суммирования и нажимаем вот по правилам нужно закрыть скобку но в данном случае не обязательно потому что их целью закроют самостоятельно как видим посчитала сумма таким же образом можно вставить абсолютно любую имеющуюся в excel
08:30: функции однако сумм это самая часто используемая функция и поэтому для нее придуманы и вынесены на ленту специальной кнопки авто сумма их здесь аж две вот первая кнопка и закладки главная вторая кнопка для того чтобы воспользоваться активируем ячейку удаляем если за все и далее просто нажимаем на эту кнопку как видим excel автоматически правильно определяет диапазон суммирование вот он внутри так называемых бегущих муравьев это такая перемещающийся
09:00: по периметру диапазоны рамка осталось нажать enter и так для суммирования данных достаточно воспользоваться авто сумы если диапазон суммирования определяется неверно то это легко поправить с помощью мышки от примеру мы еще раз нажимаем авто сумма диапазон определился но он допустим нам нужно просуммировать только половину значений нет ничего проще указать мышка этот диапазон нажимаем enter возвращаем правильный расчет теперь подобьем итог для столбца со стоимостью как мы знаем сделать это
09:30: можно различными способами ну воспользуемся наиболее быстром кнопкой авто сумма кстати рассчитать сумму можно сразу для целой строки вот удалим эти значения выделим те ячейки в которые мы хотим увидеть авто сумму и нажмём на эту кнопочку как видим сумма посчитала сразу то всего выделенного диапазона либо второй вариант выделяем диапазон суммирования снова нажимаем на кнопку авто сумы excel автоматически определяет структуру данных и в 1 пустой строке заполняет
10:00: сумму значение продолжим наши вычисления в аналитических целях часто требуется рассчитать долю то есть отношении отдельного элемента к общей сумме добавим столбец который он будет рассчитана доля стоимости каждой позиции к итогу подписываем доля и введём формулам равно нам нужно стоимость конкретно этой позиции разделить на итоговое значение вот она нажимаем вот распространим эту
10:30: формулу на остальные ячейки можно протянуть появились какие-то ошибки ничего удивительного в этом нет разумеется я сделал это специально чтобы показать к чему приводит невнимательное отношение к типу ссылок смотрите для первой ячейке ссылки указаны верно выделяем ячейку и нажимаем f2 чтобы активировать чтобы подсветить имеющиеся ссылке в этой формуле здесь все верно стоимость делится на итог сделаем то же самое для второй ячейке как
11:00: видим в этом расчете числитель поехал вслед за формула все правильно а знаменатель уехал вниз чего не должно было произойти так как знаменатель это итоговая строка и она должна оставаться для всех постоянной вот здесь как раз и уместно абсолютно ссылка которая всегда остается на месте и имеет один и тот же адрес для того чтобы сделать ссылку абсолютной нужно закрепить столбец или строку с помощью значка доллара для этого достаточно поместить курсор на ссылку здесь или в строке формул далее в
11:30: английской раскладке нажимаем shift 4 для того чтобы закрепить строку и shift 4 для того чтобы закрепить столбец но сразу оговорюсь таким способом закреплять ссылки очень неудобно гораздо правильнее воспользоваться кнопкой f4 мы снова ставим курсор в какое-либо место ссылке которую нужно превратить и из относительной в абсолютный либо наоборот и нажимаем кнопочку f4 как видим сейчас я выберу курсор как видим первое нажатие кнопки в 4 из относительной ссылки
12:00: делают абсолютно то есть закрепляют ее и по строкам и по столбцам следующее нажатие закрепляет ссылку только по строкам следующая только по столбцам и вновь полностью относительно ссылка снова полностью абсолютно и так далее значит мы нажимаем кнопку несколько раз останавливаемся на нужном нам варианте нашем случае нам нужно закрепить итоговую строку вот такой вариант и оставим а нужно было сделать это в первой ячейке вот здесь нажимаем f4 и вот
12:30: теперь протягиваем формулу вниз и сейчас мы убедимся что все работает правильно здесь тоже можно добавить сумму и она у нас получится какая правильная единица если у вас получилось не единица значит где ты произведен неверный расчет нужно проверить ещё раз формулы и так мы увидели как пользоваться функции sum и математическими операторами предлагаю знакомиться с еще несколькими популярными функциями excel
13:02: довольно часто рассчитывают среднюю арифметическую для этого есть специальная функция которая называется знач в учебных целях проведем расчет средней стоимости как мы уже знаем существует несколько способов ведения формул я думаю нет смысла перебирать их всех заново воспользуемся вставкой функции полный алфавитный перечень кстати если нажать первую букву функции то мы автоматически окажемся на первой функции по списку которая начинается с этой буквы нас
13:30: интересуют среднее значение и здесь нажимаем букву s и мы оказываемся ведь в первую функцию на букву с сегодня нам не нужно находим среднее значение кожа на нажимаем два раза открывается диалоговое окошко здесь мы вновь указываем только диапазон и производится расчет эту же функцию можно ввести вручную
14:04: и ещё один способ через кнопку авто сумы нажимаем на раскрывающийся список выбираем функцию и здесь выбираем я excel автоматически пытайся угадать диапазон который нам нужно усреднить но делает это некорректно потому что захватывает итоговое значение мы можем легко это исправить с помощью мышки существенно реже но используются и другие функции например функция
14:30: подсчета количества чисел минимума максимова подсчитаем примеру количество чисел в нашей таблице для этого можно воспользоваться кнопкой авто сумы вот эта функция счетчик называется и выделяем диапазон с нашими данными видно что у нас 20 позиций точно так можно посчитать минимальное и максимальное значение пример максимум максимальное значение 1875
15:01: минимальное значение 70 все эти функции имеют очень простой вид название и ссылка на диапазон то есть на результат влияет только один аргумент диапазон данных но есть более сложные функции синтаксис которых состоит из нескольких параметров например часто используют функцию округления который называется округу с помощью этой функции в отличие от форматирования производят реально округление числа в
15:31: авто сумме ее уже нет поэтому обращаемся во вставку функции округлим допустим рассчитанную долю до трех знаков после запятой можно обратиться во вставку функции кстати здесь есть поисковая строка если мы не знаем как называется наша функция то можно попробовать и найти следующим способом 3 2 раза кликаем открывается окно здесь уже два поля обязательны для заполнения в первом поле мы указываем число которое собираемся
16:00: округлить вот это число и второе поле это число разрядов то есть количество знаков после запятой в нашем случае мы укажем 3 знака и нажимаем муки кстати если обратить внимание в этом диалоговом окне сразу указывается ответ то есть еще до нажатия кнопки ok мы можем увидеть результат расчета 0 119 вот это значение формулы можно протянуть вниз и как
16:31: видим все работает правильно функции не обязательно писать в отдельных ячейках они могут являться частью одной большой формулы что позволяет существенно экономить место и делать таблицы более компактными например проведем округление прямо в столбце с расчетом доли для этого активируем ячейку переходим в строку формул прямо здесь прописываем округление вот мы и выбираем нажимаем кнопочку tab значит округление произойдет вот этого числа ставим точку с
17:01: запятой это равносильно тому что мы переходим следующее поле и указываем число три закрываем скобку пишем равно и протягиваем формулу вниз до последней строки таким образом мы избавились от лишнего столбца с расчетами для того чтобы убедиться что у нас нет знаков после третьего можно сделать вот так то есть там ну листают долю часто измеряют в проценту и поэтому изменим формат этого столбца выделяем весь
17:30: диапазон и нажимаем на кнопочку процентный формат с помощью вот этих кнопок можно задать количество видимых знаков после запятой у нас остается всего лишь один потому что мы произвели округление и вот так оставляем и еще один полезный трюк связаны с простыми вычислениями выделим диапазон для которого нужно рассчитать показатель типа сумма средняя и так далее выделили теперь смотрим в правый нижний угол здесь в строке состояния автоматически рассчитываются некоторые показатели
18:00: для выделенного диапазона это среднее количество заполненных ячеек и сумма также помогают ускорить работу когда нужно быстро узнать сумму по некоторым диапазону данных при этом нет необходимости эту сумму где-то сохранять очень удобный способ берем на заметку и так в этом уроке мы рассмотрели наиболее часто используемую функции excel с их помощью можно производить большинство несложных расчетов повторим их прежде всего это математические операторы плюс минус умножить делись а также
18:30: функции сумма среднее значение максимально и минимально и округление начинающему пользователю достаточно для проведения аналогичных расчетов смежных ячейках достаточно протянуть выбранную функцию то есть скопировать ее на другие ячейки при этом следует быть внимательным к типу ссылки вообще копирование это очень мощный и эффективный инструмент ввода данных поэтому на копирование выделен целый урок он как раз следующей по порядку