Введение

Инженер конструктор отдела гл.механика Гаврилов Сергей Фёдорович написал эту книгу

Студентам и школьникам в помощь при написании курсовых и дипломных работ.

Многие расчеты трудоемки и часто повторяются. Не все будут осваивать программирование на языках Python или Pascal и ставить эти программы на свои компьютеры. Программа Excel входит в стандартный Офис и,

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

Расчеты выполнены с цифровыми примерами и справочными таблицами, чертежами и схемами.

Книга иллюстрирована большим количеством рисунков, облегчающим понимание материала.

В конце книги приведен обширный список литературы по затронутым в книге темам.

По приведенной ссылке нужно скачать расчетные листы Excel. Более сорока Расчетных листов в вашем распоряжении. Копируй их на свой компьютер – и у вас готовая библиотека рабочих расчетных программ –

включай нужное и расcчитывай. Если нужно выполнить расчет – отсутствующий в книге,

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

Арифметические операции в Excel

15 = 5 * 3; Пять умножить на три = 15…

5 = 15 / 3; Пятнадцать разделить на три = пять…

125 =СТЕПЕНЬ(5;3); Пять в степени три = 125…

Степень числа может быть и меньше единицы например 0,3333..

5 =СТЕПЕНЬ(125;0.33333); 125 в степени 0,33333 = 5…

12 = КОРЕНЬ(144); Извлечение квадратного корня из числа = 144..

12 = sqrt(144); Извлечение квадратного корня из числа = 144.. ( при записи в Word ).

90 градусов = 1,570796327 радиан. = Pii/2…

180 градусов = 3,14159265358979324…радиан. = Pii…

ctg( a ) = tan ( 90 – a )… Перевод приведен для справки.. Угол берется в градусах.

Эвалюта угла (GR); – Угол в данный расчет берется в радианах.

Inv(GR) = tan(GR) – (GR);

Константы

Pii = 3,14159265358979324… – Число Пи..

.cosa= cos20 = 0,93969262078591 – Косинус 20 градусов

.sinaa=sin40 = 0,64278760968654 – Синус 40 градусов

.tana=tan20 = 0,3639702342662024 – Тангенс 20 градусов

.inva=0,01490438386734 – Эволюта 20 градусов

.kx=1,387048 – Коэф. постоянной хорды зуба для прямозубых без коррекции.

.hx=0,748 – Коэф. высоты до хорды для прямозубых без коррекции.

Примечание: Программа компиляции текста в эл.книгу иногда переносит часть формулы на другую строку.

– в конце формулы всегда стоит двойная точка.. В тексте 400 градусов – значек градуса программа превращает в ноль – в тексте эл.книги получается 4000 градусов. ( это надо учитывать ). Если справочная таблица в книге очень мелкая – смотрите таблицу в приложении по ссылке.

Вес фрагмента детали

Вес прямоугольного бруса

Данные простые расчеты приведены для для облегчения понимания более сложных, приведенных далее.

Исходные данные: Все размеры в миллиметрах.

Плотность материала принята 7,85 т / куб.м.. ( Сталь ).

Рис. Прямоугольный брус.

Ширина бруса b = 128.

Высота бруса h = 46.

Длина бруса L = 235.

Расчет:

Масса бруса в кг: Q = 7,85 * b * h* L / 1000000..;

Q = 10,861888 кг…

Плотность материала = 7850 кг / куб.м. ( Сталь 30 ГОСТ 1050-88 ).

,,,,,,,,,,

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

Условные точки:

@001. – Делаем объединение ячеек. Заголовок листа.

@002. – Форматирование ячеек, столбцов.

@003. – Защита ячеек, столбцов от изменения при содержания при работе.

@004. – Изменение высоты строк, ширины столбцов.

@004А. Формат столбика, ячейки для ввода исходных цифровых данных.

@005. – Пишем расчетную формулу – оформляем ячейку вывода результата.

@006. – Сохранение расчетного листа.

@007. – Защита расчетного листа от случайных или нет, изменений программы.

@008. – Выбор типа формул, Вставка математических функций в формулу.

@009. – Редактируем формулы.

@010. – Ввод формулы с тригонометрией.

@011. – Делаем столбик очень узким – чтоб не отвлекал промежуточными результатами.

@012. – Расчет объема и веса конуса и усеченного конуса.

@013. – Вставим рисунок.

@014. – Вставим лист № 2.

@015. – Перенос результатов – копированием.

@016. – Программирование ( запись ) Макроса .

@017. – Перенос результатов с применением Макроса.

@018. – Примечание по написанию формул в книге.

@019. – Создание листа «Оглавление».

@020. – Создаем удобный для работы масштаб листа.

Запись программы в Excel

Первым делом откроем чистый лист Excel по имени А_00.

Этот файл есть в приложении ( скачиваем по ссылке ).

Сразу же сохраняем его под именем Е 001. Это что бы у нас всегда оставался чистый лист.

Слева виден зеленый прямоугольник «Файл» правее видно что мы находимся в режиме «Главная».

@001. На листе необходимо выполнить заголовок расчета. Для этого ставим курсор в ячейку А1 и не отпуская

Левую кнопку мыши ведем к ячейке F1. Ячейки выделяются цветом. Отпустим кнопку мыши, переведем

курсор на значек «Объединить ячейки», ( смотри Рис 02 ) щелкнем кнопкой – Ячейки объединятся.

Рис. Экран Excel центровка.

,,,

Рис. Экран Excel объединить.

,,,,,

,,,,

Левой кнопкой мыши щелкнем на объединенной области А1-F1 – выбрав ее. Щелкнем левой кнопкой на строке формул и в строке формул пишем «Вес прямоугольного бруса.». Эта надпись появится в выбранной области.

@002. Переводим курсор на значки «Центровка» и выберем вертикальную центровку «По центру». Горизонтальную центровку тоже «По центру».

Далее щелкаем на значке «Выбор формул»– выйдет табличка Рис 06.

В табличке выберем категорию «Математические». ОК.

Правой кнопкой щелкаем по надписи в А1-F1 – появится табличка Рис 03.

В ней выбираем пункт «Формат ячеек».

Рис. Экран Excel вставка.

,,,

,,,

После щелчка на «Формате ячеек» появится табличка Рис 04:

Рис. Экран Excel формат.

,,,,

,,,

В колонке «Число»– выбираем «Общее»

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

В пункте «Защита»– выберем «защищаемая ячейка». В этом режиме при работе с таблицей надпись в этой ячейке изменить остается неизменной.

Далее нам надо разместить исходные данные для расчета.

Названия исходных данных расположим в столбике «А».

@002А. Ставим курсор в ячейку А12 Нажимаем левую кнопку мыши и с нажатой кнопкой ведем вверх

до ячейки А2. Столбик А2-А12 выделяется. Ставим курсор в выделенный столбик и жмем

правую кнопку мыши.

Появится табличка Рис 03. В ней выбираем пункт «Формат ячеек». После щелчка на «Формате ячеек»появится табличка Рис 04: В этой табличке выберем в колонке «Число»– выбираем «Общее»

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

@003. – В пункте «Защита»– выберем»защищаемая ячейка». В этом режиме, при работе с таблицей,

надпись в этой ячейке изменить не получится – надпись остается неизменной.

Точно также обработаем столбик»С». С2-С12. В этом столбике будет выведены названия результатов расчета.

Теперь заполним столбик «А» записями. Щелкаем левой клавишей на ячейке А2.

В стоке формул пишем: «Размеры бруса в мм.». Надпись появилась в ячейке А2. Щелкаем правой клавишей по надписи в ячейке А2. В табличке Рис 03 выделяем «Формате ячеек», табличке Рис 04 – изменяем цвет шрифта на зеленый. Изменяем цвет заливки на бледно – голубой. Ентер.

@004. Если высота ячейки мала – ставим курсор на границу между «2» и «3» Смотри Рис 01.

Появится крестик со стрелочками. Нажимаем левую клавишу и тянем границу строки вниз делая

строку больше по высоте.

Точно также – если надо сделать ячейку длиннее. Курсор на границу между «А» и «В» и жмем левую клавишу и тянем вправо делая столбик «А»шире.

Точно также заполняем ячейку А5 надписью «Длина»только цвет шрифта оставим черным и заливку не делаем. В ячейку А7 пишем «Ширина». В ячейку А9 пишем «Высота».

В ячейку А11 пишем «Удельный вес в т/куб.м.».

Аналогично заполним столбик»С». Щелкаем левой клавишей на ячейке С6.

В стоке формул пишем: «Вес бруса в кгс.». Надпись появилась в ячейке С6. Шелкаем правой клавишей по надписи в ячейке С6. В табличке Рис 03 выделяем «Формате ячеек», табличке Рис 04 – изменяем цвет шрифта на красный. Изменяем цвет заливки на бледно – желтый. Ентер.

В пункте «Защита»– выберем «защищаемая ячейка».

@004А. Теперь обработаем столбик «В»– в него помещаем – вводим исходные цифровые данные.

Ставим курсор в ячейку В12 Нажимаем левую кнопку мыши и с нажатой кнопкой ведем вверх до ячейки В2.

Столбик В2-В12 выделяется. Ставим курсор в выделенный столбик и жмем правую кнопку мыши.

Появится табличка Рис 03. В ней выбираем пункт»Формат ячеек». После щелчка на «Формате ячеек»появится табличка Рис 04: В этой табличке выберем размер шрифта надписей ( 16 ), цвет шрифта ( черный ),

В графе «Число»выбираем формат «Числовой». В графе «Выравнивание»выбираем выравнивание

как по вертикали, так и по горизонтали по центру. В графе «Защита»нужно убрать защиту ячеек. Ентер.

@005. Ячейка вывода результата D6. ставим галочку «Скрыть формулы». Защиту уберем.

Заливку сделаем светло желтой. В графе «Число»выбираем формат «Числовой».

Выравнивание все по центру.

Теперь пишем формулу расчета. Щелкаем на ячейке D6. Ячейка выделится черной рамкой.

В строке формул ставим знак «=». В Excel все формулы начинаются со знака равно.

Далее переводим курсор на ячейку В5 – щелкаем – в строке формул отпечаталась В5. Печатаем знак «*» умножить. Затем курсор на ячейку В7 – щелкаем, печатаем *. Затем курсор на ячейку В9 – щелкаем, печатаем *.

Затем курсор на ячейку В11 – щелкаем, печатаем /1000000.

В итоге ф строке формул будет запись»=В5*В7*В9*В11/1000000»Ентер.

При постановке в ячейки В5…В11 чисел исходных данных – в ячейке появится результат расчета.

Проверьте по контрольному примеру приведенному выше.

@006. После проверки сохраним расчетный лист как файл Е 00. Затем как файл Е 01.

В Файле Е 01 заблокируем изменения того, что изменять не хотим.

@007. Проверим, чтобы в столбике»В»была снята защита со всех ячеек.

Чтобы в ячейке D6. Была снята защита, но стояла галочка «Скрыть формулы».

Откроем режим «Рецензирование». Справа иконка «Защитить лист» Щелкнем.

Выйдет табличка Рис 05. Рис. Экран Excel защита.

,,,

,,,

Здесь нужно убрать галочку с пункта «Выделение заблокированных ячеек». Затем ОК.

Заранее запишите пароль. Проверьте закрыв лист, затем снова открыв его – можно ли менять исходные данные и будет ли меняться результат. Если что не так. Файл листа удалить – открыть файл Е 00,

сохранить его как Е_01 и снова проверить все ячейки на блокировку.

На всех листах применен пароль «blok»( для справки ).

Готовый лист с блокировкой под паролем сохраняем под именем Е_01_Z.

Не смущайтесь, что простую программу так долго делали – дальше дело пойдет быстрей.

Будем использовать незащищенный файл Е 00 – Сохранять его под другим именем, затем заменять, добавлять надписи, редактировать формулы – время потребуется меньше.

Следующие расчеты буду подробно расписывать, если будут встречаться новые элементы.

Все описанные программы можно будет скачать в приложении по ссылке.

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

В результате лист будет испорчен, работать не станет.

##

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

Вес круглого бруса

Исходные данные:

Рис. Круглый брус.

Диаметр d = 127.

Длина L = 390.

Расчет:

Площадь поперечного сечения: S=d*d*Pii/4; S=127*127*Pii /4..;

S= 12667,68698.

Масса бруса:

Q = 7,85 * S * L / 1000000..;

Q = 7,85 *12667,68698*390 / 1000000..;

Q = 38,782124 кг.

Запись программы в Excel

Первым делом откроем лист Excel по имени Е_00. ( Скачать из приложения ).

Сохраним его как Е _03. Далее на листе необходимо выполнить заголовок расчета «Вес круглого бруса».

Примечание: Бывает Excel сохранять под другим именем не желает –

тогда вносим небольшое изменение в заголовок – тогда сохраняется под другим именеи без проблем.

Просто меняем надпись в заголовке. Затем меняем надписи в столбике исходных данных:

Надпись «Длина» оставим, в ячейке А7 пишем»Диаметр», в ячейке А9 пишем «Удельный вес в т/куб.м.»,

ячейку А11 выделим и удалим клавишей Delete.

Ячейку результата D6. Меняем формулу: Щелкнем на D6 – в строке формул появится формула.

Ставим курсор в конец записи формулы и стираем ее оставив лишь знак «=».

Курсор справа от знака»равно»переводим курсор на ячейку В5 – Щелкаем. Далее вводим знак «*»,

Далее на ячейку В7 – Щелкаем. Далее вводим знак «*», еще на ячейку В7 – Щелкаем.

Далее вводим знак «*»,

На ячейку В9 – Щелкаем. Далее вводим знак «*».

@008. Далее щелкаем на значке «Выбор формул»– выйдет табличка Рис 06.

В табличке выберем категорию «Математические».

Рис. Экран Excel Математические функции.

,,,,,

,,,,

По табличке находим функцию без аргументов ( пустые скобки ) «Пи»– это константа.

Выбрали нажимаем «Ок»функция ПИ встраивается в формулу. Далее печатаем знак «/»делить,

И печатаем число 4000000. В итоге в строке формул получится: =В5*В7*В7*И9*ПИ()/4000000.. Нажимаем Ентер. Сохраняем файл под именем Е 03 – это будет открытый файл. Этот же файл сохраняем как Е 03_Z.

Это будет закрытый файл.

@008А. Примечание: Функцию ПИ() как и любую другую функцию нельзя просто напечатать – ее надо

обязательно выбрать ( смотри Рис 02 ) щелкнув на значке выбора формул. Если функция запрашивает число в скобки – то это число надо заранее вычислить в определенной ячейке таблицы и в скобки вставить (щелкнуть) номер ячейки, где хранится вычисленное это число. Следует помнить, что в аргументы в тригонометрических функциях вводятся только в радианах.

@007. Откроем файл Е 03_Z – переходим в раздел «Рецензирование»-

Проверим, чтобы в столбике «В»была снята защита со всех ячеек.

Чтобы в ячейке D6. Была снята защита, но стояла галочка «Скрыть формулы».

Щелкаем по иконке «Защитить лист».

Откроем режим «Рецензирование». Справа иконка «Защитить лист»Щелкнем.

Выйдет табличка Рис 05.

Здесь нужно убрать галочку с пункта «Выделение заблокированных ячеек». Затем ОК.

Заранее запишите пароль. Проверьте закрыв лист, затем снова открыв его – можно ли менять исходные данные и будет ли меняться результат. Если что не так. Файл листа удалить – открыть файл Е_03,

сохранить его как Е_03_Z и снова проверить все ячейки на блокировку.

На всех листах применять пароль «blok»( для справки ).

Как видим – метод правки шаблона значительно ускоряет разработку расчетного листа.

Вес трубы

Исходные данные:

Наружный диаметр d = 200.

Внутренний диаметр dv = 100.

Длина L = 300.

Плотность стали 7850 кг/куб.м.

Рис. Труба круглая.

Расчет:

Sn=d*d*Pii/4; Sn= 200*200*Pii/4; Sn= 31415,92654..

Sv=dv*dv*Pii/4..;

Sv= 100*100*Pii/4; Sv=7853,981634..

Q=7,85*(Sn-Sv)*L / 1000000..; Q=7,85*(23561,9449)*300 / 1000000..;

Q= 55,48838 кг…

Запись программы в Excel

Первым делом откроем лист Excel по имени Е_03. ( Скачать из приложения ).

Сохраним его как Е 04. Далее на листе необходимо выполнить заголовок расчета «Вес трубы».

Просто меняем надпись в заголовке. Затем меняем надписи в столбике исходных данных:

Надпись «Длина»оставим, в ячейке А7 пишем «Диаметр наружный»,

в ячейке А9 пишем «Диаметр внутренний»,

в ячейке А11 пишем «Удельный вес в т/куб.м.»,

@009. Выделяем ячейку Е5. Форматируем ее точно как ячейку D6.

Выделяем ячейку Е7. Форматируем ее точно как ячейку D6.

Проверяем чтобы в ячейке D6.; Е5, Е7 – Была снята защита, но стояла галочка «Скрыть формулы».

В ячейки Е5 и Е7 копируем формулу из ячейки D6. Для этого щелкаем на ячейке D6 – в строке формул

Появится формула из ячейки D6. Выделим ее курсором нажав левую кнопку и проведя по формуле.

Затем нажмем правую кнопку и на выпавшей табличке нажмем «Скопировать».

Выделим курсором ячейку Е5 левой кнопкой и поставив курсор на пустую строку формул щелкнем левой кнопкой – Затем щелкаем правой кнопкой – в выпавшей табличке щелкаем на иконке «Вставить». В строке формул появится скопированная формула из ячейки D6.

Точно так же копируем формулу в ячейку Е7.

Щелкаем на ячейке Е5 – в строке формул появится формула из ячейки D6 : =В5*В7*В7*В9*ПИ()/4000000..

Заменяем в формуле В9 на В11 – Получится : =В5*В7*В7*В11*ПИ()/4000000..

Щелкаем на ячейке Е7 – в строке формул появится формула из ячейки D6 : =В5*В7*В7*В9*ПИ()/4000000..

Заменяем в формуле В7 на В9.; А В9 на В11 – Получится : =В5*В9*В9*В11*ПИ()/4000000..

Ячейку результата D6. Меняем формулу: Щелкнем на D6 – в строке формул появится формула.

Меняем формулу на: =Е5-Е7 Ентер.

Проверяем правильность записей – расчетом с контрольными цифрами.

Если все правильно – сохраняем лист как Е 04. Затем сохраняем как Е 04_Z.

Включаем защиту листа Е 04_Z. Смотри @007.

Вес правильного шестигранника

Исходные данные:

Размер под ключ S = 100.

Длина бруса L = 300.

Плотность стали 7850 кг /куб. м.

Рис. Шестигранник.

Расчет:

Q=7,85 * 0,866025403 * S * S *L / 1000000..;

Sp= 0,866025403 * S * S .. Площадь кв.мм.

Q=7,85 * 0,866025403 * 100 * 100 * 300 / 1000000..;

Q= 20,3949 кг…

Примечание: Программа для расчета веса шестигранника отличается от программы для круглого бруса

только отсутствием в формуле числа ПИ и введением коэф. = 0,866025403…

Вес прямоугольного треугольника

Брус.

В сечении прямоугольный треугольник.

Высота h = 200.

Основание b = 160.

Длина бруса С = 300.

Рис. Прямоугольный треугольник.

Масса бруса в кг: Q = 7,85 * b * h* С / 2000000..;

Q=7,85 * 200 * 160 * 300 / 2000000..;

Q= 37,68 кг…

Примечание: Программа для расчета веса прямоугольного треугольника отличается от программы

для прямоугольного бруса только введением коэф. = 0,5…

Вес треугольника с разными сторонами

Брус – в сечении треугольник.

Сторона a = 200.

Сторона b = 160.

Сторона c = 140.

Длина бруса h = 300.

Рис. Разносторонний треугольник.

Расчет:

Материал – сталь q = 7,85 т / куб.м.

Расчет:

.x=(b*b+c*c-(a*a))/(2*b*c)..;

.x=((160*160+140*140-(200*200))/(2*160*140)..;

.x= 0,116071428…

Ua = arccos(x)…

Ua = 83,33457274…

Напротив стороны лежит одноименный угол..

Высота на сторону b :

Hb = c * cos ( 90 – Ua ); ( 90 градусов = 1,570796327 радиан. ).

Hb = 140 * 0,993240868 = 139,0537216…

Площадь треугольника:

S = b * Hb/2..; S = 160 * 139,0537216 / 2 = 11124,29773 …

Масса бруса:

Q = 7,85 * S * h..; Q = 7,85 * 11124,29773 * 300 / 1000000..;