Yup... it lets computers find other computers and peripherals/users connected to them.
If you have a printer connected to an Airport, then Bonjour means that it just 'appears' in the Print dialogue.
If you're using iChat then, even if there's no wireless network, if two people have Bonjour enabled in iChat, then you'll be able to see one another.