Why do we need so many transistors?
Transistors are switches, yes, but switches are more than just for turning lights on and off.

Switches are grouped together into logic gates.  Logic gates are grouped together into logic blocks.  Logic blocks are grouped together into logic functions.  Logic functions are grouped together into chips.

For example, a TTL NAND gate typically uses 2 transistors (NAND gates are considered one of the fundamental building blocks of logic, along with NOR):







simulate this circuit &ndash; Schematic created using CircuitLab

As the technology transitioned from TTL to CMOS (which is now the de-facto standard) there was basically an instant doubling of transistors.  For instance, the NAND gate went from 2 transistors to 4:







simulate this circuit

A latch (such as an SR) can be made using 2 CMOS NAND gates, so 8 transistors.  A 32-bit register could therefore be made using 32 flip-flops, so 64 NAND gates, or 256 transistors.  An ALU may have multiple registers, plus lots of other gates as well, so the number of transistors grows rapidly.

The more complex the functions the chip performs, the more gates are needed, and thus the more transistors.

Your average CPU these days is considerably more complex than say a Z80 chip from 30 years ago.  It not only uses registers that are 8 times the width, but the actual operations it performs (complex 3D transformations, vector processing, etc) are all far far more complex than the older chips can perform.  A single instruction in a modern CPU may take a many seconds (or even minutes) of computation in an old 8-bitter, and all that is done, ultimately, by having more transistors.